ODBC data source to connect to a Databricks catalog.database via MS Access Not Working

[DRock]

When using an ODBC data source to connect to a Databricks catalog database via Microsoft Access, the tables are not listing/appearing in the MS Access database for selection.

However, when using the same ODBC data source to connect to Microsoft Excel, the catalog database tables are listing and are accessible without any issues.

Is this a known issue? If not, could you please provide documentation outlining the steps to resolve this Databricks ODBC to Microsoft Access issue?

Your assistance is greatly appreciated.

[VZLA]

To resolve the issue of tables not listing in Microsoft Access when connecting to a Databricks catalog database via ODBC, ensure the catalog is specified in the connection string, as Access doesn’t automatically handle catalog settings through the DSN alone.

Use the following connection string format:

DSN=your_dsn_name;Catalog=your_catalog_name;

This allows Access to recognize the catalog and display the tables. Note that this setting must be defined in the connection string rather than directly within the ODBC DSN configuration.

[DRock]

Thank you for the reply, but the recommendation did not resolving the issue.

[VZLA]

Can the ODBC trace logs be shared? Make sure to mask any sensitive data.

[DRock]

The Trace log info is below.  Also, I find it interesting that the log is making reference to hive_metastore (see below), which is not the catalog explicitly stated via the Server Side Properties. 

MSACCESS 3818-164c ENTER SQLAllocEnv

HENV * 0x000000EE247C49A8

MSACCESS 3818-164c EXIT SQLAllocEnv with return code 0 (SQL_SUCCESS)

HENV * 0x000000EE247C49A8 ( 0x0000026296DF7BB0)

MSACCESS 3818-164c ENTER SQLAllocConnect

HENV 0x0000026296DF7BB0

HDBC * 0x000000EE247C4A68

MSACCESS 3818-164c EXIT SQLAllocConnect with return code 0 (SQL_SUCCESS)

HENV 0x0000026296DF7BB0

HDBC * 0x000000EE247C4A68 ( 0x000002628CCDF010)

MSACCESS 3818-164c ENTER SQLSetConnectOptionW

HDBC 0x000002628CCDF010

SQLINTEGER 103 <SQL_LOGIN_TIMEOUT>

SQLPOINTER 20

MSACCESS 3818-164c EXIT SQLSetConnectOptionW with return code 0 (SQL_SUCCESS)

HDBC 0x000002628CCDF010

SQLINTEGER 103 <SQL_LOGIN_TIMEOUT>

SQLPOINTER 20

MSACCESS 3818-164c ENTER SQLSetConnectAttrW

SQLHDBC 0x000002628CCDF010

SQLINTEGER 30002 <unknown>

SQLPOINTER [Unknown attribute 30002]

SQLINTEGER -3

MSACCESS 3818-164c EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS)

SQLHDBC 0x000002628CCDF010

SQLINTEGER 30002 <unknown>

SQLPOINTER [Unknown attribute 30002]

SQLINTEGER -3

MSACCESS 3818-164c ENTER SQLDriverConnectW

HDBC 0x000002628CCDF010

HWND 0x0000000000091ABC

WCHAR * 0x00007FFA70F830D8 [ -3] "******\ 0"

SWORD -3

WCHAR * 0x00007FFA70F830D8

SWORD -3

SWORD * 0x0000000000000000

UWORD 1 <SQL_DRIVER_COMPLETE>

MSACCESS 3818-164c EXIT SQLDriverConnectW with return code 1 (SQL_SUCCESS_WITH_INFO)

HDBC 0x000002628CCDF010

HWND 0x0000000000091ABC

WCHAR * 0x00007FFA70F830D8 [ -3] "******\ 0"

SWORD -3

WCHAR * 0x00007FFA70F830D8 <Invalid buffer length!> [-3]

SWORD -3

SWORD * 0x0000000000000000

UWORD 1 <SQL_DRIVER_COMPLETE>

DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed (0)

MSACCESS 3818-164c ENTER SQLGetInfoW

HDBC 0x000002628CCDF010

UWORD 9 <SQL_ODBC_API_CONFORMANCE>

PTR 0x000000EE247C4A70

SWORD 2

SWORD * 0x000000EE247C4A60

MSACCESS 3818-164c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)

HDBC 0x000002628CCDF010

