SSMS Import of MS Access accdb external component has thrown an exception (System.Data)

Question

I've been using SQL Server Management Studio for some time to import tables from MS Access into SQL Server.

Now I get an error

External component has thrown an exception. (System.Data)

when I choose an Access database to import from. This worked only a couple of weeks ago, any ideas what might have changed?

I only have Office 2016 installed. The full error is:

External component has thrown an exception. (System.Data)

---

Program Location:

at System.Data.OleDb.DataSourceWrapper.InitializeAndCreateSession(OleDbConnectionString constr, SessionWrapper& sessionWrapper) at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at Microsoft.SqlServer.Dts.DtsWizard.DTSWizard.GetOpenedConnection(WizardInputs wizardInputs, String connEntryName) at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnLeavePage(LeavePageEventArgs e)

Answer 1

Hi @Richard Williams,

Please try to install both 32bit and 64bit version for access.

You may download 32-bit and 64-bit access drivers from this source https://www.microsoft.com/en-us/download/details.aspx?id=54920 first, and then go to terminal -> go to downloads (or where you downloaded the access drivers to) -> run "accessdatabaseengine_X64.exe /quiet" -> then run "Accessdatabaseengine.exe /quiet"

Or refer to Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

This did solve the problem but I had to uninstall the existing accessdatabaseengine_X64.exe and accessdatabaseengine.exe installations before reinstalling them. Then, a couple of days later the same error was occurring in SSMS. I tried both version 19 and version 20, same issue. So I uninstalled and reinstalled accessdatabaseengine_X64.exe and accessdatabaseengine.exe again. The problem has gone away, again! Can anyone explain this instability?

Answer 3

This solution worked but now, each time I try to use import I have to uninstall and then re-install accessdatabaseengine_X64.exe and accessdatabaseengine.exe again before the import works. Is there some way of stopping this from happening?