getting an Unexpected error from external database driver (1) when importing data from excel to access

Question

I have a 2010 Excel file with a sheet that spans 34864 rows and 1387 columns. I'm trying to import it into Access 2010 with the import wizard, but when I select that sheet, Access goes unresponsive, and after several seconds gives me a

"Unexpected error from external database driver (1)"

Is this due to the size of the sheet or is there something in the cells that stops it from working. Other sheets from the same file import with no problem.

Nicosia

Suggest you try a test at 1/2 size import, and then move up if that's successful. – Leena

I am also getting same error with EXCEL 2010 xlsx file when my file is on non editable mode. After making enable editing able to upload.Any solution for this ? – Speck

Answer 1

You may experience this error after installing security patch KB4041681. See this MSDN entry. In my case, replacing Microsoft.Jet.OLEDB.4.0 provider with Microsoft.ACE.OLEDB.12.0 helped.

Dentilabial

Answer 2

I just experienced this problem too and found a very simple, easy solution. I noticed that my spreadsheet had a custom name so I decided to see if that was causing the problem. I changed it to the default name of "Sheet1" and, sure enough, it worked!!

Kumler

Answer 3

Check out this for Access 2010 specification: http://office.microsoft.com/en-us/access-help/access-2010-specifications-HA010341462.aspx.

In your case, it might be number of columns, so try to import less than 255 first. Also, it might be the size of the data in the columns or data types (importing text into numeric etc.)

Permittivity

it's down to 72 columns now, and I removed the majority of elements so there are only 1863 rows now, that still won't load into access interestingly enough, if I copy the data from that sheet to another new sheet, the new sheet will load (with 1863 rows and 72 columns), but if I copy the sheet that's giving me a hard time, that copy won't load – Nicosia

It sounds like a "left over" data that causes the problem. When you copy 1863 rows, you copy exactly what you see/want. When you copy a Sheet, you copy ALL the data (beyond 1863 rows). E.g. you might have some data in the row #18000 cell#55 that is in WHITE color. – Permittivity

but I highlighted all those rows and pressed "delete", shouldn't that delete whatever's there? or is this some different kind of data? – Nicosia

Delete should work. Make sure you also highlight and delete all columns (73+) as well – Permittivity

Answer 4

Download and install the Microsoft Access Database Engine 2010 Redistributable, and then modify the DB connection strings in Microsoft Excel to use ACE as a provider.

Change (example):

Provider=Microsoft.Jet.OLEDB.4.0

to:

Provider=Microsoft.ACE.OLEDB.12.0.

Microsoft is working on a resolution and will provide an update in an upcoming release.

Baca

One thing to note here: You pretty much have to use the 2010 version, regardless of what version of Office you may have installed on your computer. I tried to download/install the 2016 version of the Access Database Engine Redistributable, but it wouldn't install on my machine, apparently because I have the Office Desktop applications installed from my O365 subscription. Apparently, the CTR (Click-To-Run) components interfere with the installation of higher versions of the redistributable. – Bamboo

Answer 5

Use this

OleDbConnection conObj = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\a.XLS;Extended Properties=Excel 8.0;")

instead of this

OleDbConnection conObj = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\a.XLS;Extended Properties=Excel 8.0;")

Drayman

Answer 6

A solution that has worked for me when tackling this issue is to have the Excel File where the Import / Export is being executed open when the Import & Export is happening.

The issue appears to be caused by the Patch preventing data transaction from occurring between CLOSED .xls files and other external database applications. Having the excel file open addresses this issue.

Some sample code examples below to highlight what works and what doesn't with VBA:

FAILS

wbTarget.SaveAs strFilename, xlExcel8
wbTarget.Close    
ExportSheetToDB strFilename, strSheetName, "tblTemp"

WORKS

wbTarget.SaveAs strFilename, xlExcel8
ExportSheetToDB strFilename, strSheetName, "tblTemp"
wbTarget.Close

Neves

Answer 7

Save your Excel sheet to a 'Comma delimited' .CSV file, and then upload it as Text File. For me, this works fine.

The problem is that in .xls(x) all kind of lay-out issues are present. Converting it into .CSV removes all lay-out related mark up and converts in into 'raw' data.

all the best!

Barbiebarbieri

Answer 8

Along the lines of RHiggins answer:

In my case Jet OleDb.4.0 threw this error (oledbconnection.open()) because the name of a worksheet in a workbook (.xls) was too long.

Ministration

Answer 9

There seems to be a bug between Excel and Access. In some instances, I have to open the Excel file, click save, and then close the file. Then I can import it without error into Access.

I have yet to find a work around for this for VBA automation.

Caerphilly

Answer 10

In my case the spreadsheet was linked to another spreadsheet. I was importing a subset of the original sheet. I created a new sheet and copied the data column by column to notepad and then to the new sheet removing the links. There was something in the links which was causing the problem.
BTW: it was this one set of data since I've done this import sequence from this one spreadsheet to this database more than 50 times. Halving the data worked for the first (larger) half of the data, but not for the second.

Frodina

Answer 11

hi I am also facing this error when import .xlsb file. After that i copied the contents to another xlsx file then import this xlsx file.

Rozina

Answer 12

I was getting this error when importing from a XLSB file, save the file as XLSX file and then import, should work

Attic

Answer 13

Another issue that is discovered is if the excel file is saved as a binary excel worksheet just resave it as an excel workbook and it loaded fine.

Almaraz

Answer 14

I had the exact same error. The spreadsheet was created from another software (SAP). Since it was not created by Office, Excel was not able to read it (!?!). I have to open them in Excel, save it, and then load it in Access and it works! As a worst case scenario, let's say you have dozens of files, you could just open and close them by VBA code before import.

Parasang

Answer 15

I had same issue, after getting a security update in Windows7 this error occurred. We have too many excel files to perform an open/close operation so I decided to try other ways.

1. Return a restore point when Access worked fine: It did not work in my case. The only change in software configuration is a security update and it seems security update still causes problem.

2. Reducing rows, columns etc: It did not work for me; first file that access tried to reach had 10k rows, reducing this rows to 3 was not the solution.

3. Trying to modify connection string: It did not work for me, it is not very reasonable as well; connection has been working for years, suddenly why would it stop? In some cases it does but not this time.

4. Uninstalling most recent security update worked for in my case. Here is the uninstalled security update.

Good luck with solving.

Dexter

Answer 16

Solved for Windows 7:

Uninstall Security Update KB4041681 and KB4041678 both(Related to Excel and Jet/ACE provider)

Wiese

Answer 17

In my case (I has the same error) the problem was that I had the Access DB stored on a network drive which ran out of space (not enough free space for the import to finish). I freed up space and compacted the DB; the error didn't appear again.

Fordone

Answer 18

I encountered this error when I imported a large XLSX file. The solution is to open the XLSX file by Excel first. Then start Access import from External Data.

Pressurecook