Greg S
asked on
Contents of fields in 0 records were deleted/lost
Hello,I am having an error when I try to import data in a spreadsheet to an Access table using the following VBA code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xm
The error message that I get is:
"Access was unable to append all the data to the table. The contents of fields in 0 record(s) were deleted and 0 record(s) were lost due to key violations"
Attached is a screenshot of the error message.
I am importing to a temporary Access table, and then running an append query to transfer the contents in the temp table to the final destination table in Access. The contents of the temp table are cleared after this second step. The error message is related to the first step (Excel to temp table) only.
If I choose to proceed anyway on the error message, the data transfers without any issues. Interestingly, I only get this error sometimes.
I looked at data types and there are no issues. I am not using any autonumber fields in the destination table
Any help will be greatly appreciated.
-Greg
Gregory Miller
Possibly, you have a column index or group of columns as an index which does not allow duplicate and in the data you are importing, it is violating that index rule. You are possibly losing at least one record of imported data each time you see this message.
ASKER
Hi Gregory,
I only have one field in the temp Access table that is required and indexed, but with "duplicates OK" selected. All other fields are not required and not indexed. Does this help?
-Greg
I only have one field in the temp Access table that is required and indexed, but with "duplicates OK" selected. All other fields are not required and not indexed. Does this help?
-Greg
ASKER
Also, when I choose to proceed anyway, all 75 records in the Excel file are transferred to the Access table. No records are lost.
Another thought is that you might have a field in your import data set that has a special character that access does not like. Thins like ", ', /, %, #, !, etc. Those can exists in Excel but have special meaning in Access.
ASKER
I checked and the only special characters in the Excel file are ":" (a field formatted as a time data type) and "-" (in text fields, and also a field formatted as a date data type).
I am not certain if you can duplicate your MDB for testing but does the file that gives you the random error produce the error everytime you import it or is it still only random. In other words, if you import a file and you get the error and you import it two more times, does it give you the error each time? If you import it 100 times, does it give the error 100 times or randomly?
Check your "range" argument: "Output$"
This is typically a "hardcoded" range.
So if the different import (Excel) files have fewer records than this range, ...then the error you are reporting will result.
(because of the "missing" records/rows.)
Typically you can leave the Range argument out, ...to avoid confusion, ...and Excel will simply bring in all the records.
Also note, ...If you are recycling the same Excel file, ...the "Last cell" might still be set to the "larger" range (if the new range is smaller), ...even after the output range argument is deleted from your code.
To clear the "presumed" last cell in an Excel file, ...you must select all the rows below the last row and select
Home-->Editing>Clear All
...Do the same for the columns, ...to be sure...
;-)
JeffCoachman
This is typically a "hardcoded" range.
So if the different import (Excel) files have fewer records than this range, ...then the error you are reporting will result.
(because of the "missing" records/rows.)
Typically you can leave the Range argument out, ...to avoid confusion, ...and Excel will simply bring in all the records.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xm...unless for some reason you are sure that you will always have the exact number of records for each import...l, "TempTable", strInputFileName, True
Also note, ...If you are recycling the same Excel file, ...the "Last cell" might still be set to the "larger" range (if the new range is smaller), ...even after the output range argument is deleted from your code.
To clear the "presumed" last cell in an Excel file, ...you must select all the rows below the last row and select
Home-->Editing>Clear All
...Do the same for the columns, ...to be sure...
;-)
JeffCoachman
ASKER
Thanks for all your help Gregory! I will experiment further and see if I can make any progress.
ASKER
Thanks Jeff. My Excel input file always has multiple worksheets, one of which is always named "Output." The "Output$" in my VBA code tells the DoCmd.TransferSpreadsheet function to only transfer contents from the "Output" tab of the Excel file.
Greg S
ok, ...sorry, I thought it was "OutputS"
;-)
...and you are sure the error triggers on the transferspreadsheet code, and not on the Append query?
...If the imported Excel file has an index set to "Yes, duplicates OK", try setting this index to "No" before running the append.
Like Gregory Miller, ...I would need a sample/example db (and a sample spreadsheet) that exhibits this issue, to see if I/we can recreate this issue...
Keep us posted
;-)
jeff
ok, ...sorry, I thought it was "OutputS"
;-)
...and you are sure the error triggers on the transferspreadsheet code, and not on the Append query?
...If the imported Excel file has an index set to "Yes, duplicates OK", try setting this index to "No" before running the append.
Like Gregory Miller, ...I would need a sample/example db (and a sample spreadsheet) that exhibits this issue, to see if I/we can recreate this issue...
Keep us posted
;-)
jeff
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.