Adding autonumber field using a macro

  • Thread starter Thread starter Beginner
  • Start date Start date
B

Beginner

In a macro, I import an excel spreadsheet to a new table
and then run a query to manipulate the data(adding
fields, calculating values etc).

Is there any way I could add a field with the type
autonumber to the table in a macro.

I followed the suggestion of creating a table with the
required autonumber field and then appending the data from
the table(which does not have this field). It simply
doesn't work-I get error message-cannot append all records
due to validation rule violations.

Any other way to do this. I would really appreciate this.

Thanks in advance
Beginner
 
-----Original Message-----
In a macro, I import an excel spreadsheet to a new table
and then run a query to manipulate the data(adding
fields, calculating values etc).

Is there any way I could add a field with the type
autonumber to the table in a macro.

I followed the suggestion of creating a table with the
required autonumber field and then appending the data from
the table(which does not have this field). It simply
doesn't work-I get error message-cannot append all records
due to validation rule violations.

Any other way to do this. I would really appreciate this.

Thanks in advance
Beginner
.
I don't know why your getting the error message as I use
Click to expand...
that method all the time. Make sure all your field in both
tables are the same type.
 
Thanks for your reply

I tried to do the same with a new set of tables and
queries and it works fine and I go back to the original
tables , it gives the same error message for some reason I
do not know.

Anyway the method works for me now.

Thanks again
 
Beginner,

A clue to the location of the problem whould be the number of records
the error message relates to. All or most of the records can't be
appended, or only 1 or a few? There are a number of possible
scenarios... duplicated values in a field which is supposed to be
unique, a zero-length string in a field which does not allow this
(controlled by the Allow Zero-length property of the field in table
design), trying to insert text data into a field with a Number data
type, etc.

- Steve Schapel, Microsoft Access MVP
 
I figured it was because of the difference in the format
of some fields of the source and destination tables

Thanks for all your replies...
 
Back
Top