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://docs.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
CodePudding user response：
The right process is the one implemented by SSMA for Access: https://www.microsoft.com/en-us/download/details.aspx?id=54255
After creating matching tables and migrating the data the last step is to replace the Access tables with ODBC linked tables to the SQL Server so your Access-based forms and reports work directly with the SQL Server data.
Having a linked server from SQL Server to Access is not needed.
CodePudding user response：
This is more a comment than an answer but is too long for a comment.
Try the following steps:
- Using SSMS create a database
- With that database (right mouse click) use task/import data and connect to your Access database using the Access connection type
- Select the table(s) to copy (and copy them)
- In your Access front end, connect to the SQL Server database using an ODBC connection and link the source tables
You can then read/write to them directly