Skip to main content
Indiana Wesleyan University Support Knowledge Base

ODBC Connections

Goal

After completing this how-to you will have updated ODBC connections using ODBC Data Sources on the PC, using embedded link in MSQueries and using embedded link in MS Access.

ODBC Data Sources

  1. In the Search box, type ODBC
  2. Click on ODBC Data Sources
    clipboard_e8861605d629ee61ca715a24e24cf5a98.png
  3. Verify that the connections in BOTH User DSN  and System DSN do not say SQL Server for the driver.
    Old Drivers (need to be updated):
    clipboard_e2afe3b43d62e6c81ec5b212d2b600c2f.png
    New drivers (set up correctly):
    clipboard_eb8105ce285aea505e0ff2ab9953ebd92.png
  4. If there is one or more that need to be fixed, here are the steps to re-create it.
    1. Click on the name of the data source ex. beryl.reports
    2. Click Configure...
    3. Screenshot this page - you will need the information later . 
    4. Rename the connection (ex. beryl.reports_old)
      clipboard_eadc0803f69ace34bc96e16f6265839da.png
    5. Click Finish
    6. Click Add...
    7. Select ODBC Driver 17 for SQL Server (SQL Server Native Client 11.0 will also work if the other one is not available)
    8. Click Finish
    9. Type:
      1. Name: (ex. beryl.reports)
      2. Description: (ex. Warehouse reporting)
      3. Server (ex. warehousedb)
    10. Click Next - nothing needs to be changed on this screen
      clipboard_ed95d8a8fddeb29266d348985200db7cc.png
    11. Click Next - Check the first box - Change the default database to: and type in the database name (ex. reports)
      clipboard_e0e7840f44f469a1eaa6fabfb23668a4d.png
    12. Click Next - nothing needs to be changed on this screen
      clipboard_e1a7d7a4d45618e6dfe50638433457f6c.png
    13. Click Finish
    14. Click Test Data Source...
    15. You should see this box:
      clipboard_eafe588a9f0379f731c773f68eab65154.png
    16. Click OK
    17. Click OK
    18. Verify that you see the new ODBC connection in the list.

 

MSQueries

These will need to be updated when the ODBC connect from the computer is not used and the connection is embedded into MSQueries.

  1.  Open xlsx file with MSQuery
  2. Click on Data -> Queries & Connections
    clipboard_e86ea17c5e3587cc989c01ccf03f6768f.png
  3. Click on Connections
    clipboard_ed4c4f825db3647f0882282d2328d92ea.png
  4. Click on Query from beryl and then right click on it -> Click on Properties
  5. Click on Definition -> In Connection string: Replace the words SQL Serve with ODBC Driver 17 for SQL Server
    clipboard_ed035d9d23b9754c38ec609b56fb378b7.png
  6. Click OK
  7. Save Spreadsheet

MS Access and DSN files

  1. Open the MS Access database file
  2. Right-click on one of the linked tables (ex. dbo_Engagement) -> Linked Table Manager
    clipboard_ebbc8c6c59cd0b4394046a7a83078c2b2.png
  3. Click the box and click Edit
    clipboard_ef0219ae83fa43dad73cbd9fe1df4e688.png
  4. In the Connection string: change DRIVER=SQL Server to DRIVER=ODBC Driver 17 for SQL Server
    clipboard_e23e252df89a74cd80f53579e8f384792.png
  5. Click Save
  6. Click Relink
  7. Click OK
  8. Click OK
  9. Close Close (it autosaves the connection)

 

  • Was this article helpful?