Access auto-counter problem

[Cyberice]

Hi all,

Here’s my problem… I have a database that is in the office 2003 format and needs to transformed into the office 2010 format. I have brought all the tables, queries, forms, modules into a blank database and all was ok expect for one table…

The problem is that about 12 months ago the database “2003” crashed and we could not compact and repair it any more without it not working, I have a copy of that table prior to the crash and that can be compacted and repaired. If I were to bring this table “2003” into my new office 2010 database it works fine however the data is over 12 months old.

My questions are,
· In both tables column A is an auto-counter
· In both tables this auto-counter has got gaps between the numbers
· i.e. 1,2,3,4,5,6,7,9,10,11,12,14,15,16,17,18,20
· I have managed to get both the tables one in office 2003 and one in office 2010 the right amount on both entries and counter i.e. 2003 = 123456 entries and 2010 = 123456


· As the 2003 table is 12 months newer it has about 6000 more entries and some of these have missing gaps too i.e. 1000,1001,1002,1005,1006,1007,1008,1010
· I have found out that if copy and paste only one entire at a time it seems to work fine

I am trying to populate the working office 2010 table with the office 2003 table that is current… Can that be done by copy and pasting each entire on block, keeping in mind that I need to keep the both counts equal?

I will try and upload the sample tables tomorrow unless it can’t be done…

Kind regards,

[ItsMe]

If there is not a conflict with duplicating Key values (Autonumber field for instance) an update query will take the data from your old table and update your new table.

[June7]

I am confused. If both tables have the same number of entries (123456) then how does 2003 table have 6000 more entries?

Are there related tables using this autonumber field as a foreign key?

[Cyberice]

Hi there,

Apologies for not explaining myself… 2003 table has about 150,000 entries and 2010 table has about 145,000 entries both of the tables are equal at the 145,000 stage. I need to get the rest of the 5000 entries into my 2010 table. I know it sounds easy but it’s to do if the auto-number…
I will try and upload both tables tomorrow as this might make it clear.

Regards,

[June7]

Test with a copy.

Try copy/paste the records from the 145,000 mark.

[Cyberice]

Hi there,
I have and everything is ok… 146,000, 147,000 but if I try to do it on mass it throws it out…

· 2003 table 2010 table
· 145,000 info 145 145,000 info 145
· 146,000 info 146 146,000 info 146
· 147,000 info 147 147,000 info 147
· 148,000 info 148 148,000 info 148
Blank Record 149,000 info 149
· 150,000 info 149 150,000 info 150
· 151,000 info 150 151,000 info 151

[June7]

Throws out what? If there is no record in table 2003 with ID 149 then it won't create one in 2010.

If you don't want to retain the ID's from the 2003 table then don't include that field in the copy/paste.

[Cyberice]

Hi there,

Can I say thanks for all your help… I have fixed the problem, just appended the records to new table and now sorted!!!

Back up and running, again thanks.

I am sure this won’t be the last problem I have but at least I know were to come.

Regards,