MS Access – How to Reach Linked Server on MSSQL

linked-serverms accessMySQLsql server

We have an MS Access DB that has been in place for 16 years in a Front End/BackEnd configuration. It is now running under the latest version of MS Access using ACCDB files. Since its inception, we have added the element of connecting to a remote MySQL server to access some of its tables R/O. This works fine. We create an ODBC connection to the remote MySQL server, then in Access we choose – External Data>>ODBC Database>>Link to the data source, select the ODBC driver and then select the tables we need. They then appear as linked tables with a globe icon.

For reasons too lengthy to get into, we have the need to move the MS Access Back End to an MSSQL server. So we have set up one on the LAN (SQL 2012). It works fine, and I can do basically the same type of ODBC connection in Access to reach any DB we create on that server.

We then use the SQL Server Management Studio to create a Linked Server of the remote MYSQL server. This works fine as well. Using the SSMS, we can see all the tables of the Linked server and even query data using the OPENQUERY method. We cannot seem to use the 4-part name ("servername.databasename.schemaname.tablename") to query the data. Any attempt to do so returns an error similar to this:

… contains no columns that can be selected or the current user does not have permissions on that object.

But the real question is – what would I do in MS Access to reach the Linked server? When I create an ODBC connection to the MSSQL server, all I can see is the DBs created there. I cannot see the Linked Server, therefore I cannot link any of its tables to MS Access. I have scoured the web, and some threads touch on this, but are not clear enough for me to figure this out.

Thanks in advance to anyone who can help.

Best Answer

As Sean Lange mentioned, MS Access cannot use a linked server which is only accessible from within the SQL Server. But MS Access is external to SQL Server.

If you think of MS Access as the Client in a 2-tier Client/Server project, then it should be clear that you can use the 3-part naming convention (ServerName.Schema.Object) to access the SQL Server.

If you have sufficient rights, you can update data in SQL Server from MS Access through the common use of T-SQL to manipulate SQL Server data.

If you want the SQL Server itself to fetch data from a MS Access database, then you could create a Linked Server in SQL Server that would reach out through a provider to get data from the MS Access.

https://msdn.microsoft.com/en-us/library/ms190479.aspx gives a sample provide setup from the Northwind database.

EXEC sp_addlinkedserver 
   @server = N'SEATTLE Payroll', 
   @srvproduct = N'',
   @provider = N'MSDASQL', 
   @datasrc = N'LocalServer';
GO
Related Question