Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

How to avoid the error "was unable to append all the data to the table" using VBA in Access 2003, mdb type file when importing an Excel 2003 file?

I am developing an Access 2003 VBA application using an MDB type file.

Within the Access application I import an Excel 2003 file with 4 worksheets into 4 Access tables defined as follows:

Field Names                     Field Type
-------------------               --------------
F1 through F14                Text with size of 255 and not a required field

My VBA code routine is as follows:

ssql = "delete * from [Bank Credits (A)]"
rst.Open ssql, con, 2, 2
ssql = "delete * from [Bank Debits (B)]"
rst.Open ssql, con, 2, 2
ssql = "delete * from [Book Debits (C)]"
rst.Open ssql, con, 2, 2
ssql = "delete * from [Book Credits (D)]"
rst.Open ssql, con, 2, 2

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Bank Credits (A)", filenm, False, "Bank Credits (A)!A:N"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Bank Debits (B)", filenm, False, "Bank Debits (B)!A:N"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Book Debits (C)", filenm, False, "Book Debits (C)!A:N"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Book Credits (D)", filenm, False, "Book Credits (D)!A:N"

I have attached a sample Excel file. I only have data in 1 column for each of the 4 worksheets because this is the ONLY column that is causing me an error condition.

When I click on a command button, the import process takes place and I get the following error message. Do you know how I can fix the attached Excel file to avoid this error from happening?

The first worksheet of the Excel file with the tab titled as "Bank Credits (A)" generates the following error message:

FinanicalReports application was unable to append all the data to the table. The contents
of fields in 3 records were deleted, and 0 record(s) were lost due to key violations
* If data was deleted, the data you pasted or imported doesn't match the field data types or the Field Size 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?

Yes    No      Help
1098-11-13-2012.xls.xls
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Instead of importing the sheets, link them as attached tables. Then you can run some validation on the data before importing them.

/gustav
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial