Autonumber Corruption after each Compaction and Repair

[FoFa]

You could put a MSGBOX "FUNCTION COMLPETE",,"Done" as the last executed line for some feedback.
I would say like this:
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
AutoNumFix = lngKt
msgbox "FUNCTION COMPLETE",,"Done"
End Function

 

[bugleboy]

I got the same 0 when I hit the Enter key in the Immediate window. What does that mean? I thought there was supposed to be a dialog box asking to change the table or not.

 

[RuralGuy]

Allen's code returns a 0 if it did not find any AutoNumbers to repair.

 

[bugleboy]

I get the same result, but there are still ~80 records in the table that have either a negative autonumber or a huge positive autonumber that starts at 29737662 (there are only 550 records in the db). I thought this function was supposed to fix those.

The tables are not attached tables, and the database is not split as I created a new DB and imported external data. The db is not split.

 

[RuralGuy]

From Allen's site:

The code below checks all tables in your database, and offers to fix any where the Seed of the AutoNumber is less than zero or below the existing values. The function returns the number of tables that were changed - zero if none had the problem. It does not alter the values of any duplicate values in the column.

Let me ask some questions. Do you have this Autonumber in any other table as a Foreign Key? What would happen is we replace *every* AutoNumber with a completely different number? Would your system barf? Does this table have Referential Integrity enabled with any other table?

 

[bugleboy]

The autonumber in the Main table is not a foreign key in any of the other three tables.

I don't know what would happen if I replaced the autonumber with another number. How would I do that and still generate a unique record ID?

There are no relationships between the Main table and the other three tables, so there is no referential integrity enforced.

Should there be a relationship between Main and the other three tables (which I added after splitting the db in order to autocalculate/populate some fields in the Hazard section.

See attached gif for table design info.

 

[bugleboy]

Thanks Everybody!

I give up on this one.

I appreciate everybody's help, and I'd still like to know what is not working, but I went ahead and reverted back to 12/29/06 (before the autonumbers got screwed up) and just added the 80 records back into the db. I'll spend a few more hours updating the records and be done with it.

Moral of the story? I will not take my database offsite and try to synchronize it again. I will wait until our company gets a VPN for remote access to the server.

Once again, thanks to Keith G., Ruralguy, and FoFa for all you help!