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.