Need help with pass-thru and local queries

[Tcs]

I've been stumped on this for quite a while. I don't know if it's so simple
that I just can't see it, or it's really possible. (Obviously, I HOPE it IS
possible.)

I'm trying to get my queries to run from VB. My pass-thru query retrieves
data from our AS/400 that I use to build a local table (on my PC). My pass-thru
and local do in fact work together when I run them interactively. But I want,
no make that NEED, to run them from VB.

Here's the code that Access built for the local query:

INSERT INTO tblAcctsRecAging_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
90dayAmtBilled,
90dayUnpaid )

SELECT
qryAcctsRecAging_0040_90days_420_BE.UTCSID,
qryAcctsRecAging_0040_90days_420_BE.UTLCID,
qryAcctsRecAging_0040_90days_420_BE.UTRCLS,
qryAcctsRecAging_0040_90days_420_BE.UTSVC,
qryAcctsRecAging_0040_90days_420_BE.UTPEYY,
qryAcctsRecAging_0040_90days_420_BE.UTPEMM,
qryAcctsRecAging_0040_90days_420_BE.UTAGE,
qryAcctsRecAging_0040_90days_420_BE.UTTTYP,
qryAcctsRecAging_0040_90days_420_BE.UTTDSC,
qryAcctsRecAging_0040_90days_420_BE.UTTAMT,
qryAcctsRecAging_0040_90days_420_BE.UTUNPD

FROM tblAcctsRecAging_Details
RIGHT JOIN qryAcctsRecAging_0040_90days_420_BE
ON tblAcctsRecAging_Details.LocID =
qryAcctsRecAging_0040_90days_420_BE.UTLCID;

(Please don't tell me that my (BE) pass-thru query name is too long. While that
may be true, the fact of the matter is...it WORKS.)

Here's my pass-thru (qryAcctsRecAging_0040_90days_420_BE) query:

SELECT distinct
b.UTCSID,
b.UTLCID,
b.UTRCLS,
b.UTSVC,
b.UTPEYY,
b.UTPEMM,
b.UTAGE,
b.UTTTYP,
b.UTTDSC,
b.UTTAMT,
b.UTUNPD

FROM tblCXLIB.UT420AP as b

WHERE
((b.UTAGE='C') AND
(((b.UTPEMM)=8) AND ((b.UTPEYY)=4)) Or
(((b.UTPEMM)=7) AND ((b.UTPEYY)=4)))

ORDER BY
b.UTRCLS,
b.UTSVC,
b.UTPEYY,
b.UTPEMM,
b.UTTTYP,
b.UTTDSC;

I have modified the local query to:

INSERT INTO tblAcctsRecAging_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
90dayAmtBilled,
90dayUnpaid )

SELECT
UTCSID,
UTLCID,
UTRCLS,
UTSVC,
UTPEYY,
UTPEMM,
UTAGE,
UTTTYP,
UTTDSC,
UTTAMT,
UTUNPD

FROM tblAcctsRecAging_Details
RIGHT JOIN (<my pass-thru "BE" query>)
ON tblAcctsRecAging_Details.LocID = tblCXLIB_UT420AP.[UTLCID];

BUT...I'm getting an error #3131 message of "Syntax error in FROM clause". I
feel that I'm getting closer, but I still haven't gotten it quite "right". I've
tried varaiations of this FROM clause, but to no avail.

Can anyone help? Have any thoughts/suggestions?

Thanks in advance.

[Christopher Lewis]

Tcs wrote:

I've been stumped on this for quite a while. I don't know if it's so
simple
that I just can't see it, or it's really possible. (Obviously, I HOPE it
IS possible.)

I'm trying to get my queries to run from VB. My pass-thru query retrieves
data from our AS/400 that I use to build a local table (on my PC). My
pass-thru
and local do in fact work together when I run them interactively. But I
want, no make that NEED, to run them from VB.
<Snipped Queries>
BUT...I'm getting an error #3131 message of "Syntax error in FROM clause".
I
feel that I'm getting closer, but I still haven't gotten it quite "right".
I've tried varaiations of this FROM clause, but to no avail.

Can anyone help? Have any thoughts/suggestions?

Thanks in advance.

Okay, we got the queries, obviously they've been saved. (Right?) You didn't
show us the code in question that runs these queries, but it should be
just:
docmd.openquery "<Insert Local Query Name Here>"

If your saved Local Query runs, this will run.

