decimal639
New member
- Local time
- Today, 02:50
- Joined
- Dec 2, 2016
- Messages
- 4
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.
"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.