UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> "....unable To Append All Data To Table" Message, Access 2016    
 
   
SargeUSMC
post Dec 3 2019, 09:44 AM
Post#1



Posts: 12
Joined: 7-December 12



So I have a database here with multiple tables......and when I try to import data to 1 specific table from Excel, the thing always tells me

"MS Access was unable to append all the data to the table"

"Contents of fields in 0 record(s) were deleted, and 0 record(s) were lost due to key violations.

I've been using this DB for years now to manage data, and this issue with this one table started occurring last year. I've been ignoring it, because it's not affecting the data, but I'd sure like to put an end to this, for the sake of whomever is going to take over when I go.

There are similar tables for managing different data. All are setup the same, ie short text for all fields except dates and currency.

Also, with the other tables now....not with this one....when I import data from Excel, I get blank records being created in the table after the import that I have to delete. That started last year too, and again, I've been ignoring it, but I'd sure like to get this worked out.

I should say I'm a novice level user.

Any assistance would be greatly appreciated.
Go to the top of the page
 
GroverParkGeorge
post Dec 3 2019, 10:35 AM
Post#2


UA Admin
Posts: 36,182
Joined: 20-June 02
From: Newcastle, WA


The problem here lies in the kind of data in the Excel source file.

There are two separate, but related problems.

When you first set this up and while it was working as expected, all of the data in all of the rows and columns of the spreadsheet were correctly matched up to the expected datatypes in the Access table. That's no longer the case.

The other problem has to do with with Excel's habit of handling "blank" rows differently from what we, as humans, might expect. What's going on there is that the rows being appended to your Access table are "blank" in Excel, but Excel still considers them to be part of the "used range" in the worksheet. This can happen if you delete values from cells in rows and columns, but do NOT deleted the actual rows or columns themselves.

One possible way to hand the latter is to just do as you are now doing--delete the records added to the Access table inappropriately. Another is to try to delete the "blank" rows from the Excel file before importing it, but how you go about that depends on how they are getting there.

Invest some time examining the data values in the Excel source file which are not being imported correctly into Access. I think you'll find something like dates being identified as numbers, or something like that. You may have to add some pre-importing steps to clean up the Excel data first. Or you might just bring everything into a temp file in Access and doing the clean up there before appending the data to the final destination tables. That latter approach is the one I have normally followed.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
SargeUSMC
post Dec 3 2019, 11:04 AM
Post#3



Posts: 12
Joined: 7-December 12



Thanks George,

"Invest some time examining the data values in the Excel source file which are not being imported correctly into Access." - Therein lies the rub. The data IS being imported correctly. That's why I don't understand what I'm seeing.

"One possible way to hand the latter is to just do as you are now doing--delete the records added to the Access table inappropriately. Another is to try to delete the "blank" rows from the Excel file before importing it, but how you go about that depends on how they are getting there." - If I look at the Excel data prior to importing into Access....and I look at the page break preview....only the rows I need to import are included on the page...does that make sense?

Frustrating. I don't understand why this one specific table is giving me this indication, when the process and setup are the same for all the other tables.

I'll do as you suggest.

Also...with this one table that is giving me fits.....it seems like if I import data to that table before doing any of the others...when I get to a different one...the import process is interrupted by "Subscript out of range" which leaves me hanging. I can't do anything until I re-start Access.

Got any ideas what that might be all about??


Go to the top of the page
 
GroverParkGeorge
post Dec 3 2019, 11:28 AM
Post#4


UA Admin
Posts: 36,182
Joined: 20-June 02
From: Newcastle, WA


As I said, there is probably some new data in that one data source. That can happen. I used to support a process which imports literally dozens of text files daily. As long as all of the data in all of the fields in each file complied with the standard for that file type, it all went smoothly. Every once in a while, though, a data point got dropped (it's "blank" in one cell) or a string value gets put into a field that should only hold numbers. That can happen because of user error, or it can be other things. When that happens, the process can fail. I managed to put in trapping that caught a lot of the more common errors, but you can't predict all of the things that can happen in a free-range environment.

The problem with "blank" Excel rows or columns is one that I first encountered years ago in a different environment. It happens when an Excel template workbook is populated with records, let's say you first append 100 rows of data. If that same worksheet is later emptied, i.e. all 100 rows of data are deleted, and then 50 new rows are added to the same worksheet, it now has 50 rows of data and 50 rows that appear "blank", but which Excel still thinks are part of the original 100 "used rows". Access imports those 50 blank rows as records with nothing in them. The solution was to always delete the "blank" ROWS and COLUMNS just before the import, and not count on "blank" rows being left out. Think of it as similar to the difference between "Null" and "Zero Length Strings". It's probably not the same thing, but that might be a good analogy to understand why it can happen. Excel considers rows and columns "used" if they have, or have had, data in them.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
SargeUSMC
post Dec 3 2019, 11:46 AM
Post#5



Posts: 12
Joined: 7-December 12



TY sir
Go to the top of the page
 
kfield7
post Dec 3 2019, 12:00 PM
Post#6



Posts: 1,024
Joined: 12-November 03
From: Iowa Lot


alternative to processing the blank rows in Excel is you can import into a temp holding table, then your update or append query leaves out any blank records, or you have a delete query on the temp table prior to the update or append query.
This allows more automation in the import process, rather than adjusting each Excel sheet.

Or, you can predetermine the import range by actually examining the data for text / numbers from a key field (column) in Excel. That, too, can be automated. You might need to have an exception for "skipped" rows?

Or, this link might be useful.

My preference is the first method, using a temp holding table. You can do other QA with that table before the final update or append query.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 10:10 AM