How to Import Data from a Microsoft Access Database to SQL Server

  Devin Manno        November 15, 2013

If you need to import data from a Microsoft Access database to SQL Server, one simple way is to setup the Access file as a Linked Server. You can add the linked server using the SQL Server Management Studio GUI, or through T-SQL commands. Run the sp_addlinkedserver procedure to add a Linked Server. Follow that with sp_addlinkedsrvlogin to add a login to the Access database.

If you’re working with Access 2002-2003 with an .mdb file extension, you can use the Microsoft.Jet.OLEDB.4.0 provider. If you’re linking to a newer Access file (2007+) with an extension of .accdb, you’ll need to use the Microsoft.ACE.OLEDB.12.0 provider. If that’s the case, download the 2007+ database engine and install it on your SQL Server machine: Microsoft Access Database Engine 2010. Once you have the correct provider, add the linked server:

DECLARE @AccessFileName nvarchar(32);
DECLARE @FilePath nvarchar(max);
DECLARE @CombinedPath nvarchar(max);
 
SET @AccessFileName = N'AccessDBFile.accdb';
SET @FilePath = N'D:\Data Files\Access\';
SET @CombinedPath = @FilePath + @AccessFileName;
 
IF  EXISTS (
     SELECT srv.name 
     FROM sys.servers srv 
     WHERE srv.server_id != 0 
     AND srv.name = N'MyAccessDB' ) 
BEGIN
     EXEC master.dbo.sp_dropserver 
          @server=N'MyAccessDB', 
          @droplogins='droplogins' 
END
 
EXEC master.dbo.sp_addlinkedserver 
     @server = N'MyAccessDB',  
     @provider = N'Microsoft.ACE.OLEDB.12.0',  
     @srvproduct = N'Access2007', 
     @datasrc=@CombinedPath

Next, setup a login mapping to the Access file. My Access file, AccessDBFile.accdb, had no username or password required. In this example, I wanted to map a local SQL Server user, LocalSQLAcctName, to the Access database. Since this Access database file did not require a username, I used a remote user called “Admin” with no password:

EXEC master.dbo.sp_addlinkedsrvlogin 
     @rmtsrvname = N'MyAccessDB', 
     @locallogin = N'LocalSQLAcctName', 
     @useself = N'False', 
     @RmtUser = N'Admin'
GO

Once you complete the above, you’ll see your newly linked server in SQL Server Management Studio:

If you’re having trouble browsing or selecting from the linked server, make sure the account you’re using (LocalSQLAcctName in the above example) has permission to EXECUTE ON sys.XP_PROP_OLEDB_PROVIDER:

USE master;
GO
CREATE USER [LocalSQLAcctName] FOR LOGIN [LocalSQLAcctName];
GO
GRANT EXECUTE ON sys.XP_PROP_OLEDB_PROVIDER TO [LocalSQLAcctName];
GO

You may also have to run the following and restart the SQL Server instance if receiving a Cannot create an instance of OLE DB Provider "Microsoft.ACE.OLEDB.12.0" error or a Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server error after completing the above steps:

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

You should now be able to query your Access database (note 3 dots after the name of the linked server):

SELECT * FROM [MyAccessDB]...[Table1]

Screenshot of SQL Server Management Studio and a Linked Server