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

First, confirm that the ODBC connection is setup.

In the Search box, type ODBC
Click on ODBC Data Sources
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):

Old Drivers (need to be updated):

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.

Open the file.
Click on Data -> Queries & Connections
Click on Connections
Click on Query from beryl and then right click on it. Click on Properties.
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)
Click OK
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.

Open the MS Access database file
Right-click on one of the linked tables (ex. dbo_Engagement) -> Linked Table Manager
Click the box and click Edit
In the Connection string: change DRIVER=SQL Server to DRIVER=ODBC Driver 17 for SQL Server
Click Save
Click Relink
Click OK
Click OK
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:

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

Open the MS Access database file
Right-click on one of the linked tables (ex. dbo_Student_Dim -> Linked Table Manager
If there are no tables on the left, press F11 to bring up the tables
Click the box next to SQL and click Relink (it may say ODBC here instead of SQL)
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;
Click OK and continue OK until all tables are re-linked.
Click Yes if any other prompts come up.
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.
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.