Connect to Microsoft Access with ODBC from Windows

Introduction

This article explains how to connect to Microsoft Access with ODBC from Windows 10.

It covers the following topics:

  1. How to connect to Microsoft Access (with code samples).
  2. How to setup ODBC:
    • Configure ODBC using the ODBC Data Source Administrator:

      We recommend using system DSNs (available to all users).

Connect to Microsoft Access [top]

We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples.

Follow these steps to connect:

  1. Create a new channel to try out the code:
    • Create a channel like this:
      • Name: DB Access Connection (or similar)
      • Source: From Translator
      • Destination: To Channel
    • Alternatively use an existing channel:

      Your channel will need a From/To Translator or a Filter component.

  2. Connect to a database using ODBC:
    • Paste this code into the Translator:

      If your script uses connection object methods conn:query{} or conn:execute{}to connect to a remote Microsoft SQL Server database, the location of the remote host is specified as part of the name parameter in the db.connect{ } command. If you use conn:merge{} to connect the remote host is specified as part of the name parameter in the conn:merge{} command itself.

      function main()
      local conn = db.connect{   
      api=db.ACCESS, 
      name='your_odbc_server_name',
      user='',      -- use empty string for integrated security
      password='',  -- use empty string for integrated security
      use_unicode = true,
      live = true
   }

   conn:execute{sql='SELECT * FROM <your table>', live=true}
end
    • The name is the name of an ODBC source that you create using the ODBC Administrator.
  3. Adapt the code to your requirements.

Setup ODBC [top]

To set up a new ODBC data source for Microsoft Access:

  1. Install the latest Microsoft Access Database Engine Redistributable:

    At the time of writing this was the 2016 version. This version contains 64 bit ODBC drivers.

  2. Open the 64 bit ODBC Administrator:

    Windows 10 (64 bit version) supports 32 and 64 bit ODBC sources — always use the 64 bit ODBC Administrator.

    • Search for ODBC and choose ODBC Data Sources (64 bit):
      search for ODBC
    • Right Click and choose Run as Administrator:
      run as administrator
  3. Open the System DSN tab and click Add:
    odbc system dsn
  4. Choose the latest Access ODBC driver and click Finish:
    Choose the latest Access ODBC driver
  5. Enter the ODBC credentials:
    • Use any Name and Description you prefer.
    • Click Select and browse to choose your Access database file.
    • Alternatively you Create a new database.
    • Click OK to create the ODBC data source:
      Enter Access DB credentials
  6. Your Access ODBC source should now be ready to use.

Troubleshooting [top]

  • Problems with connection settings:

    Speak to your DBA (database administrator) for general connection issues like: User name and password, Database Server network name etc.

  • Using integrated security:

    Using integrated security is the usual way to connect to a Microsoft Access database. If this does not work speak to your DBA (database administrator) about the user credentials you should use to connect to the database.

If you need more help please contact us at support@interfaceware.com.

More Information [top]

Tagged: