Snowflake ODBC Query Of MS Access Linked Tables

Question

I have a business user I am migrating to Snowflake that has many MS Access reporting processes that use MS Access Linked Tables that he is attempting to redirect to Snowflake through an ODBC connection. If his ODBC connection specifies a Database, he can see contents of the linked table. If he tries to link a table in a different Database, MS Access can find the table in list of objects but cannot open it. It is like the process to find the table is using the full data dictionary but when you try to link it, it forms a query that lacks the database qualifier.

We have tried most all qualification of including or omitting entries out of the ODBC connection properties including the database. We can think of two work-arounds but neither are ideal because there is a large user community and the reporting is complex and vast. The two backup options are to create a separate driver for each database or to create views in Snowflake under the same database that can be accessed.

Has anybody else encountered this issue and know a better solution? Is it the version of the ODBC driver for Snowflake (which I assume is the most current because the user followed a canned set of download directions created by our IT department).

Answer 1

Alter User CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX

This setting must be altered to add Linked Tables, it is not required if the Linked Tables have already been set up.

The default CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=false option in Snowflake produces strange behaviour in Microsoft Access when adding a Linked Table via ODBC.

Firstly, the list of tables to choose from appears to contain every database that is accessible to the user, but without database names. If there are two tables in different databases that have the same schema and table names, then when the table is added there is a dialog box explaining that a field cannot be added more than once.

This is caused by the following: https://community.snowflake.com/s/article/Connecting-MS-Access-to-Snowflake-via-link-table-shows-duplicate-tables-via-Snowflake-ODBC-driver

By default ODBC connections are not set up with any connection context, despite what is added to the database and schema fields in the ODBC setup process.

To rectify this, set the CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX option to true:

ALTER USER "MYNAME" SET CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=true;