UWORD 9 <SQL_ODBC_API_CONFORMANCE>

PTR 0x000000EE247C4A70 (2) <SQL_OAC_LEVEL2>

SWORD 2

SWORD * 0x000000EE247C4A60 (2)

MSACCESS 3818-164c ENTER SQLGetInfoW

HDBC 0x000002628CCDF010

UWORD 6 <SQL_DRIVER_NAME>

PTR 0x000000EE247C4AB0

SWORD 200

SWORD * 0x000000EE247C4A60

MSACCESS 3818-164c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)

HDBC 0x000002628CCDF010

UWORD 6 <SQL_DRIVER_NAME>

PTR 0x000000EE247C4AB0 [ 46] "Simba Spark ODBC Driver"

SWORD 200

SWORD * 0x000000EE247C4A60 (46)

MSACCESS 3818-164c ENTER SQLGetInfoW

HDBC 0x000002628CCDF010

UWORD 46 <SQL_TXN_CAPABLE>

PTR 0x000000EE247C4578

SWORD 2

SWORD * 0x000000EE247C4520

MSACCESS 3818-164c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)

HDBC 0x000002628CCDF010

UWORD 46 <SQL_TXN_CAPABLE>

PTR 0x000000EE247C4578 (0) <SQL_TC_NONE>

SWORD 2

SWORD * 0x000000EE247C4520 (2)

MSACCESS 3818-164c ENTER SQLGetInfoW

HDBC 0x000002628CCDF010

UWORD 1 <SQL_ACTIVE_STATEMENTS>

PTR 0x000000EE247C4570

SWORD 2

SWORD * 0x000000EE247C4598

MSACCESS 3818-164c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)

HDBC 0x000002628CCDF010

UWORD 1 <SQL_ACTIVE_STATEMENTS>

PTR 0x000000EE247C4570 (0)

SWORD 2

SWORD * 0x000000EE247C4598 (2)

MSACCESS 3818-164c ENTER SQLAllocStmt

HDBC 0x000002628CCDF010

HSTMT * 0x000000EE247C44F0

MSACCESS 3818-164c EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)

HDBC 0x000002628CCDF010

HSTMT * 0x000000EE247C44F0 ( 0x0000026296DEA870)

MSACCESS 3818-164c ENTER SQLGetStmtAttrW

SQLHSTMT 0x0000026296DEA870

SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT>

SQLPOINTER 0x000000EE247C4440

SQLINTEGER -5

SQLINTEGER * 0x0000000000000000

MSACCESS 3818-164c EXIT SQLGetStmtAttrW with return code 0 (SQL_SUCCESS)

SQLHSTMT 0x0000026296DEA870

SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT>

SQLPOINTER 0x000000EE247C4440 (0)

SQLINTEGER -5

SQLINTEGER * 0x0000000000000000

MSACCESS 3818-164c ENTER SQLSetStmtAttrW

SQLHSTMT 0x0000026296DEA870

SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT>

SQLPOINTER 60

SQLINTEGER -5

MSACCESS 3818-164c EXIT SQLSetStmtAttrW with return code 0 (SQL_SUCCESS)

SQLHSTMT 0x0000026296DEA870

SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT>

SQLPOINTER 60

SQLINTEGER -5

MSACCESS 3818-164c ENTER SQLExecDirectW

HSTMT 0x0000026296DEA870

WCHAR * 0x00007FFA28681DF0 [ -3] "SELECT Config, nValue FROM MSysConf\ 0"

SDWORD -3

MSACCESS 3818-164c EXIT SQLExecDirectW with return code -1 (SQL_ERROR)

HSTMT 0x0000026296DEA870

WCHAR * 0x00007FFA28681DF0 [ -3] "SELECT Config, nValue FROM MSysConf\ 0"

SDWORD -3

DIAG [S0002] [Simba][SQLEngine] (31740) Table or view not found: hive_metastore..MSysConf (31740)

MSACCESS 3818-164c ENTER SQLErrorW

HENV 0x0000026296DF7BB0

HDBC 0x000002628CCDF010

HSTMT 0x0000026296DEA870

WCHAR * 0x000000EE247C4378

SDWORD * 0x000000EE247C4354

WCHAR * 0x0000026293375E50

SWORD 4095

SWORD * 0x000000EE247C4350

