How to create an ODBC connection to an MS Access Database that is saved on a local Hard Drive?

WilmerSerrano

How to create an ODBC connection to an MS Access Database that is saved on a local Hard Drive?

 

• I could not add a DSN because of limited admin privilages in my desktop <- Ruled Out
• I don't know the exact Configurations/Steps to put in the Connection Builder for MS Access
• I don't know the Syntax to put in the Connectrion String

CWebb

Hi @WilmerSerrano,

I'm sorry you're having trouble getting your data loaded into Domo. Microsoft Access is a bit tricky when it comes to ODBC.

 

To your points above, you're correct that to create a System DSN, you must have elevated permissions on the Windows PC. I've found that most IT departments are at least willing to grant temporary access so you can create the necessary System DSN. They can also create the DSN for you, after which you will have access to use that DSN in Workbench. This is the recommended method for connecting to Access databases.

 

With "Connection Builder", you enter details for your ODBC connection, such as login credentials, driver, server URL, database name, port, timeout value, and connection parameters. These details will depend on the configuration of your Access database. I would advise you get with the owner/DBA of the database if you'd like to use the connection builder. 

 

For connection strings, you can get a working sample from ConnectionStrings.com:

 

https://www.connectionstrings.com/microsoft-access-odbc-driver/

 

Depending on your Access version, the string will vary. Like the connection builder, you will need to know the details of each connection metric which can be obtained from the DBA or database owner.

 

Please let me know what other questions you may have.

Chronos

Is anyone able to help out with this request?

WilmerSerrano

No None yet... Would you be able to help?

CWebb

Hi @WilmerSerrano,

I'm sorry you're having trouble getting your data loaded into Domo. Microsoft Access is a bit tricky when it comes to ODBC.

 

To your points above, you're correct that to create a System DSN, you must have elevated permissions on the Windows PC. I've found that most IT departments are at least willing to grant temporary access so you can create the necessary System DSN. They can also create the DSN for you, after which you will have access to use that DSN in Workbench. This is the recommended method for connecting to Access databases.

 

With "Connection Builder", you enter details for your ODBC connection, such as login credentials, driver, server URL, database name, port, timeout value, and connection parameters. These details will depend on the configuration of your Access database. I would advise you get with the owner/DBA of the database if you'd like to use the connection builder. 

 

For connection strings, you can get a working sample from ConnectionStrings.com:

 

https://www.connectionstrings.com/microsoft-access-odbc-driver/

 

Depending on your Access version, the string will vary. Like the connection builder, you will need to know the details of each connection metric which can be obtained from the DBA or database owner.

 

Please let me know what other questions you may have.

WilmerSerrano

Thank you Cody!!! Appreciate your reply to this got it working.