Re: Oracle, Access, SQL*Net and ODBC ?

comrmb_at_herts.ac.uk (Mike Bearne) wrote:
>Hello,
>
>I am looking in to the feasability of using an Access front end to an
>Oracle 7 database. I've been in touch with Oracle, Microsoft etc but no
>one seems to be able to give me a straight answer as to what is needed to
>link these two databases together. I've been told by various companies
>that I need ODBC drivers, or SQL*Net, or both (on either or both platforms)
>but nobody seems to wanbt to tell me exactly what the set up should be.

SQL*Net is the network "language" by which Oracle products talk to one another. That means you need the appropriate SQL*Net "piece" on both ends of the connection. On the server side, there will be a SQL*Net "listener" that listens for connection requests from clients (or other servers) then makes the connection and manages the traffic until the connection is broken. On the client side there will be some code that translates the client service requests into the SQL*Net protocol and communicates with the listener on the server.

ODBC is a standardized API (Application Programming Interface) that provides a mostly-target-independent set of services for accessing databases. Many applications/application building tools take advantage of ODBC as a way to talk to different databases. You need a "driver" for the kind of database you want to access -- Oracle in this case. MS Access comes with a number of drivers for different "remote" databases, including Oracle. Other Oracle drivers are available from the Internet, Compuserve, Oracle or other third parties. Some are free, some cost significant bucks but add features or have higher performance. You need to trade off for what you require for your specific applications.

ODBC and SQL*Net represent the most common (not necessarily "best") way MS Windows PC clients talk to Oracle databases. There are some commercial alternatives (expect a note from the sales droid at Visigenic if you haven't gotten one already) but they tend to be expensive. Only you can determine if they are worth the expense and "non-standard" pieces. The hottest product at the moment (IMHO) is Oracle Objects for OLE which still needs SQL*Net but is an alternative to ODBC. As far as I know, it doesn't work with MS Access, however.

• enter soapbox mode------ Having said all that, I'd question WHY you want to use MS Access as a front end development tool?

MS Access is a reasonably competent, mostly-standalone, full *database* product that happens to be able to communicate with other "remote" databases (including Oracle). As a full database product, there is a significant amount of overhead (performance and storage) associated with using MS Access. Among other issues is the "why in hell would they want to do THAT?" way in which queries against a remote database can frequently attempt to COPY the whole friggin' table from the remote database to the PC BEFORE trying to invoke the conditions of the WHERE clause. Not a pretty sight if your remote table contains several million rows :-( :-( :-(

A similar question applies to Paradox, Approach, SuperBase, etc., though each has its own advantages and problems.

For purely "front-end" purposes, many better tools exist, including (but not limited to) Visual Basic, Power Builder, SQL Windows, Delphi, Oracle CDE tools, Oracle Power Objects (soon) etc. etc. etc. Almost ANY of these would be a better choice for a true "front-end" application.

If you require a local database, though, just ignore the man on the soapbox.

-----end soapbox mode-------

Hope this helps. Remember, free advice is worth whay you pay for it :-)

--
Bill Meahan,  Senior Developer  |        wmeahan_at_ef0424.efhd.ford.com
Electrical & Fuel Handling Division, Ford Motor Company
Opions expressed herein are those of the author and in no way represent
any official statement or opinion of Ford Motor Company