MSACCESS 3818-164c EXIT SQLErrorW with return code 0 (SQL_SUCCESS)

HENV 0x0000026296DF7BB0

HDBC 0x000002628CCDF010

HSTMT 0x0000026296DEA870

WCHAR * 0x000000EE247C4378 [ 5] "S0002"

SDWORD * 0x000000EE247C4354 (31740)

WCHAR * 0x0000026293375E50 [ 76] "[Simba][SQLEngine] (31740) Table or view not found: hive_metastore..MSysConf"

SWORD 4095

SWORD * 0x000000EE247C4350 (76)

MSACCESS 3818-164c ENTER SQLErrorW

HENV 0x0000026296DF7BB0

HDBC 0x000002628CCDF010

HSTMT 0x0000026296DEA870

WCHAR * 0x000000EE247C4378

SDWORD * 0x000000EE247C4354

WCHAR * 0x0000026293375F02

SWORD 4006

SWORD * 0x000000EE247C4350

MSACCESS 3818-164c EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)

HENV 0x0000026296DF7BB0

HDBC 0x000002628CCDF010

HSTMT 0x0000026296DEA870

WCHAR * 0x000000EE247C4378

SDWORD * 0x000000EE247C4354

WCHAR * 0x0000026293375F02

SWORD 4006

SWORD * 0x000000EE247C4350

MSACCESS 3818-164c ENTER SQLFreeStmt

HSTMT 0x0000026296DEA870

UWORD 1 <SQL_DROP>

MSACCESS 3818-164c EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)

HSTMT 0x0000026296DEA870

UWORD 1 <SQL_DROP>

MSACCESS 3818-164c ENTER SQLGetInfoW

HDBC 0x000002628CCDF010

UWORD 17 <SQL_DBMS_NAME>

PTR 0x000000EE247C4E10

SWORD 200

SWORD * 0x000000EE247C4E00

MSACCESS 3818-164c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)

HDBC 0x000002628CCDF010

UWORD 17 <SQL_DBMS_NAME>

PTR 0x000000EE247C4E10 [ 18] "Spark SQL"

SWORD 200

SWORD * 0x000000EE247C4E00 (18)

MSACCESS 3818-164c ENTER SQLGetInfoW

HDBC 0x000002628CCDF010

UWORD 0 <SQL_ACTIVE_CONNECTIONS>

PTR 0x000000EE247C4F64

SWORD 2

SWORD * 0x000000EE247C4F60

MSACCESS 3818-164c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)

HDBC 0x000002628CCDF010

UWORD 0 <SQL_ACTIVE_CONNECTIONS>

PTR 0x000000EE247C4F64 (0)

SWORD 2

SWORD * 0x000000EE247C4F60 (2)

MSACCESS 3818-164c ENTER SQLGetInfoW

HDBC 0x000002628CCDF010

UWORD 25 <SQL_DATA_SOURCE_READ_ONLY>

PTR 0x000000EE247C4F78

SWORD 20

SWORD * 0x000000EE247C4F60

MSACCESS 3818-164c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)

HDBC 0x000002628CCDF010

UWORD 25 <SQL_DATA_SOURCE_READ_ONLY>

PTR 0x000000EE247C4F78 [ 2] "N"

SWORD 20

SWORD * 0x000000EE247C4F60 (2)

MSACCESS 3818-164c ENTER SQLGetInfoW

HDBC 0x000002628CCDF010

UWORD 46 <SQL_TXN_CAPABLE>

PTR 0x000000EE247C4F68

SWORD 2

SWORD * 0x000000EE247C4F10

MSACCESS 3818-164c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)

HDBC 0x000002628CCDF010

UWORD 46 <SQL_TXN_CAPABLE>

PTR 0x000000EE247C4F68 (0) <SQL_TC_NONE>

SWORD 2

SWORD * 0x000000EE247C4F10 (2)

MSACCESS 3818-164c ENTER SQLGetInfoW

HDBC 0x000002628CCDF010

UWORD 29 <SQL_IDENTIFIER_QUOTE_CHAR>

PTR 0x000000EE247C4F78

SWORD 20

SWORD * 0x000000EE247C4F60

MSACCESS 3818-164c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)

HDBC 0x000002628CCDF010

UWORD 29 <SQL_IDENTIFIER_QUOTE_CHAR>

