Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-02-2016, 09:39 AM   #1
decimal639
Newly Registered User
 
Join Date: Dec 2016
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
decimal639 is on a distinguished road
Importing into Access from Excel

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.

decimal639 is offline   Reply With Quote
Old 12-02-2016, 09:43 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,157
Thanks: 0
Thanked 690 Times in 675 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Importing into Access from Excel

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?
Ranman256 is offline   Reply With Quote
Old 12-05-2016, 05:53 AM   #3
decimal639
Newly Registered User
 
Join Date: Dec 2016
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
decimal639 is on a distinguished road
Re: Importing into Access from Excel

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?

decimal639 is offline   Reply With Quote
Old 12-05-2016, 07:00 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,569
Thanks: 62
Thanked 1,196 Times in 1,096 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Importing into Access from Excel

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
Old 12-05-2016, 08:25 AM   #5
Tieval
Still Clueless
 
Join Date: Jun 2015
Location: UK
Posts: 412
Thanks: 54
Thanked 41 Times in 41 Posts
Tieval is on a distinguished road
Re: Importing into Access from Excel

Quote:
Originally Posted by The_Doc_Man View Post
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).
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.
Tieval is offline   Reply With Quote
Old 12-05-2016, 11:31 AM   #6
decimal639
Newly Registered User
 
Join Date: Dec 2016
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
decimal639 is on a distinguished road
Re: Importing into Access from Excel

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.
decimal639 is offline   Reply With Quote
Old 12-05-2016, 09:48 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,569
Thanks: 62
Thanked 1,196 Times in 1,096 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Importing into Access from Excel

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
Old 12-06-2016, 03:42 AM   #8
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,462
Thanks: 51
Thanked 949 Times in 918 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Importing into Access from Excel

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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 12-06-2016, 06:25 AM   #9
decimal639
Newly Registered User
 
Join Date: Dec 2016
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
decimal639 is on a distinguished road
Re: Importing into Access from Excel

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!!!

decimal639 is offline   Reply With Quote
Reply

Tags
access , excel , importing , microsoft , office

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
importing excel into access SoxPats83 Tables 2 05-13-2010 11:10 AM
Importing - Get Value From Access In Excel CharlesWhiteman Modules & VBA 2 04-29-2008 05:29 AM
importing excel into access coolnax79 General 3 04-04-2007 11:48 PM
Importing from Excel to Access sweetyo Tables 1 05-17-2004 02:13 AM
Importing from Excel to Access sweetyo Queries 1 05-12-2004 07:34 PM




All times are GMT -8. The time now is 09:17 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World