Re: How does Access 97 rate as a front end?

In general, when it comes to ODBC, caveat emptor. As of 11/98 (I haven't checked [Quoted] 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).

[Quoted] [Quoted] 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 CET