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)

Before you can add the ODBC Connection, you need to know whether or not you are running the 32-bit or 64-bit version of Microsoft Access.  Here is how you find out:

  1. In Microsoft Access, click on File.
  2. Next, click on Account.
  3. You will be brought to the screen that has Product Information on it.  Click on that the About Access button.
  4. At the very top it will tell you what version of Access you have and at the end will either say 32-bit, 64-bit, or may not have anything at the end.  If it says 32-bit or does not specify, it is 32-bit.  If it says 64-bit, it is 64-bit.

Now you are ready to add the ODBC Connection:

  1. In the search bar, search for Administrative Tools, then click to open.
  2. Open ODBC Data Sources (32-bit) or ODBC Data Source (64-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
Tue 4/20/21 2:47 PM