Migrate MS Access back-end to SQL Server

Question

I am incredibly frustrated!

I have been trying for months to migrate a MS Access database to SQL Server and nothing has worked. I need to be able to edit data on an Access front-end and the SQL Server back-end.

I've been able to copy a table, but edits on one end are not applied on the other. I'm severely limited in what applications I can download -- it takes at least a week for our IT department to install a new app. I've tried using SSMS 18 (just had it installed today) to create a linked service.

I use "Microsoft Office 12.0 Access ..." (16.0 is not available) as the Provider and leave Product Name and Provider String blank -- I get this error for either a .mdb or a .accdb file.

TITLE: Microsoft SQL Server Management Studio

The linked server has been created but failed a connection test. Do you want to keep the linked server?

ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "2022_08_24". (Microsoft SQL Server, Error: 7302) For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-7302-database-engine-error

The generated script is

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'2022_08_24', @srvproduct=N'', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\Users\liptonj1\OneDrive - Southern California Edison\Documents\Word Test_be.accdb'
GO
EXEC master.dbo.sp_serveroption @server=N'2022_08_24', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'2022_08_24', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'2022_08_24', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'2022_08_24', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'2022_08_24', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'2022_08_24', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'2022_08_24', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'2022_08_24', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'2022_08_24', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'2022_08_24', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'2022_08_24', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'2022_08_24', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'2022_08_24', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'2022_08_24', @locallogin = NULL , @useself = N'False'
GO

Answer 1

Ok, there is ALSO a 3rd option.

Access 2010 was the LAST version of Access to have a built in SQL server migration tool.

So here we are in 2022 12 years later!!!

but, Access 2010, and in fact Access 2007? It has a BUILT IN migration tool!!!

This is on the 2007 menu:

So, AFTER 2010, you ARE strong suggested to use the SSMAA - a seperate download and install.

However, Access 2003, 2007, and 2010? They have a BUILT-IN migration tool to sql server.

I don't have a 15 year old version of Access kicking around anymore, but you may very well want to try and use that tool. Just make sure you DO NOT use or pick the "ADP" option.