Creating ODBC Connections for Jenzabar-linked Access Databases (Windows 10)

Step 1:  update your ODBC drivers

  1. Download the msodbcsql.msi file on this page under Files.  (Alternately, you can open File Explorer and browse to \\cccmain\Dept_Data\All Share\ODBC Drivers)
  2. Double-click on the msodbcsql file. Choose Run.
  3. At the Welcome screen, choose Next.
  4. Accept the terms and click Next again.
  5. Drop down the box for ODBC Driver for SQL Server SDK and choose Will be installed on local hard drive.
  6. Click Next and then Install to begin installation. Click Yes to allow installation, then Finish to close window.

Step 2: Find your DSN Connection name and default database name

  1. Open the Access Database you are trying to use. Close out of any error messages you receive.
  2. Click on the External Data tab, then click on Linked Table Manager.
  3. You will see a list of all connections that your tables are trying to connect to. In the below example, the DSN is EX6: Jenzabar EX, and the default database is named tmseprd. Make a note of all DSN and database names before moving on.  Spaces and capital letters are important!

NOTE: You may have more than one DSN and default database names listed. You will need to create a separate ODBC connection for each one.

Step 3: Add ODBC Connection(s)

  1. In the search bar, search for Administrative Tools, then click to open.
  2. Open ODBC Data Sources (32-bit)
  3. Click on the System DSN tab, then click Add.
  4. Scroll down the list and choose ODBC Driver 13 for SQL Server, then click Finish. (If you don’t see this, make sure you ran the update in the directions above. You may need to reboot)
  5. For Name, enter the DSN connection name found in your Access database. In our example, I would enter EX6: Jenzabar EX.
  6. Description can be left blank.
  7. For Server, TYPE cccsql. (using the dropdown takes a long time for windows to search).  Click Next.
  8. Leave With Integrated Windows authentication checked, and click Next.
  9. Check the box for Change the default database to:. Using the dropdown, choose your default database name that was listed in your Access database. Leave the rest as is, and click Next.
  10. Leave the defaults as is, and click Finish.
  11. Click Test Data Source to make sure your information was correct, then click OK on all windows to close.
  12. Re-open your Access database and try to open your linked tables and queries.

Details

Article ID: 84620
Created
Fri 8/9/19 12:56 PM
Modified
Sun 11/24/19 6:10 PM

Files (1)

msi

msodbcsql.msi

8/9/2019 1:42:36 PM