Import excel data to access with an auto number field

[surfdragon]

Hi,



This could be a long one so please bear with me.......

Whilst carrying out some work on a database last week I inadvertently deleted the back end (aaaaaaaaaaah).
Due to a lack of IT support there is now no chance of recovery (we were led to believe all our databases were backed up daily, wrong!)

I have an old version of the back end which I had backed up a while back, and an Excel spreadsheet which is a direct copy of the table (completely up to date). However I am having trouble changing the spreadsheet to a direct copy of the table as one of the fields which is the primary key is an auto-number field.

My first question is should an append query allow me to do this?
If not can it be done?

Many thanks and please go gently as I only have a limited grasp of access

[June7]

Why is an autonumber field causing issue? When you import data it is creating primary keys that don't agree with foreign key in dependent records?

It is possible to force the value saved into autonumber field. Do this test. Create a table with an autonumber field and couple other fields and maybe 10 records then copy that table - Structure Only. Run code in VBA immediate window:

CurrentDb.Execute "INSERT INTO Table2 SELECT * FROM table1"

Table1 and table2 should be identical. Delete a couple of records from table2. Run the code again. Tables should still be identical.

Set a link to spreadsheet and run code similar to above.


After doing an edit like this, good idea to run Compact & Repair.

[Minty]

If the Spreadsheet has everything in it then simply import it as is into a new table.
Rename you old data table and then change your new table name to the original data table name. (Make sure you have Auto-Correct names option switched off.!)

Now change the field type to of the ID field to Primary key and then to Autonumber, and you should be good to go.

[June7]

Can't change a field to autonumber type (even if there is no data in it). Just tried it.

[Minty]

@June - You are correct - I'm sure you used to be able to to do that in earlier versions.

@OP apologies, the only way is to have an empty table and then append query as per June's instructions.

[June7]

Make table query also works. Don't have to pre-build table. Issue is won't be able to immediately see the new table. Run C&R.

CurrentDb.Execute "SELECT * INTO Table2 FROM Table1"