In general, when it comes to ODBC, caveat emptor. As of 11/98 (I haven't checked
since then), Oracle's ODBC drivers for version 8.X were UNABLE TO HANDLE OUTER
JOINS and there were serious acknowledged bugs in the ODBC drivers for 7.4.x.
Those warnings aside, there are a number of things you can do with MS-Access and
Oracle that do get some work done, though they are really quick-and-dirty, largely
because of how Access was designed to work with server-side data. You can:
- "link" to the Oracle tables (i.e. as remote tables) via ODBC and then use the
query builder in Access to "paint" ad hoc queries and build reports. Using the
standard data-retrieval mechanisms provided by Access, this approach can consume
large amounts of network bandwidth when tables are large, because Access does a lot
of work on the client (Access does client-side processing in order to provide
support for "heterogeneous" joins, i.e. joins against tables from multiple back-end
data sources). But you can use the "pass-through" query option to force the
back-end engine to do the work (though I've encountered rare situations in Access
2.0 and Access 95 where the query, as painted, was not translated correctly to
native Oracle SQL syntax). And of course there is no pass-through translation of
Access intrinsic functions (e.g. uppercase conversion or substring manipulations)
to their Oracle counterparts (when they exist). However, you can type in the Oracle
SQL statement directly into the query edit window, using server side functions, and
then execute it as passthrough query on the server.
- and you can develop forms in Access against the "linked" Oracle tables, but
again, this is inefficient and you do not get much control over navigation. So you
could build "unbound" Access forms and use Oracle's Objects for OLE (OO4O) instead
of ODBC, but then you might as well use VB. VB lacks a sophisticated visual
run-time query designer, one that displays tables and join conditions visually, as
Access does, and that would give the developer the ability to intercept the query
and make sure it was not a cartesian product on a few large tables (even Access
doesn't do this).
So, if your application is for "casual" ad hoc use with a smallish database (~
50mb) then Access might well suffice. But if you have a commercial-quality
client-server application in mind, then Access won't be suitable.
Tim Romano
Received on Mon Dec 28 1998 - 00:00:00 CST
