How to get MS Access to reach a Linked server on MSSQL

Question

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.

Answer 1

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

Answer 2

After researching further, I have found my own answer to this question. From Access, I can create an ODBC connection to the MS SQL Server that contains the linked server. Even though the linked DB is not shown as one of the DBs available on the server, I can use the 4-part naming convention if I leave out the 2nd and 3rd parts (e.g. servername...tablename). This will only work if I first go to the properties of MSDASQL under Linked Servers->Providers (on the SQL server). There, 'Level zero only' must be checked.

I understand that normally I could continue to access the MySQL DB through ODBC separate from the MS SQL server, but the MySQL DB belongs to another company which has given us access to it only under 1 IP address, our company's office IP. Since we would like to start using the Access program remotely, it will have to be accessible through the MS SQL server which sits at our office, so all the requests go through the correct IP address.

Thanks for the feedback though.