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
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.
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.
  
  Warning:
  If you opened ODBC Data Source with an administrator account, when you hit "Test Data Source" it will always fail. Once you've followed the steps above and are ready to Click Test Data Source, reopen ODBC Data Source with your normal user, click through the prompts until you get to the Test Data Source option, and test if the connection is correct.
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.

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.

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.