IN [ODBC with multiple tables

[Randy Harris]

I have been using "IN" to query tables in Oracle from MS Access very
successfully.

Select Field FROM MyTable IN [ODBC...etc

Works great if there is only one table involved. Anyone know how I can use
the same technique with multiple tables (and be certain that the join occurs
at the server).

--
Randy Harris
(tech at promail dot com)

[MGFoster]

Randy Harris wrote:

I have been using "IN" to query tables in Oracle from MS Access very
successfully.

Select Field FROM MyTable IN [ODBC...etc

Works great if there is only one table involved. Anyone know how I can use
the same technique with multiple tables (and be certain that the join occurs
at the server).

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Microsoft recommends that instead of the IN <table> clause you link any
tables you want to the Access db file. Then run queries against the
linked tables.

You can also run SQL Pass Through (SPT) queries, then all the work will
be done on the Oracle server & only the results passed back to Access.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQivDrYechKqOuFEgEQLU/QCdF9eXJrBbteh47GQhGuB0k9nrDyIAoNWm
nsoYNJ/0ib2yo6xfCmKdxjVV
=gsnp
-----END PGP SIGNATURE-----

[Randy Harris]

"MGFoster" <[e-mail]> wrote in message
news:Cs*****************@newsread1.news.pas.earthl ink.net...

Randy Harris wrote:

I have been using "IN" to query tables in Oracle from MS Access very
successfully.

Select Field FROM MyTable IN [ODBC...etc

Works great if there is only one table involved. Anyone know how I can use the same technique with multiple tables (and be certain that the join occurs at the server).

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Microsoft recommends that instead of the IN <table> clause you link any
tables you want to the Access db file. Then run queries against the
linked tables.

Would you happen to know why Microsoft recommends that (other than joins),
or where I can read more about that recommendation?. I'm using IN <table>
in quite a few places, wondering what problems I've caused myself.

You can also run SQL Pass Through (SPT) queries, then all the work will
be done on the Oracle server & only the results passed back to Access.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQivDrYechKqOuFEgEQLU/QCdF9eXJrBbteh47GQhGuB0k9nrDyIAoNWm
nsoYNJ/0ib2yo6xfCmKdxjVV
=gsnp
-----END PGP SIGNATURE-----

[Tom van Stiphout]

On Mon, 07 Mar 2005 03:33:35 GMT, "Randy Harris" <ra***@SpamFree.com>
wrote:

Access 2000 help file / Index / IN_clause / IN Clause (Microsoft Jet
SQL):
For improved performance and ease of use, use a linked table instead
of IN.

You can probably imagine that the IN clause is very much JET-specific,
and as such perhaps less desirable.

Personally, I would build a view in the server, and link to it from
Access.

-Tom.

"MGFoster" <[e-mail]> wrote in message
news:Cs*****************@newsread1.news.pas.earth link.net...

Randy Harris wrote:

> I have been using "IN" to query tables in Oracle from MS Access very
> successfully.
>
> Select Field FROM MyTable IN [ODBC...etc
>
> Works great if there is only one table involved. Anyone know how I canuse > the same technique with multiple tables (and be certain that the joinoccurs > at the server).
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Microsoft recommends that instead of the IN <table> clause you link any
tables you want to the Access db file. Then run queries against the
linked tables.

Would you happen to know why Microsoft recommends that (other than joins),
or where I can read more about that recommendation?. I'm using IN <table>
in quite a few places, wondering what problems I've caused myself.

You can also run SQL Pass Through (SPT) queries, then all the work will
be done on the Oracle server & only the results passed back to Access.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQivDrYechKqOuFEgEQLU/QCdF9eXJrBbteh47GQhGuB0k9nrDyIAoNWm
nsoYNJ/0ib2yo6xfCmKdxjVV
=gsnp
-----END PGP SIGNATURE-----

[Darryl Kerkeslager]

>>> Randy Harris wrote:

I have been using "IN" to query tables in Oracle from MS Access very
successfully.
Select Field FROM MyTable IN [ODBC...etc
Works great if there is only one table involved. Anyone know how I can
use the same technique with multiple tables (and be certain that the
join
occurs at the server).

"MGFoster" wrote

Microsoft recommends that instead of the IN <table> clause you link any
tables you want to the Access db file. Then run queries against the
linked tables.

Randy Harris wrote:

Would you happen to know why Microsoft recommends that (other than joins),
or where I can read more about that recommendation?. I'm using IN <table>
in quite a few places, wondering what problems I've caused myself.

"Tom van Stiphout" wrote Access 2000 help file / Index / IN_clause / IN Clause (Microsoft Jet
SQL):
For improved performance and ease of use, use a linked table instead
of IN.
You can probably imagine that the IN clause is very much JET-specific,
and as such perhaps less desirable.
Personally, I would build a view in the server, and link to it from
Access.

I know that the OP was asking about an Oracle BE, but I will just say that
for a pure Access FE/BE solution ('Jet specific'), I have been using the IN
clause to do all my connections, and I can't say that I have had any
problems. When I need to connect to more than one table, I simply connect
to a query in the BE.

Perhaps the 'Ease of Use' part is correct, but I just can't see that there
is going to be 'Increased Performance'.

--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927

[Randy Harris]

"Darryl Kerkeslager" <ke*********@comcast.net> wrote in message
news:fr********************@comcast.com...

Randy Harris wrote:
I have been using "IN" to query tables in Oracle from MS Access very
successfully.
Select Field FROM MyTable IN [ODBC...etc
Works great if there is only one table involved. Anyone know how I can use the same technique with multiple tables (and be certain that the
join
occurs at the server)."MGFoster" wrote
Microsoft recommends that instead of the IN <table> clause you link any tables you want to the Access db file. Then run queries against the
linked tables. Randy Harris wrote:
Would you happen to know why Microsoft recommends that (other than joins),or where I can read more about that recommendation?. I'm using IN <table>in quite a few places, wondering what problems I've caused myself.

"Tom van Stiphout" wrote

Access 2000 help file / Index / IN_clause / IN Clause (Microsoft Jet
SQL):
For improved performance and ease of use, use a linked table instead
of IN.
You can probably imagine that the IN clause is very much JET-specific,
and as such perhaps less desirable.
Personally, I would build a view in the server, and link to it from
Access.

I know that the OP was asking about an Oracle BE, but I will just say that
for a pure Access FE/BE solution ('Jet specific'), I have been using the

IN clause to do all my connections, and I can't say that I have had any
problems. When I need to connect to more than one table, I simply connect
to a query in the BE.

Perhaps the 'Ease of Use' part is correct, but I just can't see that there
is going to be 'Increased Performance'.

--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927

I would sure like to know more about performance issues. This application
maintains 30 to 40 tables in Oracle. I've chosen not to use any linked
tables. There are nearly a hundred users - the proliferation of links had
me worried.

Tom's suggestion of using views in the server, is what I have been doing
until now. Using a view is roughly equivalent to using a query in an Access
BE. The piece I'm developing now, however, is building queries on the fly.
(ad-hoc). I had hoped to avoid repeatedly creating a custom view on the
server. I might have no choice.

--
Randy Harris
(tech at promail dot com)

[Tim Marshall]

Randy Harris wrote:

The piece I'm developing now, however, is building queries on the fly.
(ad-hoc). I had hoped to avoid repeatedly creating a custom view on the
server. I might have no choice.

I do this constantly with Oracle and the theta join method of
constructing joins (ie, using the where clause: "WHERE TBL1_PK =
TBL2_TBL1_FK" instead of inner/outer join stuff which I find more
involved to write in code) makes writing pass through queries a snap. I
use it to construct row sources for list and combo boxes and for
creating lists of Oracle records.

I've never heard of the IN clause before, actually, other than as an
alternative operator for "or".

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Randy Harris]

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:d0**********@coranto.ucs.mun.ca...

Randy Harris wrote:

The piece I'm developing now, however, is building queries on the fly.
(ad-hoc). I had hoped to avoid repeatedly creating a custom view on the
server. I might have no choice.

I do this constantly with Oracle and the theta join method of
constructing joins (ie, using the where clause: "WHERE TBL1_PK =
TBL2_TBL1_FK" instead of inner/outer join stuff which I find more
involved to write in code) makes writing pass through queries a snap. I
use it to construct row sources for list and combo boxes and for
creating lists of Oracle records.

I've never heard of the IN clause before, actually, other than as an
alternative operator for "or".

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Tim, are you using queries in linked tables for the row sources for the list
and combo boxes?

--
Randy Harris
(tech at promail dot com)

[Tim Marshall]

Randy Harris wrote:

Tim, are you using queries in linked tables for the row sources for the list
and combo boxes?

Nope, I use pass through queries saved as querydefs, ie, saved in the
query tab.

Here's my standard DAO code for constructing such a query. It's assumed
the Oracle SQL string has been constructed. There is reference to a
connnect string, cTmarconnect, which is something I always set up once
and once only in a module so that I don't have to worry about it should
any parameters of the connnect string change.

Public Const cTmarConnect =
"ODBC;DSN=TMA;UID=<Oracle_user_id>;PWD=<password>; DBQ=<oracle_database_alias>;DBA=W;APA=T;PFC=1;TLO= 0;DATABASE="

The following doesn't have any error handling:

Sub sCreatePT(strSql As String, strQryName As String)

'Takes an SQL string (strSql) and a query name (strQryName) and either
creates a new PT query or, if
'it exists already, changes the SQL string

Dim qdf1 As DAO.QueryDef
Dim dbs As DAO.Database

Dim booFound As Boolean

Set dbs = Access.CurrentDb

booFound = False

For Each qdf1 In dbs.QueryDefs

If qdf1.Name = strQryName Then

booFound = True

Exit For

End If

Next

If booFound = False Then 'querydef does not exist, so create it

Set qdf1 = dbs.CreateQueryDef(strQryName)

Else 'already exists

Set qdf1 = dbs.QueryDefs(strQryName)

End If

'Now assign characteristics

With qdf1

.Connect = cTmarConnect

.SQL = strSql

.ReturnsRecords = True

End With

Exit_Proc:

qdf1.Close
Set qdf1 = Nothing

dbs.Close
Set dbs = Nothing

Exit Sub

Err_Proc:

End Sub

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Randy Harris]

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:d0**********@coranto.ucs.mun.ca...

Randy Harris wrote:

Tim, are you using queries in linked tables for the row sources for the list and combo boxes?
Nope, I use pass through queries saved as querydefs, ie, saved in the
query tab.

Here's my standard DAO code for constructing such a query. It's assumed
the Oracle SQL string has been constructed. There is reference to a
connnect string, cTmarconnect, which is something I always set up once
and once only in a module so that I don't have to worry about it should
any parameters of the connnect string change.

Public Const cTmarConnect =

"ODBC;DSN=TMA;UID=<Oracle_user_id>;PWD=<password>; DBQ=<oracle_database_alias;DBA=W;APA=T;PFC=1;TLO=0;DATABASE="

The following doesn't have any error handling:

Sub sCreatePT(strSql As String, strQryName As String)

'Takes an SQL string (strSql) and a query name (strQryName) and either
creates a new PT query or, if
'it exists already, changes the SQL string

Dim qdf1 As DAO.QueryDef
Dim dbs As DAO.Database

Dim booFound As Boolean

Set dbs = Access.CurrentDb

booFound = False

For Each qdf1 In dbs.QueryDefs

If qdf1.Name = strQryName Then

booFound = True

Exit For

End If

Next

If booFound = False Then 'querydef does not exist, so create it

Set qdf1 = dbs.CreateQueryDef(strQryName)

Else 'already exists

Set qdf1 = dbs.QueryDefs(strQryName)

End If

'Now assign characteristics

With qdf1

.Connect = cTmarConnect

.SQL = strSql

.ReturnsRecords = True

End With

Exit_Proc:

qdf1.Close
Set qdf1 = Nothing

dbs.Close
Set dbs = Nothing

Exit Sub

Err_Proc:

End Sub

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Does this require a DSN, or can it be "DSN'less"? Also, Can you give me a
simple example of the SQL for a PT query? (something that shows how the
Oracle connection works)

--
Randy Harris
(tech at promail dot com)

[Tim Marshall]

Randy Harris wrote:

Does this require a DSN, or can it be "DSN'less"? Also, Can you give me a
simple example of the SQL for a PT query? (something that shows how the
Oracle connection works)

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

[Randy Harris]

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" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:d0**********@coranto.ucs.mun.ca...

Randy Harris wrote:

Does this require a DSN, or can it be "DSN'less"? Also, Can you give me a simple example of the SQL for a PT query? (something that shows how the
Oracle connection works)

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