Access "behind the scenes"

[Tcs]

Is there any way to see what Access is REALLY doing, "behind the scenes" so to
speak? I need help with translating what Access is doing, so I get it to work
from VB.

I'm still 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. 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?

Thanks in advance.

 

[MGFoster]

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

In the pass-thru query you have "tblCXLIB.UT420AP" as the table name.
In SQL syntax that means the table UT420AP in the schema (object owner)
tblCXLIB. Is this a typo? If not you should change the table name so
it doesn't include the period.

The FROM clause of the last query has the JOIN ON clause using the name
of the table in the pass-thru query instead of the pass-thru query's
name. Change to something like this:

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

Also, in the last query the parentheses around the pass-thru query name
aren't needed. I don't believe this would cause any problems, but, just
in case, get rid of them.

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

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

iQA/AwUBQTT+XIechKqOuFEgEQKqrACfe2x6X1CQFvk6Mz6jjxY95s1wPT8AoIqU
Y55x3Owk6f2RYDEOEj6eQtfM
=SAn0
-----END PGP SIGNATURE-----

Is there any way to see what Access is REALLY doing, "behind the scenes" so to
speak? I need help with translating what Access is doing, so I get it to work
from VB.

I'm still 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. 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.