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

[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