Re: Connecting to Oracle from MS Access 97

Roy,
I just went through a port of my Access 97 app from Jet to Client Server (both SQL Server 2000 & Oracle 9i), keeping the front end in Access. There you know, there are more ways to skin a cat. It depends on how much work you want to do, do you want to preserve your existing code, do you opt for more performance, etc.

Not knowing your situation, I'll just reflect on my experience with my app.

My app was pretty major as far as Access apps go. There were 150 tables, 180 forms, 60,000 lines of DAO vb code. The application was an appraisal system for county tax assessors so I have some decent sized tables (3M rows+).

We needed to go client server, we needed to support both MSSQL and Oracle backends. It does some serious analysis of sales trends so performance was very important. It had to support 150+ users.

After looking at all the options, I opted for the ODBC drivers approach. Time to deliver the product was important. There was so much DAO code to convert and the performance difference wasn't that different so ADO wasn't warrented. I used MS SQL server drivers for the SQL server backend and I used the Oracle ODBC drivers for the Oracle backend. I moved as much of the logic out of the app and into the database through stored procedures & triggers.

Working close to the data made any performance issues with ODBC moot. However, you really have to be careful how you structure your queries in forms and reports. ODBC drivers mangle your queries to make them work for the appropriate back end. You'd be amazed at how inefficient they can be if you're not careful!

Use a profiling tool (trace) to see what happens at the back end when you use your forms and reports.

Also, when you make tuning changes to your database (like adding/removing indexes), you need to refresh your linked tables so that they know about these changes. Otherwise, your ODBC driver will choose an optimization path that's less than optimal.

Be carefull when using queries that combine data from JET and Oracle at the same time. ODBC cannot optimize this query at the back end so it pulls everything over and sorts it out at the client end. This may result in pulling your entire table over the wire if you're not carefull.

Doing operations that move data from text files or access tables into Oracle can be tricky to make happen with good performance. This is because ODBC performs at least one INSERT statement per ROW. If you dump 50,000 rows from a jet table into oracle, that's a lot of commands hitting your server. SQL*Loader is a great tool for doing bulk inserts.

Everyone is quick to poo-poo Access for various reasons (some good, some not). I like the flexibility of it as well as the report writer so I was going to stay with it. I was very happy with the results. By doing all of the above, I was able to have an application that could switch on the fly and use either JET, SQL Server, or Oracle back ends. The application code in the MDB had very little back-end specific code, too.

I've just recently battled this war so let me know if I can be of any more help.

-Mike

On 15 Oct 2002 15:14:01 -0700, ulrich_at_ulrichandhelvas.com (Roy Zolnoski) wrote:

>We are migrating an existing MS Access 97 application to an Oracle
>back end, Access front end (future phases of the project will replace
>Access entirely). I am trying to determine the method of connecting
>to the database which will reach the best balance of performance vs.
>time spent rewriting the existing Access VBA code.
>
>It sounds like these are the three most likely alternatives. Do you
>have any recommendations or experience with similar applications?
>
>1) MS ODBC driver (as ships with Access)
>2) Oracle ODBC driver (requires Net8, we already have the Oracle 7
>driver with SQL*Net installed on our workstations - any conflicts with
>trying to use both versions?)
>3) Rewrite the VBA code to use direct ADO connections to the
>database.
>
>Existing architecture: MS Access 97 front end database with VBA code
>using DAO, linked tables to a series of back end MS Access 97
>databases. MS Excel 97 pivot tables which get data from queries in
>the Access databases.
>
>Proposed architecture (this phase): MS Access 97 GUI front end with
>VBA code, linked tables or direct ODBC connection to Oracle 8.1.7 back
>end database. MS Excel 97 pivot tables which get data from views in
>the Oracle database.
Received on Tue Oct 15 2002 - 23:07:12 CDT