AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
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.
