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.

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

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

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

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

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

View more solutions

Comments

[user1251858]

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

[Igor Turman]

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.

[user1251858]

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

[Igor Turman]

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

[G_Hosa_Phat]

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.