Skip to main content
Indiana Wesleyan University Support Knowledge Base

ODBC Connections

  1. Last updated
  2. Save as PDF

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

First, confirm that the ODBC connection is setup.

  1. In the Search box, type ODBC
  2. Click on ODBC Data Sources
    clipboard_e8861605d629ee61ca715a24e24cf5a98.png
  3. Verify that the connections in System DSN show the correct drivers (see below). If beryl reports is not installed OR the old drivers are showing, the new drivers can be installed via the application catalog (listed under Colleague Reports with Driver). 

New drivers (set up correctly):

clipboard_eb8105ce285aea505e0ff2ab9953ebd92.png

Old Drivers (need to be updated):

clipboard_e2afe3b43d62e6c81ec5b212d2b600c2f.png

ODBC Data Source on Computer

How to add the warehouse ODBC connection manually.

  1. In the Search box, type ODBC
  2. Right click on ODBC Data Sources (64 bit) and click "Run as administrator"
  3. Click on System DSN
    clipboard_edc8df21e1cdf890c5a4d87755fa53c25.png
  4. Sometimes, this looks like it has been set up (especially when installed from the IWU Application Catalog) but does not work. It shows the above screenshot but when you click on beryl.reports 64-bit, it says it does not exist.  PLEASE CONTINUE with these instructions when this is the case. DO NOT ASSUME when you see the ODBC drivers set up that they are indeed set up.
  5. To manually install this, do the following:
    1. Click Add
    2. Click ODBC Driver 17 for SQL Server
    3. Click Finish
    4. Type in Name: beryl.reports
    5. Description: Warehouse
    6. Server: warehousedb
    7. Click Next
      clipboard_e0f19433e2597ae3fbb170271fd3d6787.png
    8. Click Next
    9. Click the box next to Change the default database to: and add reports
      clipboard_e88813f30d4ec2866085dc5f99fe17003.png
    10. Click Next
    11. Click Finish
    12. Click Test Data Source...
      clipboard_e69ba91e39bedf4ded749bb60b10a6422.png
    13. Verify that it worked and ask customer to re-try report.
      clipboard_ed7271db979b709eb5c83b7038130c49f.png
    14. If the test fails or the report still has an error, please make sure that this ODBC 64 connection was deleted and re-created using the above instructions BEFORE sending on to the Software Development team to troubleshoot. As noted above, sometimes this connection is created "empty," and it looks like the ODBC connection exists, but it needs to be deleted and re-create. In my experience,  when the the Application Catalog is used to create this automatincally, it creates the "empty" shell connection and has to be deleted and re-created manually.

MSQueries

Some Excel documents use an embedded connection instead of the computer ODBC connection. If the correct driver is installed and the report is still not working, the embedded connection string likely needs to be updated per the following instructions. 

  1.  Open the file.
  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: Check to see which database. If it says beryl.reports or reports, then replace the connection string with this:

    Description=WarehouseDB;DRIVER=ODBC Driver 17 for SQL Server;SERVER=WAREHOUSEDB;Trusted_Connection=Yes;APP=Microsoft Office 2016;DATABASE=reports;

    If the database in the connection string says, DM-ENGAGEMENT, use:

    Description=IWU Data;DRIVER=ODBC Driver 17 for SQL Server;SERVER=IWUDATA;Trusted_Connection=Yes;APP=Microsoft Office 2016;DATABASE=DM-ENGAGEMENT;  (see screenshot below) 

    clipboard_ef5d6ae78f8d45db1d2894a5757f7e7a4.png

  6. Click OK
  7. Save Spreadsheet. Have the customer test and confirm the query is working.
Note: If updating the Connection string does not work, take a screenshot of the current string and attach it to the ticket. If there is an error message when trying to run the report, screenshot the error and attach that to the ticket as well. Route the ticket to the Software Development Unassigned queue.

MS Access and DSN files for IWU Data

A similar issue may occur with Access databases. To update the connection string in the Access database, do the following.

  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_e27717efd596da926d5bd6ec58aede434.png
  4. In the Connection string: change DRIVER=SQL Server to DRIVER=ODBC Driver 17 for SQL Server
    clipboard_e4f1adc34d820251897c73ff6c45a77e4.png
  5. Click Save
  6. Click Relink
  7. Click OK
  8. Click OK
  9. Close Close (it autosaves the connection)
Note: If updating the Connection string does not work, take a screenshot of the current string and attach it to the ticket. If there is an error message when trying to run the report, screenshot the error and attach that to the ticket as well. Route the ticket to Software Development Unassigned queue.

MS Access for Data Warehouse 

A similar issue may occur with Access databases. The error they receive might look like this: 

clipboard_e92c296703dbff2d67075a3523295e624.png

To update the connection string in the Access database, do the following.

  1. Open the MS Access database file
  2. Right-click on one of the linked tables (ex. dbo_Student_Dim -> Linked Table Manager
    clipboard_e80c52955c274c5957750ca1566818c59.png
  3. If there are no tables on the left, press F11 to bring up the tables
  4. Click the box next to SQL and click Relink (it may say ODBC here instead of SQL)
    clipboard_ed6bbdefdf4b21d90b1d0cd60b1dbb6c2.png
  5. In the Connection string: deleting what is there and paste the following:
    Description=WarehouseDB;DRIVER=ODBC Driver 17 for SQL Server;SERVER=WAREHOUSEDB;Trusted_Connection=Yes;APP=Microsoft Office 2016;DATABASE=reports;
    clipboard_e43798e864c1a279dd78d8e41b6f26587.png
  6. Click OK and continue OK until all tables are re-linked.
  7. Click Yes if any other prompts come up.
  8. Sometimes it will prompt you to pick a field within a table. Ex. STUDENT_DIM.  If so, then find the field that matches the table (ex. STUDENT_DIM) and click on that field. Click OK when prompted.
  9. Click Close
Note: If updating the Connection string does not work, take a screenshot of the current string and attach it to the ticket. If there is an error message when trying to run the report, screenshot the error and attach that to the ticket as well. Route the ticket to Software Development Unassigned queue.

MS Access for Data Warehouse

 How to update SQL Pass through query ODBC connection string:

1. Open the MS Access Database File

2. Press F11 to open the All Access Objects tab

3. Under the Queries section you should see the items with global icons. Right-click on the first global icon. and select Deisgn View. 

clipboard_ece584bf359b0e1235f5a3987e7a4a1d1.png

4. A new properties tab should open on the right side of the Access Screen. On the row "ODBC Connect Str" delete the old string and replace it with: 

ODBC;DSN=beryl.reports;Description=berylrpts ODBC Driver 17 for SQL Server;Trusted_Connection=Yes;DATABASE=reports;

clipboard_e58fcee55598c215b4afd4f6760468e43.png

5. All Queries with a Globe icon next to them will need to have the same string updated. 

 

Note: If you still get an ODBC Connection error, it could be the user does not have access to a specific table in the query. Example, HR, Fin Aid, and Advancement tables are locked down in the warehouse. The Data Center grants the permissions, but contact the Software Development Team for specific group.
  1. Back to top
  • Was this article helpful?

Recommended articles

  1. Article type
    How-To
  2. Tags
    This page has no tags.