Working with Oracle (ADO Enabled)

Create a server side ODBC for the initial connection. Testing can be helpful in debugging user errors on the Oracle side before we continue.

Create a Access database on the server in a safe place to be used as a bridge. We are using Access 2000 in the example. We need to link to the Oracle tables using the ODBC you just created.

Enter in your password.

Select the tables you would like to work with. Check save password. Click OK.

You should now be able to browse the tables data from the Access linked connections.

Create a ColdFusion “Access Database” connection and apply. Make sure the test is successful. No user name or password is needed.

Login to RIT as a web administrator. Go to the ODBC menu and add the newly created CF data source to the site you will to manipulate data with. In this example the site is called ORATEST.

You can now proceed to making queries and reports. The drop down will show you a list of linked tables and queries. The ability to have queries show up as a table is a huge time saver. For example in Access two tables where joined and the query was saved as SalesQuery. It now shows up in list of available tables to choose from.

Once you choose you data you will get a list of available fields. From there you have full access to your live data using Access as middle man but the advanced usage of Access’s query builder should make it well worth it.