1. Does anyone have recommendations for books or web resources for
general rules/guidelines/help on doing this? I haven't found a good
"narrower" term for searching usenet or the web that gets me to a
In my opinion (and Rick Brandt may disagree with me on this), even if
you exclusively use ODBC linked tables (against which you current Access
querydefs will work with no or very little modification needed), you
should still have an understanding of how Oracle SQL works.
Thus, while not a book on migration to Oracle from Access/Jet, the book
that I've had great success with is _Oracle SQL and PL/SQL Handbook_ by
John Adolph Palinski. Should be available via Amazon. There are
doubtless other great books out there, but this one is what is on my
desk (staring up at me, battered and with pages falling out of the well
2. Speed is an issue. I've been thinking that running stored Access
queries on our distributed network will take longer than simply
calling defined Views in Oracle running on the dedicated Oracle
servers. Any advice here?
My personal preference is pass through queries using Oracle syntax
versus using linked tables and Jet SQL syntax. In a well designed
Oracle database, simple to moderately complex queries in Access against
Oracle tables work just as well and even fractionally faster than
straight Oracle SQL via pass through queries. The key, here, though is
"well designed" Oracle tables, ie, properly indexed and PK/FK and other
constraints defined. Really, to properly design Oracle tables, you use
the same care and consideration you would to establish good Jet table
However, sometimes, even in a well designed Oracle database, if you are
putting together a lot of tables in a Select statement, the ODBC
connection can start dropping whole columns of information and groaning
In my experience, Select clause sub queries (indeed sub queries of any
kind) suck golf balls through a narrow urethra in a linked tables set
up. Not sure what Rck Brandt's comments on this might be.
In the situation described in the previous two paragraphs, I found pass
through queries were the cat's meow. Plus, they allowed me to better
talk to my Oracle admins when I was having problems or wanted them to
look at see why a table might be behaving unexpectedly.
The big disadvantage of pass through queries is that they are read only.
Thus if you have a continuous form in which you add records on the
fly, you can't do it the way you would if you had a Jet or linked table
Access query as a recordsource for the form. This leads to more
development consideration (routines for adding records, routines for
updating records, routines for deleting records) that those of us who
also develop in Jet find longer than what we're used to.
Not related to questions, but if your organization is running Oracle,
you will have Oracle admins who will be of help in terms of setting up
table spaces, your schema and such.
3. I have lots of tables where I liberally used "Memo" fields. Some
of these, I'm realizing were just lazy, and I should have made text
columns of a certain size. However, a few, I need to be open ended
for people to type up reports/feedback/etc. Any guidance out there on
the proper equivalent in Oracle? CLOB's? Text with 4000 characters?
(Usage: The total number of these types of fields will be fairly
limited. I anticipate, at most, maybe 10,000 in 5 years at the most.
Many times, these will be blank or just have one or two sentences.
It's only wordy guys like me who'll use lots of space...)
I actually don't have experience with CLOBs, BLOBs or BFILE data types.
My major Oracle app is an older one and uses Longs (of which you may
only have one in any table or view - a "view" BTW, if you're not sure,
is actually the equivalent of a stored Access/Jet query. I do find the
varchar2 data type (which can be up to 4000 characters long) to be very
useful for most things.
I guess the thing to do is run a Jet totals query on numbers of current
record for the length of your larger field(s) and go from there.
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me