How do I setup a connection to get data from unity catalog into MS Access?

[Brammer88]

Dear community,

Im trying to setup a connection to get data from a catalog / underlying tables from that catalog and load it directly into MS Access 365 (latest version). Is it possible to do this? What would be the best practice? 

Thanks,

Bram

[Yeshwanth]

@Brammer88 have you tried using PowerBI? Do you think it can help with your use case?

I am attaching the link which has the steps to connect with PowerBI: https://docs.databricks.com/en/partners/bi/power-bi.html#connect-power-bi-to-databricks

[Brammer88]

Thanks, yes we are using powerbi too, but we have a specific requirement for which we should be able to connect directly to our legacy MS access databases too. 

[jakubk]

I have set it up in Excel where users can pull data from databricks directly

Unfortunately it looks like access doesn't easily support DSNless connections via a URL so you're gonna have to set up a user or system odbc DSN using the simba spark driver

https://www.databricks.com/spark/odbc-drivers-download

Then to use it in access - New data source > From other sources > odbc database > select your simba databricks odbc connection

the connection string (assuming azure databricks and SSO/MFA) 

Driver=Simba Spark ODBC Driver;Host=[redacted].azuredatabricks.net;Port=443;HttpPath=/sql/1.0/warehouses/[redacted];SSL=1;AuthMech=11;Auth_Flow=2;Catalog=samples;Schema=default

how you configure it depends on how you want to connect. the personal access token way is described here - https://stackoverflow.com/questions/75147926/how-do-i-create-dsn-of-azure-databricks

we use SSO via azure AD so i selected oauth2.0 and set oauth type to browser based auth code

I quickly tested this and it worked fine, i was able to import databricks tables into access

Hope that helps

[Luise7x7]

Thank you so much. I've been beating my head against this issue for ... years. Your solution worked, and has saved me massive amounts of further head beating. You rule.

[Brammer88]

Hi @jakubk ,

Thanks for your response, I have tried to make a connection via spark simba odbc driver and it works. So that is great, thanks. I also have been able to connect via oauth2.0. 

The only thing I cannot achieve via access is to change to tables from inside the unity catalog, i.e. I can only see tables in the hive_metastore and cannot change the default / or to any other catalog. I cannot find where I need to configure the connection string, can you help me to achieve this?

Btw from Excel I do see all catalogs if I connect via the same odbc driver. Also, I would be keen to learn how you setup a DSNless connection via Excel, if you could give me any pointers to documentation that would be much appriciated. 

Thanks again! 

Bram

[jakubk]

I can't help with the hive metastore only in access - are you sure you're using a databricks endpoint that has unity catalog support? I could see my UC in both access and excel

dsnless in excel

From ODBC > advanced options > paste in your connection string

I just choose custom/default as the auth type for oauth2.0 and leave the login/password box blank

[njcheng]

Hi! @jakubk:

I have the same issue that I cannot achieve via access is to change to tables from inside the unity catalog, I can only see tables in the hive_metastore and cannot change the default / or to any other catalog. The same DSN is working fine to connect UC in excel. Which is the version of your ms access db? thanks

[Stefania003]

I have the same issue. Has anyone been able to find a solution?

[Stefania003]

I have the same issue as well. The odbc connection works fine in excel but when I use it with ms access all I can see is the hive_metastore. Has anyone here found a solution? @Brammer88 did you resolve your issue? If you found a solution I'd really appreciate if you could share it. Thanks so much.

[jakubk]

@njcheng @Brammer88 

I went back to have another look - i had the same experience where i couldn't see the UC tables in any tool that used the oauth connection type (back in early sept) - but tried it again just now and i can see UC tables again in access

Which endpoint are you connecting to? I'm using sql warehouse classic

Did you try using a PAT instead of oauth?

I dont really have any other suggestions, sorry

[njcheng]

Interesting! I found a workaround worked:

1. Add any table in hive_metastore.
2. Update the connection string of the ODBC data source in Linked Table Manager, as mentioned by @jakubk .  Make sure the catalog and schema are one with target table in unity catalog:

Driver=Simba Spark ODBC Driver;Host=[redacted].azuredatabricks.net;Port=443;HttpPath=/sql/1.0/warehouses/[redacted];SSL=1;AuthMech=11;Auth_Flow=2;Catalog=XXX;Schema=XXX

3.  Click the table in linked table manager, then click relink to update with target table name  in unity catalog

4. Rename table name in All Access Object Panel

[Stefania003]

Thanks so much. It just does not work for me. I can't find a place in Access where I can paste in the connection string. I found a workaround by connecting to the table in the unit catalog via Excel and then linking the Excel table in Access. Ugly but it works

[atrev66]

Hey guys,

See here for a solution to this issue. You can specify the catalog when you setup the odbc driver.

ODBC data source to connect to a Databricks catalo... - Databricks Community - 96354