AutoNumber beginning with #1 after an Import Query

[Guest]

I have a table that I update frequently. I run a Delete Query which deletes
all records in the table. Then I import raw data from an Excel file into this
table. The table conatins an AutoNumber as the Primary Key. When the data
is imported the AutoNumber does not begin with 1, it will begin with the
highest number of records that the table had before Delete. An example would
be my table conatins 100 records, I delete them and then import. My
AutoNumber will beging at 100 and end at 200. I am scared the number will
increase to high after repeating these steps over and over again. Should I
be worried?

Thanks,
Matt

 

[Joseph Meehan]

I have a table that I update frequently. I run a Delete Query which
deletes
all records in the table. Then I import raw data from an Excel file into
this table. The table conatins an AutoNumber as the Primary Key. When
the data is imported the AutoNumber does not begin with 1, it will begin
with the highest number of records that the table had before Delete. An
example would be my table conatins 100 records, I delete them and then
import. My AutoNumber will beging at 100 and end at 200. I am scared the
number will increase to high after repeating these steps over and over
again. Should I be worried?

Thanks,
Matt

The number can get really high without a problem However it may be a
good idea to do a repair and compact (see database utilities under the tools
menu) from time to time.

Access like most databases dose not actually delete records when you
tell it to delete. It just hides them until you compact the database.
Normally it will not cause any harm.

I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[John Vinson]

I have a table that I update frequently. I run a Delete Query which deletes
all records in the table. Then I import raw data from an Excel file into this
table. The table conatins an AutoNumber as the Primary Key. When the data
is imported the AutoNumber does not begin with 1, it will begin with the
highest number of records that the table had before Delete. An example would
be my table conatins 100 records, I delete them and then import. My
AutoNumber will beging at 100 and end at 200. I am scared the number will
increase to high after repeating these steps over and over again. Should I
be worried?

The number will increment to 2147483647, then roll over to
-2147483648, and start counting up to zero. That gives you some 43
million imports before you run out of autonumbers; I suspect Access
will have been superseded by something even more powerful by then!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps