Importing data from MS Access into SQL Server with TSQL

Introduction
In my current client project i have to create an import procedure for data from a MS Access database. This MS Access database is protected with a Workgroup file (.mdw). The .mdw file stores usernames and passwords of users allowed to open a specific MS Access database and it must be linked to an Access database to be effective. In this blogpost i'll explain three methods of importing data from a MS Access database:

1. Simple MS Access database import.
2. MS Access database import with password.
3. MS Access database import with an .mdw file.

For importing a MS Access file i'll be using the OPENDATASOURCE statement. This provides ad hoc connection information without using a linked server name. 

Simple MS Access database
I've created a small MS  Access database with two lines. See the screenshot below:

You can retrieve the data from the Access file with the following code:



sp_configure 'show advanced options',1   
reconfigure with override   
go   
sp_configure 'Ad Hoc Distributed Queries',1   
reconfigure with override   
go  


SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb"')...test;
GO


sp_configure 'show advanced options', 1 
reconfigure with override   
go


sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure with override   
go

Resulting in:

MS Access database import with password
The next step is to load data from the MS Access file that is protected with a password.  First open de MS Access database in an exclusive mode.

Below you can see how you set the password in MS Access database.

The next step is to execute the following code :


sp_configure 'show advanced options',1   
reconfigure with override   
go   
sp_configure 'Ad Hoc Distributed Queries',1   
reconfigure with override   
go  


SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb";Jet OLEDB:Database Password=test;')...test;
GO


sp_configure 'show advanced options', 1 
reconfigure with override   
go


sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure with override   
go

Resulting in 

MS Access database import with .mdw file
And now we come to the final case in which the MS Access database is protected with a workgroup file. This workgroup file enables securing the MS Access database with encrypted PID's, usernames and passwords. This generates a unique code that MS Access uses to determine the permissions

You can create a Workgroup file with the menuoption Tools in MS Access. It will show the following window: The text is in the dutch language..

Next.

Select the relevant database objects:

Next

Next

Next

Next

Next

The following files are created : Test.mdb, Test.snp, Test.ldb, beveiliging.mdw and test.bak.

If you use the wrong mdw file an error occurs.

SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb";Jet OLEDB:System Database="E:\tmp\wrong.mdw";User ID=testuser;Password=test;')...test;
GO



OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 2

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

If you use the right MDW file  the data is returned. In the below snippet you'll see a disadvantage of using the OPENDATASOURCE statement: The name and password is visible.

sp_configure 'show advanced options',1   

reconfigure with override   

go   

sp_configure 'Ad Hoc Distributed Queries',1   

reconfigure with override   

go  

SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb";Jet OLEDB:System Database="E:\tmp\Beveiliging.mdw";User ID=testuser;Password=test;')...test;

GO

sp_configure 'show advanced options', 1 

reconfigure with override   

go

sp_configure 'Ad Hoc Distributed Queries', 0

reconfigure with override   

go

The result :

Conclusion
This blogpost is about reading data from a table in MS Access database, a MS Access database protected with a password, and a MS Access database protected with a mdw file.

The OPENDATASOURCE statement is less secure because the name and password should be entered in the statement. In one of the next blogposts i'll show the Linked server option.

Greetz

Hennie