add autonumber field after import

[DS]

I am using a macro (transfer spreadsheet command) in Access 2003 to import a
spreadsheet from Excel 2003. It works very well. However, once the data is
in an Access table, I would like to add an autonumber column as it is not
done automatically. Is there a way this can be done with a macro so the
process is automated?

Thanks in advance and have a great day.

 

[Allen Browne]

Create a new query.
Switch it to SQL View (View menu.)
Paste in something like this:
ALTER TABLE MyTable ADD COLUMN ID COUNTER;
Use your own table name instead of MyTable.
Save the query.

Use RunSQL in your macro after the TransferSpreadsheet to run this query.

 

[DS]

Thanks Allen...
This is a great start, however it seems as if I'm doing something wrong.
When I paste the Alter table text, give it my table name and attempt to save
it gives me a syntax error. Any other thoughts?

 

[Allen Browne]

Try adding square brackets around your table name.
That is necessary in some cases (e.g. if the name contains a space.)

You won't be able to do this if the table already contains an AutoNumber, or
if there is already a field named ID.

If that doesn't work, paste exactly what you are trying to do.

 

[DS]

Is this supposed to be a select query? Do I need to include the table at the
top of the query before I convert to SQL mode? I keep getting syntax errors.
Sorry. This doesnt seem to be helping. Have a nice day anyway. I'll look
elsewhere for suggestions.

 

[Allen Browne]

No: it is not a SELECT query.
It is a DDL query.

There are other solutions, but executing a DDL query will be the simplest.
If you want further help on this concept, here's a bunch of examples:
http://allenbrowne.com/func-DDL.html

All the best.