Best choice for updating table from Excel data

[sagain2k]

I ran into an issue when trying to update an Access table with data imported from a source Excel worksheet. The Access table (call it "PSData") has for the first column an ID field that autonumbers the records. The Excel worksheet that will regulary be imported to update the PSData table does NOT have an ID column (otherwise the fields are the same and in the same order).

When I tried importing the excel worksheet data into the PSData table, all the data came in fine except for the autonumbered ID column: the last record in the PSData prior to import was 487; after importing from Excel (which had no ID column), it put sequential numbering starting from something like 509900545, 509900546...etc... for all the new records.

How do I go about importing the Excel data so that the autonumbered ID column will continue with the sequential numbers for all the newly appended records?

Someone had recommended importing the Excel data first into a Temporary table, then doing an Update Query to update the Access table. I started to try that (created an empty table called "Temp", imported the records, but then wasn't sure how to correctly make an Update Query work to all all the data to the PSData table. I would still need it to autonumber the continued sequence of ID numbers.

Whether it's by directly importing or via an Update Query, I'd appreciate suggestions on how to get this to reliably update the data. It would be nice to know the best method for this kind of updating.

Thanks!

 

[TheToeOfPooh]

One option I have been shown which counteracts this -
Use linked tables.
In your database go to Tables
In the grey work area right click
Select Link Tables
Select e.g. Text Files
Step through the process as prompted by the Link Wizard.
At the relevant step you are prompted to choose the Data Type. - I think this is where you may be able to overcome your problem.

Bernard