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