Tim, The SQL itself wasn't actually the problem for me, but rather
constructing the pass thru query in code. I very much prefer the older SQL
syntax that Oracle uses, to the newer SQL that is used by Jet. I find the
"old stuff" every bit as capable and much easier to code.
The piece that was confusing me was the querydef. I have been working
exclusively with ADO of late, so it took me a while to digest the DAO
function you posted a bit earlier. I now understand how it works. I had
been trying to connect to Oracle in the SQL rather than the querydef itself.
I sure wish there were an equivalent in ADO.
Thanks very much for taking the time to clarify this. It was very helpful.
--
Randy Harris
(tech at promail dot com)
"Tim Marshall" <TIMMY!@PurplePandaChasers.Moertherium> wrote in message
news:d0ivgl$f1q$1@coranto.ucs.mun.ca...[color=blue]
> Randy Harris wrote:
>[color=green]
> > Does this require a DSN, or can it be "DSN'less"? Also, Can you give me[/color][/color]
a[color=blue][color=green]
> > simple example of the SQL for a PT query? (something that shows how the
> > Oracle connection works)[/color]
>
> Yes, it requires a DSN. Oracle ADO connections can be DSNless, however,
> ADO does not allow the creation of pass through querydefs, AFAIK. I
> stand to be corrected on this.
>
> SQL for Oracle versus Jet (which is what is being used when you use
> linked tables) is a little different. It's exactly the same thing you
> use on the SQL Plus window, which, if you haven't used Oracle SQL, is
> the very basic interface with an Oracle database.
>
> I hesitate to try and teach a newbie (if you are one!) the Oracle SQL
> dialect on line. I'll assume you're pretty familiar with the Jet
> dialect and functions available in Jet SQL.
>
> I'd highly recommend you check into an Oracle course, or a good book on
> Oracle SQL. But in the meantime, here is an example of a query in
> Access using linked tables.
>
> To set the background, there are two tables, F_DRAWINGS (with primary
> key DWG_PK) and F_AREAS (foreign key AR_DWG_FK). Let's say the name of
> the Oracle database is DMS (for an imaginary drawing management
> application). The linked tables will likely appear in the tables tab as
> default names DMA_F_DRAWINGS and DMA_F_AREAS (which you can rename, BTW,
> but I don't recommend it, it makes management of your linked table app a
> PITA).
>
> Let's assume F_AREAS has information on building name (AR_BLDG_NAME),
> and system - such as heating, cooling, electrical , etc systems name
> (AR_SYSTEM). Of course both of these entities could well be the subject
> of separate tables, but let's not worry about normalization at this
> moment. Let's also assume AR_SYSTEM is not always populated, while
> AR_BLDG_NAME is.
>
> Jet SQL might be (I'm breaking it up with line breaks):
>
> Select
> DMA_F_DRAWINGS.DWG_DRAWING_NO as [Dwg No],
> DMA_F_AREAS.AR_BLDG_NAME as [Building Name],
> nz(DMA_F_AREAS.AR_SYSTEM, "<No System>") as System
> from
> DMA_F_DRAWINGS INNER JOIN DMA_F_AREAS ON DMA_F_DRAWINGS.DWG_PK =
> DMA_F_AREAS.AR_DWG_FK
> where
> DMA_F_AREAS.AR_BLDG_NAME = "Avon Gumby Hall"
>
> I usually don't use the table name prefix if I have unique field names
> within tables, but the above is how the Access query builder would do
> it, I think.
>
> The equivalent of the above in Oracle SQL, which would go into a pass
> through query, is:
>
> Select
> F_DRAWINGS.DWG_DRAWING_NO "Dwg No",
> F_AREAS.AR_BLDG_NAME "Building Name",
> nvl(F_AREAS.AR_SYSTEM, '<No System>') "System"
> from
> F_DRAWINGS, F_AREAS
> where
> F_AREAS.AR_BLDG_NAME = 'Avon Gumby Hall' AND
> F_DRAWINGS.DWG_PK = F_AREAS.AR_DWG_FK
>
> Note how:
>
> 1) field alias are designated (with the "as" and square brckets if it
> has spaces in Jet, and with double quotes in Oracle);
>
> 2) the nz function in Jet is represented by the nvl function in Oracle
> (there are, of course myriad functions available in both dialects);
>
> 3) single quotes are used in Oracle versus double quotes in Jet to
> delimit text data;
>
> 4) the location and structure of how the join between the two tables is
> made. In Jet, it is part of the from clause (ANSI join) while in Oracle
> it is in the where clause (Theta join); and
>
> 5) the prefixed Oracle database name is not used in the Oracle SQL - it
> can be and may even be required depending on the Oracle user (remember
> the Orqacle user when you create a DSN?) in which case instead of an
> underscore, it's a dot.
>
> So the above Oracle SQL string is what I would pass to the function I
> presented earlier.
>
> BTW, one drawback of pass through queries is that you cannot add or edit
> records while you can do this with queries using linked tables. To
> add/edit records via the pass through method, you need to run SQL which
> are insert or update statements, ie,
>
> Access.CurrentDb.Execute strSql, dbFailOnError
>
> Some advice:
>
> A) If not familiar with Oracle, get a book.
>
> B) If you're not at all familiar with what I was writing in the DAO
> function I presented in my last post, make sure you know what the bleep
> you're doing before you start using VBA code.
>
> C) Read the help on pass through queries.
> --
> Tim
http://www.ucs.mun.ca/~tmarshal/
> ^o<
> /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
> /^^ "What's UP, Dittoooooo?" - Ditto[/color]
