How To Add A Banner ODBC User DSN (without needing PC administrator permissions):

The following instructions use “BANNER_ODBC_SUPD” as an example User DSN, but the same method will also apply to any ODBC connection as long as the connection string is correct, the correct ODBC driver is installed and the PC/VWD has the required firewall permissions:

 [1]  Open the “ODBC Data Source Administrator (32-Bit)” window: 
In order to ensure that you open the correct (i.e. 32-bit) version, you first need to hold down the Windows Key (usually between the Ctrl and Alt keys) and then press “R” to open the “Run” command window. 
And then copy and paste the following text into the “Open:” field:
%WINDIR%\SysWOW64\odbcad32.exe



… and then click OK.


[2]  This will open the ODBC Administrator window at the “User DSN” tab:



The “System DSN” tab is where the University’s “standard” machine DSNs normally reside (including PROD & BREP), which look might like this example:



You might want to double check in here first that the DSN you require does not already exist. 
Note that there is no DSN for “BANNER_ODBC_SUPD” in this example, which is why we’re going to add it as a User DSN.

Also, you will probably see the following warning message when you first open the System DSN tab:



Just click the “OK” button to dismiss it and continue.


[3]  Go back to the User DSN tab, and then click “Add…”:




[4]  Then in the “Create New Data Source” window, scroll down and select the driver called “Oracle in ODBC12”:



(Note:  If you can’t see “Oracle in ODBC12” in the list, then you do not have the recommended Oracle driver installed for Banner ODBC, and so you will need to create a Service Desk request to enquire about this).

Then click “Finish”.


[5]  Then, a blank “Driver Configuration” window will appear:



Complete the following 4 fields, using these values for SUPD & SUPW:

SUPD ODBC connection:

	Field name:	Field value:
[a]	Data Source Name	BANNER_ODBC_SUPD
[b]	Description	BANNER_ODBC_SUPD
[c]	TNS Service Name	supd-db.banner.leeds.ac.uk/SUPD:1521
[d]	Read-Only Connection	Ticked

SUPW ODBC connection:

	Field name:	Field value:
[a]	Data Source Name	BANNER_ODBC_SUPW
[b]	Description	BANNER_ODBC_SUPW
[c]	TNS Service Name	supw-db.banner.leeds.ac.uk/SUPW:1521
[d]	Read-Only Connection	Ticked

Note that fields [a], [b] & [c] will vary when entering a DSN for a different Banner instance, but the name and description should still follow the current naming convention to keep them compatible with Access and other applications that have already been set up with links using the same UoL naming standards. 
You might need to ask for the TNS “connection string” used in field [c] if you don’t already know what it is, especially as some Banner projects will use a temporary new Banner database.



Click “OK” to save the DSN.


[6]  The new entry will then appear in the list of User DSNs, and you should be able to use this in ODBC applications when creating new table links or when running an application which has already been set up with ODBC links (which is why the DSN names must match):



Extra note:-  If the temporary DSN is no longer required, it can simply be deleted by using the “Remove” button in the ODBC Administrator “User DSN” tab.

When adding a new ODBC table link in MS Access and it comes to choosing which DSN to use, the new User DSN will appear in the list under the (default) “Machine Data Source” tab along with the other DSNs:



_____________________________________________________________________

Oracle SQL-Developer Connection Settings for SUPD & SUPW:

When adding a new SQL-Developer connection for the Banner SUPD database (by selecting the green cross), use the following settings in the default window that appears:



Name = SUPD
Hostname = supd-db.banner.leeds.ac.uk
Port = 1521 (default)
Service name (select this option) = SUPD




Name = SUPW
Hostname = supw-db.banner.leeds.ac.uk
Port = 1521 (default)
Service name (select this option) = SUPW




PMB – 04/07/2023