Import question

[Zygor]

I am importing a record in an excel file.
I am getting the following error.

"Microsoft Access was unable to append all the data to the table.

The contents of fields in 0 record(s) were deleted, and 1 record(s) were lost due to key violations.

*If data was deleted the data you pasted or imported doesnt match the field data types or the FieldSize property in the destination table.
*If records were lost, either the records you pasted contain primary key values that already exist in the destination table, or they violate referential integrity rules for a relationship defined between tables.
Do you want to proceed anyway?


I know why.

The record already exists. I'm trying to capture the err.number in my error routine, but it seems it's an access internal error. It shows no error number. I tried the on form error, but it never hits it. Any ideas how to capture this error??

 

[PHV]

No new error table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zygor]

No. No new error table.
I've gotten around this by turning warnings off and then back on, but I'd like to handle the error if I can.

 

[combo]

Guess that you export query result into excel file. Try to go to acces and execute the query (without export) here, to get plain table. See what is going on - usually field contents is lost if there are calculations on empty or wrong format data, access displays #Err here. In case of lost records, again, try to see in access what is exported, maybe you have a record of errors.

combo

 

[Zygor]

I'm not exporting. I'm importing a one row Excel file. It imports fine the first time. It's just that I want to capture the error if the user forgets they already imported it once.

 

[PHV]

Why not importing in a temporary table and then check in VBA if the record(s) may be appended to the real table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zygor]

PHV-
Thanks for your suggestion.

I imported the file (record) into a temp table. I then did a docmd.runSQL to append that record to my main table. I get the same, non trappable, error.

Did I miss what you meant by checking in VBA?

 

[PHV]

Either use the DLookUp function twice or play with a Recordset.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zygor]

Good advice PHV. I did it with 2 recordsets. Thanks.