If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access "behind the scenes"
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. |
#2
|
|||
|
|||
-----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+XIechKqOuFEgEQKqrACfe2x6X1CQFvk6Mz6jjxY95s 1wPT8AoIqU Y55x3Owk6f2RYDEOEj6eQtfM =SAn0 -----END PGP SIGNATURE----- Tcs wrote: 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Accessing a databse thru a webpage (newbie question) | Chang | General Discussion | 1 | August 23rd, 2004 09:10 AM |
Data from Excel to Access | Database Design | 2 | August 20th, 2004 12:53 PM | |
Useless Access 2003 | tired, angry, sucidial and bored | General Discussion | 10 | July 21st, 2004 11:52 PM |
You do not have exclusive access... ERROR | Robin | General Discussion | 1 | July 6th, 2004 01:18 AM |
Data Access Page Problem | Doug Munich | General Discussion | 0 | June 5th, 2004 12:03 AM |