Re: Connecting to Oracle from MS Access 97

I think Mike's comments are useful. One thing you might do is make sure the table and column names in Oracle are all upper case and not in quotes. No matter which driver you use you will need to install SQLNet. (all the drivers use SQLNet underneath). If you can use stored procedures on the backend to do the data access, insert, update, delete etc. and thus put the data optimization where the server can do something with it.

I like oo4o. Oracle's Ole Objects. Nice syntax, very fast, you can use some optimizations to use bind variables (host variables).

I just met with a fellow DBA that was trying to figure out why their Rationale's Clear Quest (defect tracking tool) would not scale. (went kaput after 5 users and as few as 2 users could get in a deadlock situation) Turns out they originally built this in Access and "ported" it to other backends. In doing so they issue table lock statements all over the place. (eg add a row to the history table, 1st lock the entire history table, insert row, do a whole lot of unrelated stuff , and then unlock the history table) In a 300,000 line debug file 150,000 lines were lock and unlock table commands. This is an example of a group writing something incorrectly. So while Access is not at fault, one can really do some bone head things because it is "easy". Sometimes making things a little hard keeps the less experienced from shooting others in the foot.

Jim

"Roy Zolnoski" <ulrich_at_ulrichandhelvas.com> wrote in message news:a419c803.0210171001.704f6e59_at_posting.google.com...
> Thanks, very helpful and informative.
>
> We are going to use the Oracle Migration Workbench to create scripts
> to move the schema to Oracle. My understanding is that the OMW will
> create tables in Access which provide a "transparent" link between the
> Access GUI and the Oracle tables.
>
> Did you use this approach to translate the schema from Access to
> Oracle? Any feedback?
>
> Thanks again.
>
> bdtmike_at_sbcglobal.net (Mike Dwyer) wrote in message
news:<3dacce31.12676988_at_news.sf.sbcglobal.net>...
> > 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.
Received on Thu Oct 17 2002 - 19:34:51 CDT