SQL Query to import data from 50 Access ACCDB databases

Question

I am new to SQL, so please forgive my ignorance. I downloaded the free version of SQL Server Express Edition 2014 and have a 32 bit machine. I'm using Microsoft SQL Server Mgmt Studio. I am trying to setup a process to either link to or import 50 Access tables that are in 50 individual Microsoft Access 2013 databases (updated weekly) into SQL. I currently do this in an Access 2013 db but I am trying to replace the process. I downloaded the free version of SQL Server Express Edition 2014 and have a 32 bit machine. I'm using Microsoft SQL Server Mgmt Studio on my PC. Below is the code I put together. however, this is the result in the message window:

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 7403, Level 16, State 1, Line 8
The OLE DB provider "Microsoft.Jet.OLEDB.12.0" has not been registered.

I am a newbie so I would greatly appreciate no assumptions in knowledge. Thank you.

sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE WITH OVERRIDE;
GO

INSERT INTO TEST2.dbo.MyTest
SELECT * FROM OPENDATASOURCE(   
'Microsoft.Jet.OLEDB.12.0',
'Data Source="D:\data\EXCEPT2.accdb"')...Testtbl;
GO

Andre · Accepted Answer · 2015-11-24 21:51:35Z

1

Turns out I had to run the below code to get the sql to work. I also had changed 'Microsoft.Jet.OLEDB.12.0', to 'Microsoft.Jet.ACE.12.0',

USE [master] 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 
GO

edited Nov 24, 2015 at 21:51

Andre

27.7k7 gold badges43 silver badges85 bronze badges

answered Nov 24, 2015 at 21:03

SQLNewbie

113 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query to import data from 50 Access ACCDB databases

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related