Also, looking through this carefully, your modified local query probably
doesn't know about the table tblCXLIB_UT420AP. It only knows about your
<my pass-thru "BE" query>, as you put it. You should probaly change that
to qryAcctsRecAging_0040_90days_420_BE from your previous query. Double
check your right join on statement.
--
Christopher Lewis

[Tcs]

On Wed, 01 Sep 2004 00:33:56 -0500, Christopher Lewis <ch******@cox.net> wrote:

Tcs wrote:

I've been stumped on this for quite a while. I don't know if it's so
simple
that I just can't see it, or it's really possible. (Obviously, I HOPE it
IS possible.)

I'm trying to get my queries to run from VB. My pass-thru query retrieves
data from our AS/400 that I use to build a local table (on my PC). My
pass-thru
and local do in fact work together when I run them interactively. But I
want, no make that NEED, to run them from VB.
<Snipped Queries>

BUT...I'm getting an error #3131 message of "Syntax error in FROM clause".
I
feel that I'm getting closer, but I still haven't gotten it quite "right".
I've tried varaiations of this FROM clause, but to no avail.

Can anyone help? Have any thoughts/suggestions?

Thanks in advance.

Okay, we got the queries, obviously they've been saved. (Right?) You didn't
show us the code in question that runs these queries, but it should be
just:
docmd.openquery "<Insert Local Query Name Here>"

If I just wanted to run the saved queries, then yes, this is what I would do.
(And have done so elsewhere.) But I don't just want to run the saved queries.
The parameters are variable. So I build my SQL statements in VB. (I've looked
at them, VB has built what I have told it to build.) So in THIS case, I use the
DoCmd.RunSQL statement, using my built statements. THIS...is what I can't get
to work. My assumption being is that my syntax is incorrect somehow.
If your saved Local Query runs, this will run.

Also, looking through this carefully, your modified local query probably
doesn't know about the table tblCXLIB_UT420AP. It only knows about your
<my pass-thru "BE" query>, as you put it.
Actually, tblCXLIB_UT420AP is a linked table to the AS/400. Access hasn't had a
problem with it before.
You should probaly change that
to qryAcctsRecAging_0040_90days_420_BE from your previous query.
I've tried that. It doesn't work.
Double check your right join on statement.

It's what Access used. It sounds right, it seems right, I just don't know if I
got the syntax right.

MAYBE...MAYBE my problem is more fundamental...

Perhaps when using the DoCmd.RunSQL command, Access doesn't know that the
pass-thru query, is in fact, a pass-thru query. Maybe it's trying to run it
locally. Hmmm...if this is true, then how WOULD I run a pass-thru query using
this command?

[Rick Brandt]

"Tcs" <ts****@eastpointcity.org> wrote in message
news:e9********************************@4ax.com...

On Wed, 01 Sep 2004 00:33:56 -0500, Christopher Lewis <ch******@cox.net> wrote:
MAYBE...MAYBE my problem is more fundamental...

Perhaps when using the DoCmd.RunSQL command, Access doesn't know that the
pass-thru query, is in fact, a pass-thru query. Maybe it's trying to run it locally. Hmmm...if this is true, then how WOULD I run a pass-thru query using this command?

Bingo! DoCmd.RunSQL assumes a local query. I don't think it can be used
for a pass-thru. You could define a saved Query with the proper pass-thru
settings for Connect, etc., and then modify the SQL of that query using
your code and then execute the saved query. I do this all the time.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Tcs]

On Wed, 1 Sep 2004 13:10:10 -0500, "Rick Brandt" <ri*********@hotmail.com>
wrote:

"Tcs" <ts****@eastpointcity.org> wrote in message
news:e9********************************@4ax.com.. .

On Wed, 01 Sep 2004 00:33:56 -0500, Christopher Lewis <ch******@cox.net>

wrote:

MAYBE...MAYBE my problem is more fundamental...

Perhaps when using the DoCmd.RunSQL command, Access doesn't know that the
pass-thru query, is in fact, a pass-thru query. Maybe it's trying to run

it

locally. Hmmm...if this is true, then how WOULD I run a pass-thru query

using

this command?

Bingo! DoCmd.RunSQL assumes a local query. I don't think it can be used
for a pass-thru. You could define a saved Query with the proper pass-thru
settings for Connect, etc., and then modify the SQL of that query using
your code and then execute the saved query. I do this all the time.

Okay. You're saying I'd actually 'run' my saved query with the DoCmd.OpenQuery
command, which should work, since my queries work interactively. But I would
programatically CHANGE my saved query before doing so? How would I do that?
Could you provide an example?

