Expert: Scottgem - 4/4/2004

Question
-------------------------
Followup To
Question -
-------------------------
Followup To
Question -
-------------------------
Followup To
Question -
Hi Scott
I am using Windows and office XP
You have helped me out before and the database was running just fine up until yesterday.
I tried to add an new record, which I have done many times with no problems. Yesterday I got a message up telling me the changes I requested were not successful because they would create duplicate values in the index etc.
This seemed strange so I did some investigating and found that the primary key for the main table was trying to duplicate. The properties for the PK are autonumber, long integer and indexed no duplicates. So in theory there shouldn't be any numbers the same, but it is definately trying to add a new record using a number that has already been used.
The last number used was 2022, yet it tried to add a record at number 130 (in use). When I closed the table and tried again this time it went to 131, this number was not in use so all was well. but I tried to add yet another record and it tried putting in 132 but this number was already used so again I had the same problems.
I usually use the form to add new records but as I was having problems thats why I went to the table to add them so I could see what was going on.
Have you any ideas how I can solve this or am I in trouble?

Many thanks
Answer -
I'm confused. I've never seen an Autonumber restart like that. There are two ways to generate Autonumbers, randomly and sequentially (the default). So I can't understand how it could be generating numbers in the 130s if there are numbers in the 2000s. Something wierd has happened to your database.

The first thing I would do is do a compact and repair. If that doesn't fix the problem, you may have to rebuild the application. Are there any records related to this table on the PK?

Hope this helps,
Scott<>

I'm sort of glad you are confused as I am, it helps to know I am not going mad!

The first thing I did was to do a compact and repair, because that seemed a good solution. It didn't help.

I have 7 tables linked in one - many relationships. I have tried deleting the joins and putting them back again. I have run a unmatched records query between the main table and all the others. All to no avail.

So where do I start with a re-build? Thats something I didn't want to do really because it has taken me (and you) about two years to get this thing working right.
Answer -
Ok a rebuild is not that bad. All you will need to do is rebuild the tables, the rest of the stuff will come over intact. Step by Step;

1) Create a new, blank database file.
2) Go to File>External Data and import ALL the objects from each tab into the new file.
3) In the Database tab go into design mode of the offending table. Add a column called OldPK. Set as Number, Long Integer.
4) Create an Update query that copies setting the value of OldPK to the original PK (the autonumber field).
5) Back in the database tab Select the bad table and select Copy and Paste. In pasting select Structure only.
6) Open the copy in Design Mode and delete the Autonumber field and save then table. Then recreate the Autonumber field and close design mode.
7) Create an Append query that appends all the fields EXCEPT the Autonumber from the bad table to the new copy. Rename the old table by adding "bad" to the name and then rename the copy so it has the original name.
8) Create an Update Query that joins the New table to each child table on OldPK to the Foreign key field in the child. Set the value of the FK to the new autonumber field. Repeat this for each child table.

This should fix it hopefully. The main issue is that you will be changing the value of the PK in each record in the new table, so you have to update the FK field in all the children to the new value.

All your queries, forms, reports, etc. shouldn't change and shouldn't be affected.

Hope this helps,
Scott<>

Ok Scott I need some extra help here.

I have got to step 4, all the tables etc are imported, that was easy. I have never done an update query before so I am unsure as to how to go about it. In new query view I added the table and put the PK field and Old PK field, then I need some criteria, and that is where I am stuck. My original PK field (the autonumber one) is simply ID the OldPK is as you say, Number and long integer.

So what do I do now?

The rest of the instructions I seem to be able to follow. I am familiar with Append queries so that should be no problem

Thanks for all your help





Answer -
Right click on the upper pane in Query design mode. Select Query Type, then Update. A row will be added in design mode labeled Update To. In the OldPK column type [ID] in that cell. The SQL should look like this:

UPDATE table SET [OldPK] = [ID]

Scott<>

P.S. let me know how it works out. One thing to remember, you are working with a COPY, so you will still have your original databse if something goes wrong.


Hi Scott
Sorry its taken a while but I am having problems. Step 8 is causing the problem, I obviously haven't got to grips with the update query thing. I have been taking the new table Oldpk and the fk from another table and doing the same as you told me to do in step 4. I have done that for each table that has a relationship. When I try to open the form it just keeps asking for the ID, like in a parameter form.
Do I need to change all the queries that are used for the form?
If you have any ideas where I am going wrong then it will be great if you let me know. One thing I did notice was that the PK in the updated new table was the same value as on the old PK. Is that how it is supposed to be?

Corinne

Answer
hmmm, Unless the numbering was sequentially the same when you appended the old data to the new table, the new PK should have been different from the OldPK. So something may have gone a bit screwy here. Can you send me the database (dafiles@optonline.net), Zip it first.

Scott<>