getting an Unexpected error from external database

问题:

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.

回答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.

回答2:

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

回答3:

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

回答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.

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

回答6:

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!

回答7:

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.

回答8:

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.

回答9:

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.

回答10:

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

回答11:

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.

回答12:

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.

回答13:

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.

回答14:

Solved for Windows 7:

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

回答15:

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

回答16:

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.