Thanks!

[Rick Brandt]

"Tcs" <ts****@eastpointcity.org> wrote in message
news:51********************************@4ax.com...

On Wed, 1 Sep 2004 13:10:10 -0500, "Rick Brandt" <ri*********@hotmail.com> wrote: Okay. You're saying I'd actually 'run' my saved query with the DoCmd.OpenQuery command, which should work, since my queries work interactively. But I would programatically CHANGE my saved query before doing so? How would I do that? Could you provide an example?

CurrentDB.QueryDefs("QueryName").SQL = strNewSQL
CurrentDB.Execute "QueryName", dbFailOnError

(I never use DoCmd.OpenQuery)
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Tcs]

On Wed, 1 Sep 2004 15:35:54 -0500, "Rick Brandt" <ri*********@hotmail.com>
wrote:

"Tcs" <ts****@eastpointcity.org> wrote in message
news:51********************************@4ax.com.. .

On Wed, 1 Sep 2004 13:10:10 -0500, "Rick Brandt"

<ri*********@hotmail.com>

wrote:

Okay. You're saying I'd actually 'run' my saved query with the

DoCmd.OpenQuery

command, which should work, since my queries work interactively. But I

would

programatically CHANGE my saved query before doing so? How would I do

that?

Could you provide an example?

CurrentDB.QueryDefs("QueryName").SQL = strNewSQL
CurrentDB.Execute "QueryName", dbFailOnError

(I never use DoCmd.OpenQuery)

Wow. I haven't had time to do everything that I need to do...yet, but I DID
modify the contents of my query, by using the command you said to use. WOW. It
works. And it's so...so...simple.

Now, another question, or two, if I may. Does the '.SQL' imply that it's a
pass-thru query? (What DOES the '.SQL' mean?) Would I use:

CurrentDB.CreateQueryDef("QueryName").SQL = strNewSQL

if my query didn't exist?

Thank you, thank you, thank you!

[Rick Brandt]

"Tcs" <ts****@eastpointcity.org> wrote in message
news:qf********************************@4ax.com...

Wow. I haven't had time to do everything that I need to do...yet, but I DID
modify the contents of my query, by using the command you said to use. WOW. It works. And it's so...so...simple.

Now, another question, or two, if I may. Does the '.SQL' imply that it's a
pass-thru query? (What DOES the '.SQL' mean?) Would I use:
The SQL propery simply contains the SQL statement for the query. The graphical
query design grid is merely a tool for letting Access create the SQL in the
background. You can go into SQL view for any query to look at the statement.
The command I gave you sinply allows you to change the SQL statement from code.

It is the Connect property that specifies that a query is a Pass-thru.
Non-Pass-Thrus will have no Connect property while Pass-Thrus will.

Would I use:
CurrentDB.CreateQueryDef("QueryName").SQL = strNewSQL
if my query didn't exist?

It's a bit more than that. CreateQueryDef would create an in-memory QueryDef
object and then you would need to append that to the database's QueryDefs
collection to make it permanent. If you look at the help file for
CreateQueryDef you will see examples of its usage.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Tcs]

On Wed, 1 Sep 2004 19:09:42 -0500, "Rick Brandt" <ri*********@hotmail.com>
wrote:

"Tcs" <ts****@eastpointcity.org> wrote in message
news:qf********************************@4ax.com.. .

Wow. I haven't had time to do everything that I need to do...yet, but I DID
modify the contents of my query, by using the command you said to use. WOW.

It

works. And it's so...so...simple.

Now, another question, or two, if I may. Does the '.SQL' imply that it's a
pass-thru query? (What DOES the '.SQL' mean?) Would I use:

The SQL propery simply contains the SQL statement for the query. The graphical
query design grid is merely a tool for letting Access create the SQL in the
background. You can go into SQL view for any query to look at the statement.
The command I gave you sinply allows you to change the SQL statement from code.

It is the Connect property that specifies that a query is a Pass-thru.
Non-Pass-Thrus will have no Connect property while Pass-Thrus will.

Would I use:
CurrentDB.CreateQueryDef("QueryName").SQL = strNewSQL
if my query didn't exist?

It's a bit more than that. CreateQueryDef would create an in-memory QueryDef
object and then you would need to append that to the database's QueryDefs
collection to make it permanent. If you look at the help file for
CreateQueryDef you will see examples of its usage.

Thank you....VERY much!