MS Access Login to Oracle

    by Bruce Knox  bknox @t uaex.edu     Oracle Database Programmer/Analyst                                                                   date last updated 02/13/14

---

This page is a  link to a  MS Access database logon example for connecting via ODBC to Oracle.  The example was originally intended to serve as an ad hoc reporting tool for SCT's Banner.  It still serves this function but is now generalized for use with any Oracle database.  Click here to download the MS Access 2000 example database (284kb).  Click here to download the MS Access 97 example database (114kb).  See the required setup instructions for the MS Access 97 database example.  (Removed all Visual Basic References as of 3/28/01.) 

 

See http://betwinx.com/ms_access.htm for a method of quickly extending this logon example to include other tables and views with minimum effort.  

See http://betwinx.com/oracle8_odbc_install.htm for ODBC Setup Notes.

 

Notes: The logon form works by having the ODBC System DSN Name  the same as your Banner/Oracle logon Database ( Host_String).  This allows the MS Access logon Form to look like the Banner/Oracle logon form.

If you logon to Banner with servername_pprd, then name your PC's ODBC System DSN Name servername_pprd.

In the MS Access code this is the DSN and DBQ found in the logon form Event Procedure for the Command Button: cmdLogon.

qdfpassThrough.Connect = "ODBC;DSN=" & txtDatabase & ";DBQ=" & txtDatabase & ";UID=" & txtUser & ";PWD=" & txtPassword

Note that the logon form in this code example is set to allow changes without actually completing the logon with a valid user name and password.  This should enable you to get setup, before the system starts defending itself.

 

There is no Banner identifiable information included in this page or the example database.  In fact the example Logon can be used with any Oracle database.

 

Special Instructions for first use of the MS Access 97 version of logon:

 

While the logon database has already been converted from Access 2000 to Access 97 the following must be done after you attempt to open the database using Access 97:

 

Access 2000 database to Access 97

You can convert code that uses Data Access Objects (DAO) back to Microsoft Access 97, but you may receive a message that your computer is missing at least one of the Access 97 object libraries. Use this procedure to fix the missing references.

1. Convert the Microsoft Access 2000 database to Microsoft Access 97. When you receive a message that your computer is missing at least one of the Access 97 object libraries, click OK.

2. Open the converted Access database in Microsoft Access 97.

3. Open a module in the database.

4. On the Tools menu, click References.

5. In the Available References box, clear the check boxes next to any missing references, and set a reference to the Microsoft DAO 3.51 Object Library.

Want more ODBC connect string parameters see ODBC Driver Help?

Disclaimer

 

Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences.  The scripts and information are provided "as is" without warranty, implied or otherwise.  Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:)

 

http://betwinx.com is my personal site.  betwinx?  This site is largely oriented to Oracle Reporting Solutions in the context of generating bespoken reports from Ellucian's Banner product. 
(I maintain a number of special topic pages with links from this primary site.)