New to MS Access - Excel import to Access

[AGASI999]

Hi,

Im quite new to MS Access. Recently Ive been trying to import an excel sheet into an MS Access database and append 100s of records from excel to access(doing from the front end GUI is a pain in the neck for my team).But there seems to be a primary key violation everytime I try to import the data from excel. Ive created a set of fields in excel to match the ones in the database(including a unique primary key id,which generally is autogenerated in the database),but the value I have in the primary key field in excel doesnt seem to be accepted by the primary key field in the database. I keep getting a primary key/indexing error.Is there a way I could copy the data into access by say, deactivating the primary key constraints for a while to allow the values Im inserting from the excel sheet? Any help on this will be highly appreciated...

A. Nair

 

[MStef]

Hello Nair!
Look at "DemoImportXLSA2000.zip".
There you have got a MDB, and an excel spreadshit "TblXls1".
In MDB you have got a "Query1ImportXLS", (make table query),
and a link on TblXls1 (tables).
Run a query, it'll make a "TblXlsAccess" (table).
Now you can put a primary key in this table.
IMPORTANT; make a relink on TblXls1 (your Path name).

 

[Lister]

I am assuming you are using the import wizard to move the Excel Sheet into an access table. If so, drop the “Key” column altogether on the Excel Sheet. Yes, all other column headers and types must line up with the table that you are importing the data into. But access dose not need Excel to tell it what the primary Key value is if access is using an autonumber.
It will add the id numbers automatically as the data is imported.

Hope this helps.