Re: Connecting to Oracle from MS Access 97

Thanks, I'll watch for the Oracle names.

Future phases of this project will remove Access entirely and, at that point, we'll rewrite a lot of the existing VBA code as PL/SQL stored procedures. We're hoping to avoid that in this phase just for time's sake but performance may force us to bite the bullet now.

Point well taken on the Access code. I'll be doing a lot of performance testing to uncover those types of issues.

Thanks again.

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<LGIr9.79216$Fz.242423_at_rwcrnsc51.ops.asp.att.net>...
> 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.
Received on Fri Oct 18 2002 - 17:00:29 CDT