Importing Excel data using auto ID field

[kleivakat]

I wanted to append some data from an Excel spreadsheet into an existing
Access table. I created the spreadsheet with exactly the same columns as the
table, including a column called "ID", which in Access is the primary (key)
field and set up as an auto-numbered field. The import would not work unless
there was data in that column in Excel. I looked at the autonumbers in
Access, and then went to Excel and manually added the next numbers in that
column. The data imported fine.

My problem is now that I'm adding another record to the end of my Access
table, the autonumber option is giving me an unusual result. Rather than the
next number in sequence, which would be 736, the autonumber field comes up as
"37489924", then Access autonumbers the next record as 37489925. I can't
seem to get back to the original sequence of numbers. Also, just as a
comment, several records within the table were deleted, so the while the
records are generally in sequential order, I actually a few less records in
the table. Would that make any difference in how the autonumbering works?

Thanks for any help you can provide.

 

[Sprinks]

I can't explain how the import affected the autonumber, but, yes, deleting
records will affect the sequence, and Access will attempt to reuse numbers
whose record has been deleted. For this reason, an AutoNumber primary key is
not useful to provide a sequence of records; it merely provides a unique
identifier for each record.

Most of the time this is fine; merely create a query that sorts the records
in the order you wish, such as by date and time. If you wish to keep the
records in a strict numerical sequence, and not reuse numbers (for example,
for Purchase Orders), you will have to write code to assign the number.

Hope that helps.
Sprinks

 

[Arvin Meyer]

Some how, the autonumber got changed to from Sequential to Random. If it's
just a single table with no related records, Create a new empty table with
all the correct fields and append all the records (sans the ID column).

If there are related records it's more complex and involves breaking the
relationship, changing the name of the field, removing the autonumber
attribute, adding new columns in each table, appending the main table
records to the new table, updating related tables, and recreating
relationships. Then testing and cleaning up. Lot's of work with many
possibilities for doing serious damage.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[kleivakat]

A little more digging through previous posts indicates that I shouldn't be
concerned that the numbers are sequential. Sequence isn't a problem for me,
but there sure is a large gap between 736 and 37 million something. Is this
perhaps not a problem afterall? Should I just ignore it?

 

[Arvin Meyer]

I can't explain how the import affected the autonumber, but, yes, deleting
records will affect the sequence, and Access will attempt to reuse numbers
whose record has been deleted. For this reason, an AutoNumber primary key is
not useful to provide a sequence of records; it merely provides a unique
identifier for each record.

Sorry, since version 2000, Access does not reuse deleted numbers unless the
entire table is deleted and the database is compacted. Prior to that it
would reuse deleted numbers only as far back as the highest number still in
the database, after a compact.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access