PTR 0x000000EE247C4F78 [ 2] "`"

SWORD 20

SWORD * 0x000000EE247C4F60 (2)

MSACCESS 3818-164c ENTER SQLAllocStmt

HDBC 0x000002628CCDF010

HSTMT * 0x000000EE247C4350

MSACCESS 3818-164c EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)

HDBC 0x000002628CCDF010

HSTMT * 0x000000EE247C4350 ( 0x0000026296D13150)

MSACCESS 3818-164c ENTER SQLGetStmtAttrW

SQLHSTMT 0x0000026296D13150

SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT>

SQLPOINTER 0x000000EE247C4290

SQLINTEGER -5

SQLINTEGER * 0x0000000000000000

MSACCESS 3818-164c EXIT SQLGetStmtAttrW with return code 0 (SQL_SUCCESS)

SQLHSTMT 0x0000026296D13150

SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT>

SQLPOINTER 0x000000EE247C4290 (0)

SQLINTEGER -5

SQLINTEGER * 0x0000000000000000

MSACCESS 3818-164c ENTER SQLSetStmtAttrW

SQLHSTMT 0x0000026296D13150

SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT>

SQLPOINTER 60

SQLINTEGER -5

MSACCESS 3818-164c EXIT SQLSetStmtAttrW with return code 0 (SQL_SUCCESS)

SQLHSTMT 0x0000026296D13150

SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT>

SQLPOINTER 60

SQLINTEGER -5

MSACCESS 3818-164c ENTER SQLTablesW

HSTMT 0x0000026296D13150

WCHAR * 0x0000000000000000 <null pointer>

SWORD 0

WCHAR * 0x0000000000000000 <null pointer>

SWORD 0

WCHAR * 0x0000000000000000 <null pointer>

SWORD 0

WCHAR * 0x00007FFA286FBAF0 [ -3] "'TABLE','VIEW','SYSTEM TABLE','ALIAS','SYNONYM'\ 0"

SWORD -3

MSACCESS 3818-164c EXIT SQLTablesW with return code 0 (SQL_SUCCESS)

HSTMT 0x0000026296D13150

WCHAR * 0x0000000000000000 <null pointer>

SWORD 0

WCHAR * 0x0000000000000000 <null pointer>

SWORD 0

WCHAR * 0x0000000000000000 <null pointer>

SWORD 0

WCHAR * 0x00007FFA286FBAF0 [ -3] "'TABLE','VIEW','SYSTEM TABLE','ALIAS','SYNONYM'\ 0"

SWORD -3

MSACCESS 3818-164c ENTER SQLNumResultCols

HSTMT 0x0000026296D13150

SWORD * 0x000000EE247C4358

MSACCESS 3818-164c EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS)

HSTMT 0x0000026296D13150

SWORD * 0x000000EE247C4358 (5)

MSACCESS 3818-164c ENTER SQLFetch

HSTMT 0x0000026296D13150

MSACCESS 3818-164c EXIT SQLFetch with return code 100 (SQL_NO_DATA_FOUND)

HSTMT 0x0000026296D13150

MSACCESS 3818-164c ENTER SQLFreeStmt

HSTMT 0x0000026296D13150

UWORD 0 <SQL_CLOSE>

MSACCESS 3818-164c EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)

HSTMT 0x0000026296D13150

UWORD 0 <SQL_CLOSE>

MSACCESS 3818-164c ENTER SQLFreeStmt

HSTMT 0x0000026296D13150

UWORD 1 <SQL_DROP>

MSACCESS 3818-164c EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)

HSTMT 0x0000026296D13150

UWORD 1 <SQL_DROP>

[VZLA · Accepted solution]

Right, that indeed seems to be the problem, ideally MS Access should be fetching the default correct values, but only newer clients are. Instead of our original approach, could you please:

1.- Open the ODBC Driver DSN Setup.

2.- Go to Advanced Options.

3.- Server Side Properties.

4.- "Add" both the databricks.catalog and/or databricks.schema, e.g.: databricks.catalog=myCatalog. Make sure both Convert Key Name to Lower Case and Apply properties with queries checkboxes are ticked.

5.- Save and retry with this new DSN connection.

Here's a similar post: https://community.databricks.com/t5/warehousing-analytics/how-do-i-setup-a-connection-to-get-data-fr...