How to Import Data to MS Access Using SQL Stored Procedures

Importing Data to MS Access

Importing data into a Microsoft Access database using stored procedures is an often overlooked topic. While many resources exist to import data from an Access database into a Microsoft SQL Server database, moving data the other way is not as common. Essentially, a "linked server" will be the vehicle of choice for importing data into the Access database. Then, a stored procedure can readily perform imports and exports freely between the SQL Server database and the Access database.

Things You'll Need

• Microsoft Access database
• Microsoft SQL Server 2000 or greater
• Microsoft Jet database driver (commonly installed on most Windows computers)

Instructions

Configure and Test the Linked Server

1. Connect to the SQL Server and create a new linked server. This can be done using the following SQL script, which will create a new liked server called "AccessDB." (The value after @datasrc is the path to the Access database on the computer that is running SQL Server.)

   EXEC sp_addlinkedserver
   @server = 'AccessDB',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'C:\Path\to\Access\Database.mdb'
   GO

2. Test the linked server by selecting from an existing table in the Access database (this is optional). For example, if there is a table called "Table1" in the Access database, the following SQL script will select all data from it.

   SELECT *
   FROM OPENQUERY(AccessDB, 'SELECT * FROM Table1')

3. If necessary, you can remove the linked server using the sp_dropserver procedure.

   EXECUTE sp_dropserver 'AccessDB'

Importing Data into the Access Database

1. To import data into an existing table in the Access database, the INSERT OPENQUERY(...) syntax must be used. For example, if the SQL Server table is called SrvTable and the Access table is called Table1 and both tables contain the same columns, the following SQL script will copy the data from the SQL Server table into the Access table.

   INSERT OPENQUERY(AccessDB, 'SELECT * FROM Table1')
   SELECT * FROM SrvTable

2. If necessary, you can also update existing data in the Access database from the SQL Server using the UPDATE OPENQUERY syntax, as seen below.

   UPDATE OPENQUERY(AccessDB, 'SELECT * FROM Table1')
   SET Col1 = 'Testing...'

3. You can also delete data from the Access database using the DELETE OPENQUERY syntax. (Note the escaped single-quotes [''] inside the OPENQUERY statement.)

   DELETE OPENQUERY(AccessDB, 'SELECT * FROM Table1 WHERE Col1 = ''Testing...''')

4. Finally, create a stored procedure that utilizes any combination of the OPENQUERY statements to accomplish your data import task. For example, to copy all records from the SrvTable to Table1 the Access database, then update Col1 to "Testing...", use the following SQL script.

   CREATE PROCEDURE CopyToTable1 AS BEGIN
   INSERT OPENQUERY(AccessDB, 'SELECT * FROM Table1')
   SELECT * FROM SrvTable

   UPDATE OPENQUERY(AccessDB, 'SELECT * FROM Table1')
   SET Col1 = 'Testing...'
   END

Tips & Warnings

• Linked servers can also be created from within a stored procedure as long as they have a unique name

• The Jet database driver is not designed for high-transaction or high-volume throughput, so this is not recommended for situations where performance is critical.

References

• MSDN: OPENQUERY (Transact-SQL)