Add auto value to Access table

Question

I've an import from Excel to Access. It works pretty well. My only problem is that after the import there is no column with auto value (primary key).

This is my import:

Private Sub Befehl1_Click()
    DoCmd.TransferSpreadsheet acImport, 10, _
       "Tabelle1", "C:\Users\u054535\Desktop\Mappe1.xlsx", True, "Tabelle2!A1:H13"
End Sub

Does somebody know how to add a column with an auto value to the import? Is it even possible?

score 0 · Answer 1 · answered Jan 31 '20 at 07:11

0

Options:

import to an already existing table
run an ALTER TABLE action SQL after import
use an import specification

answered Jan 31 '20 at 07:11

June7

19,874
8
24
34

score 0 · Answer 2 · answered Jan 31 '20 at 08:03

Try this:

Private Sub Befehl1_Click()
    DoCmd.TransferSpreadsheet acImport, 10, _
       "Tabelle1", "C:\Users\u054535\Desktop\Mappe1.xlsx", True, "Tabelle2!A1:H13"

    'Add an AutoNumber column
    CurrentDb.Execute "ALTER TABLE Tabelle1 ADD COLUMN ID COUNTER"
End Sub

Add auto value to Access table

2 Answers2