Importing into Access from Excel (1 Viewer)

I am having issues with importing into Access from Excel. I am getting the following Error:
"Microsoft Access was unable to append all the data to the table
The content in 0 record(s) were deleted, and 0 record(s) were lost due to key violations"

All the fields in my Excel spreadsheet are in my db table and all the fields save 2 in my db table are in my spreadsheet. the 2 fields in the table are the ID field (Autonumber) and and old field that will be removed at the beginning of the new year. I have tried removing the ID field and I still get the error so I know that isn't the issue. I am using VBA code to do the import. It is as follows

Function move_files()

Dim strPath As String
Dim strFilter As String
Dim MyFile As String
Dim MyFolder As String
'Dim bCopySuccess1 As Boolean
'Dim bCopySuccess2 As Boolean
Dim sSourceFile As String
Dim sDestinationFolder1 As String
Dim sDestinationFolder2 As String
Dim sDestinationFile1 As String
Dim sDestinationFile2 As String

strPath = "P:\Job Costing Daily Sheets\"
strFilter = "xlsx"
MyFolder = strPath

sDestinationFolder1 = "P:\Job Costing Needing Import\"
sDestinationFolder2 = "P:\Job Costing Done\"

'Loop through all the files in the directory by using Dir$ function
MyFile = Dir$(MyFolder & "*." & strFilter)
Do While MyFile <> ""
sSourceFile = MyFolder & MyFile
sDestinationFile1 = sDestinationFolder1 & "JCS.xlsx"
sDestinationFile2 = sDestinationFolder2 & MyFile
Name sSourceFile As sDestinationFile1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblJobCosting", "P:\Job Costing Needing Import\JCS.xlsx", True
Name sDestinationFile1 As sDestinationFile2
MyFile = Dir$(MyFolder & "*." & strFilter) 'Goto the next file in the directory listing
Loop

End Function


I would appreciate any help since Microsoft seems to keep forgetting this even after I have reported it to them a number of times and even have a case number.

Oh, and all the field types match between Excell and Access. And I get the error even if the spreadsheet has no data values, just the field header line.

does the target table have keys, or no dupe indexes?

have you tried linking the excel file as an external table, then run an append query?

Yes the primary key and no dupe indexes. But that doesn't matter since I tried removing them and still got the error. When I did a test import to a new table, the new table nearly matched the original table. The only differences was in the size of the text fields and the number fields were created as double with auto decimal places versus Long integers. I think that must be where the problem is. What do you think?

Sometimes when Access is unable to complete an import it will create a table of exceptions for each record that failed. Did it do that this time? Because SOMETHING is clearly wrong, yet in the body of the message, nothing IS wrong (the individual error counts were zero).

The other thing that MIGHT be going on is that your spreadsheet had some blank lines following the last "real" line in the worksheet. If you export the sheet to a .CSV file you would detect that by seeing a bunch of lines that held nothing but commas or nothing but the sequence ,"","","","".....

If so, get to the last "real" line, then use the mouse to select the next line and several lines after that. Delete the rows and try again.

The_Doc_Man said:

Sometimes when Access is unable to complete an import it will create a table of exceptions for each record that failed. Did it do that this time? Because SOMETHING is clearly wrong, yet in the body of the message, nothing IS wrong (the individual error counts were zero).

Click to expand...

I have always assumed that as well but it reads that nothing has been deleted or lost which is true, you just haven't added anything. In my experience the failure to do anything is usually a type error.

I usually avoid this by creating a 'correct' table with all the correct number types etc. and then appending the records to it before copying them out of it to the correct place.

It imports everything fine, Just get that "error" for each workbook that imports. And it only imports the lines with information on them, not the empty lines. And it wouldn't make sense to delete all the empty lines because then I have to go into each workbook to delete lines when it is less time consuming to just hit the "yes" button for each workbook. When I imported into a new table it created one line for each record whether it contained data or not. If I wanted that I would need a new table for each days work and that wouldn't be cost effective.

If the import works, then in a sense you really don't have a problem other than perhaps turning off warnings when doing this.

However, I ran into something like this once. It was easy enough to open an Excel Application object, open the workbook, activate the 1st worksheet, and scan the rows to identify and remove the blank rows. After that, the import was trivial - and quiet. If you do it as part of the import process, you don't have to manually do anything no matter HOW many spreadsheets you have to import.

well I presume the blank rows are the reason why you are getting the errors

your spreadsheet import fails with the blank lines, because they don't have values for the ID field - but no actual records were deleted, because they are all null.

I bet if you changed your destination table to remove the autonumber field, and make it just a number, not required, then (depending on any other similar issues) the blank rows would come in.

ie. your spreadsheet has 200 rows, only 140 rows get imported so the error statement is correct

"not all your data was imported"
"the contents of zero records were deleted" - true.
"zero records were lost due to key violations" - also true because nulls are most likely not key violations.

Okay, I took what you said to heart and found the problem. Since the workbook has the format we need we had it protected and the user would fill it out. The problem is that we haven't ever had where all the lines were used. So I tested with deleting all the blank formatted lines after the user entered data and saved it, and then imported. NO ERROR MESSAGES!!! Now I just have to figure out a way to allow the used to delete the blank formatted lines without chancing the format being deleted.

Thanks a bunch all who provided an answer! Great work!!! WAAAAHOOO!!!