CODEWITHSUNDEEP
excelms-accessimport
user1251858
user1251858

Reputation: 313

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

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.

Upvotes: 11

Views: 70492

Answers (18)

Shangwu
Shangwu

Reputation: 1520

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.

Upvotes: 0

Tuncermuncer
Tuncermuncer

Reputation: 1

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.

Screen shot of uninstalled security update

Good luck with solving.

Upvotes: 0

PorchMonkey
PorchMonkey

Reputation: 11

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.

Upvotes: 1

Maciej Lipinski
Maciej Lipinski

Reputation: 185

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.

Upvotes: 0

user2627727
user2627727

Reputation: 21

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

Upvotes: 2

Diksha
Diksha

Reputation: 1

Solved for Windows 7:

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

Upvotes: 0

BusinessAlchemist
BusinessAlchemist

Reputation: 714

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.

Upvotes: 12

alexey
alexey

Reputation: 801

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;")

Upvotes: 2

akshay saravanan
akshay saravanan

Reputation: 21

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.

Upvotes: 2

Séb Cô
Séb Cô

Reputation: 89

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.

Upvotes: 0

Bob
Bob

Reputation: 1

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.

Upvotes: 0

bharat1010
bharat1010

Reputation: 35

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

Upvotes: 0

jb_
jb_

Reputation: 11

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.

Upvotes: 1

Singaravelan
Singaravelan

Reputation: 839

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.

Upvotes: 0

Tobias
Tobias

Reputation: 29

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!

Upvotes: 1

Dave
Dave

Reputation: 1234

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.

Upvotes: 0

RHiggins
RHiggins

Reputation: 41

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

Upvotes: 4

Igor Turman
Igor Turman

Reputation: 2205

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.)

Upvotes: 3

Related Questions