Pass through query and variables

[cunger28]

I'm currently building a pass through query which ties to an IBM Iseries ODBC
driver. Is there any way to include an "in" statement from another non-pass
through table or query in the same access database? For the sake of example,
let's say I have a static table of user names (name: tbl_users). I have a
pass through query built that I would like to only return results based off
of the tbl_users content. What would be the syntax to accomplish this?

In theory, the logic would look like this but I'm not sure of the exact
syntax:

Select ODBC.userid
From ODBC
Where ODBC.userid in (tbl_users.userid)

Thanks in advance.

 

[Albert D. Kallal]

No, you can't include the local table, because the sql being executed on the
IBM box.

what you *can* do is "resolve" the values for the local table, and SEND that
query to the IBM box.

I would not suggest you do this unless there only about 15, perhaps 25
values in the local access table.

Assuming you already have a pass-through query, then you can generate the
sql on the fly,
and SEND that sql to the server.

so, in your case, you go:

Set rstNames = CurrentDb.OpenRecordset("select userID from tblUsers")

Do While rstNames.EOF = False
If strInList <> "" Then strInList = strInList & ","
strInList = strInList & rstNames(0)
rstNames.MoveNext
Loop
rstNames.Close

strSql = "select * IBMTable where userID in (" & strInList & ")"
Set qdfPass = CurrentDb.QueryDefs("PassThough")
qdfPass.SQL = strSql
Set rst = qdfPass.OpenRecordset

However, since you hint that the list of names is "static", then why not
upload a copy of this table to the IBM server and then you can just use your
pass-through query as it is, and not have to run that "loop" to place the
id's in the actual sql.

eg:

strSql = "select * IBMTable where userID in (select UserID from
tblUsers)"

As I said, I don't think I would use the above loop idea unless the list is
small, say 20-50 names max, else the sql string tends to get very long in a
hurry.

 

[cunger28]

Thanks for the response Albert. I unfortunately don't have the ability to
upload to the IBM server. It is a 3rd party off site server. My static info
is also above the threshold that you listed. One thing I can do is link to
the entire table and then build my query in access to incorporate that static
table info.