Transfer Spreadsheet w/ autonumber

[Guest]

I am transferring a spreadsheet into Access using the transfer spreadsheet
function. How can I add a field to the imported table that is an autonumber
field using VBA code?

I have duplicate records in the spreadsheet and want it to assign a
different number to each of these, so a primary key wouldn't be valid for any
other type of field but an autonumber.

Thanks for your suggestions

 

[Tim Ferguson]

I am transferring a spreadsheet into Access using the transfer
spreadsheet function. How can I add a field to the imported table
that is an autonumber field using VBA code?

Build the table first with all the correct fields as well as the new AN
field. Then insert the data with an append query rather than a maketable.

Hope that helps


Tim F

 

[Guest]

This is a good suggestion, but what you didn't know is this table changes the
number of fields from month to month. I am trying to get around creating a
table with all applicable fields at year end. I believe adding an ID through
code should fix this. I have a general idea, however was hoping there was
some generic code in VBA that will work.

 

[Tim Ferguson]

This is a good suggestion, but what you didn't know is this table
changes the number of fields from month to month.

First of all, it does not help to post the same question in multiple
threads at the same time. It wastes time, confounds and confuses, and
generally makes people Cross and Tired. That said..

I am trying to get
around creating a table with all applicable fields at year end.

I should think so too. Transferring data into a database is a queue to
getting the design right for a database. Good spreadsheet design is not the
same as good database design -- if anything, the two are inimical. First
stop should really be to recast your spreadsheet data into normalised data.
After that, the query will almost certainly reduce to a trivial problem.

Hope that helps


Tim F