Problems importing Excel spreadsheet into an existing table

  • Thread starter Thread starter Epilepsie Montreal Metropolitain
  • Start date Start date
E

Epilepsie Montreal Metropolitain

To whom it may interest:

I'm trying to import an Excel spreadsheet into an existing
Access 2000 table and am having troubles. I'm past
getting an list of errors for offending fields, and now am
getting a balloon message stating that all my records
are being erased because of primary key violations.
I've run some examples using smaller spreadsheets with
the same basic types of fields (which work!) and I'm
getting readable data in the resulting tables. The autonumber
field seems to work in my examples without errors--what
could I be doing wrong. The spreadsheet has about 3000+
records--so I don't believe it's too big. Any suggestions?

Thanks for your help,
Dave H
 
Dave,

The nature of this message has to do with duplicates or problems in the
primary key field of the table in Access. Does the data that you are
importing have a PK already? If the PK in Access is an AutoNumber, you can't
try to append any data to that field from the Spreadsheet. If it will not
let you append any records, I have a feeling that you are trying to poke
something into the AutoNumber field.

Gary Miller
 
Thanks for the response, Gary...
Dave,

The nature of this message has to do with duplicates or problems in the
primary key field of the table in Access. Does the data that you are
importing have a PK already?
Click to expand...

The data in the Excel spreadsheet has a column which numbers the
records from 1 to 3000+, and the destination table (I copied only the
structure of this table so there are no existing records in it) has a
corresponding primary key field which is an autonumber.

If the PK in Access is an AutoNumber, you
can't
try to append any data to that field from the Spreadsheet. If it will not
let you append any records, I have a feeling that you are trying to poke
something into the AutoNumber field.
Click to expand...

It works for my smaller test tables that I have been experimenting
with: the autonumber field accepts the numbers in the spreadsheet
column, starting at 1 (for the copied structure destination table). So
what else could be going wrong--remember that there are no longer
any error messages logged for the fields to be imported! I can't see
what is different between my test tables and the offending larger
table?
 
Off the top of my head, you may be getting away with it in the small table
because your new table has never had a record in it so the autonumber
counter is ready to put in a 1 and your excel data starts with one and is
absolutely sequential. I would have thought that you would have bombed on
that one too.

If your PK in the Access table is an autonumber, this is really not going to
work trying to poke pre-existing numbers into it from another source.

If you need to get the numbers in from the excel data, try putting them into
a different field in Access and let Access generate its own PK for them like
it wants to do. If it has already assigned out a number that is coming in,
it will not let you duplicate it and will reject the record.

If you are getting 'key violation errors', I am positive that this is the
root of your problem.

Gary Miller
 
Back
Top