Problem:
I have table with an autonumber field. I delete the table's records via a Delete Query and then run several append queries. Problem is that the autonumber field does not reset (you'd have to compact after deleting records to do that - not practical). The autonumber field values would eventually be very, very, large numbers, and I assume one day cause a crash.
Solution:
Pretty simple really. I have an identical table with no records ever entered into it. It stays that way. Before appending, I simply copy over the existing table with this:
DoCmd.SetWarnings False
DoCmd.CopyObject , "MyTable",acTable, "MyTable1"
DoCmd.SetWarnings True
MyTable is the table being overwritten with the virgin table MyTable1. Autonumber is now reset.
This is obviously specific to tables where you don't care if the records are deleted. Hope this helps someone.
I have table with an autonumber field. I delete the table's records via a Delete Query and then run several append queries. Problem is that the autonumber field does not reset (you'd have to compact after deleting records to do that - not practical). The autonumber field values would eventually be very, very, large numbers, and I assume one day cause a crash.
Solution:
Pretty simple really. I have an identical table with no records ever entered into it. It stays that way. Before appending, I simply copy over the existing table with this:
DoCmd.SetWarnings False
DoCmd.CopyObject , "MyTable",acTable, "MyTable1"
DoCmd.SetWarnings True
MyTable is the table being overwritten with the virgin table MyTable1. Autonumber is now reset.
This is obviously specific to tables where you don't care if the records are deleted. Hope this helps someone.