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):

ODBC Data Source on Computer

How to add the warehouse ODBC connection manually.

In the Search box, type ODBC
Right click on ODBC Data Sources (64 bit) and click "Run as administrator"
Click on System DSN
Recently this has looked set up (especially when installed from the IWU Catalog) but has not worked.
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
8. Click Next
9. Click the box next to Change the default database to: and add reports
10. Click Next
11. Click Finish
12. Click Test Data Source...
13. Verify that it worked and ask customer to re-try report.

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.

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.

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;

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