Converting ODBC Links In MS Access Applications From “PROD” To “BREP”

BREP is a separate Banner database that replaces the PROD instance for the purpose of running ODBC-based queries.  BREP is copied from PROD every night, and so the contents may be up to 24 hours out-of-date, but otherwise the data layout and the table names are an exact copy of the production system.  This document explains how to re-direct the ODBC links in an existing MS Access application to the Banner BREP database.

However, before converting your Access application(s), you should take note of the following:

a)      Take a back-up of each Access application file (i.e. the “.accdb” file) before making the following changes (just in case).  A quick and easy way to do this is simply to right-click on the file in Windows Explorer and select “Send to …” then “Compressed (zipped) folder”.  (You might also want to rename the zip-file to clarify that it is the “old” PROD version).  You can then easily go back to this zipped version if the conversion fails, or if you need to revert back to the “PROD” version in future.

b)      If your Access application displays a menu or if it runs a process automatically when you start it, you may need to avoid this by holding down the “Shift” key while clicking on the shortcut.  This will prevent any automatic features from running so that the navigation window on the left becomes “visible”.  (Also note that Access may be minimised when started in this way, so it might look like it has not opened – so do remember to check the taskbar!).

c)      If you add any extra Banner ODBC links after the conversion, do not forget to use the DSN for “BANNER_ODBC_BREP” instead of “BANNER_ODBC_PROD” for these.

d)      The following example is based on Microsoft Access 2013, but the same method applies in other versions of Access.

 

Convert your Microsoft Access application to use the new “BREP” ODBC link by following these steps:

[1]  Open the Access database application that you wish to convert to use BREP and start the “Linked Table Manager”.  You can do this in 2 ways:

[1.1] Locate any linked table in the list of objects (i.e. “BREP” in the left-hand pane) and right-click on it, then select “Linked Table Manager” from the pop-up menu…

 BREP

OR

[1.2]  Alternatively, select the “External Data” ribbon (or the “Database Tools” ribbon if using Access 2007), and then click on “Linked Table Manager”:

BREP

[2]  This will open a dialogue box with a list of all linked tables and their current “parent” DSNs (Data Source Names) shown next to them in brackets.  In this example, all tables are currently connecting to Banner via “BANNER_ODBC_PROD” DSN:

BREP

[3.1]  Tick each table that you wish to re-direct to the BREP database.  This will typically be all of them, in which case just click “Select All”...

[3.1]  You will also need to tick the box at the bottom labelled “Always prompt for new location”:  

BREP

Then click on “OK”…

[4]  Select the “Machine Data Source” tab in the window that appears:

BREP

Then select “BANNER_ODBC_BREP” and click “OK”…

NOTE-1:  If “BANNER_ODBC_BREP” does not appear in this list, you will need to contact the IT Service Desk and ask for the BREP link to be installed.

NOTE-2:  If “BANNER_ODBC_PROD” does not appear in this list either, then the Banner ODBC driver is not installed at all on your PC, and so you should let the IT Service Desk know about this too! 

[5]  Enter your Banner username and password in the Driver Connect dialogue (leaving the Service Name as it is), and the click “OK”:

BREP

[6]  There will be a delay while the table-links are converted to the new DSN, and you should then see a message saying that “all selected linked tables were successfully refreshed”. The Linked Table Manager should also reappear with the list of tables updated to show them now attached to “BANNER_ODBC_BREP”:

BREP

Click “OK” …

[7]  … and then “Close” to complete the process.  Your database links should now work as before, but taking the data from the BREP Banner database rather than the PRODuction system.


Version 1.2
Paul M Baxter (SIMS Team)
21-Aug-2015