My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I COULD be wrong... I've tried the
access group...twice...and all I get is "Access doesn't like ".", which I know,
or that my query names are too long, as there's a limit to the length of the SQL
statement(s). But this works when I don't try to do it from VB.) I THINK my
problem is just a syntax problem, seeing as this is my first experience with
running pass thru queries from VB code. So here goes...

I guess the first question to ask is - Can a pass-thru query be a nested query
as applies to VB code? (When I run my two queries manually, I get the results I
want...)

I have a local table into which I need to append data I retreive from our
AS/400. I initially developed both the local and pass thru queries which work
just fine together...with hardcoded parameters, and run manually (or even from
VB, just not "built" in VB).

The local query code (which Access created):

INSERT INTO tblAcctsRecAging_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
CurrentAmtBilled,
CurrentUnPaid )

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

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

And the pass thru query (which I created):

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

FROM
CXLIB.UT420AP

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

ORDER BY
CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY,
CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTTTYP,
CXLIB.UT420AP.UTTDSC;

The problem is that my parameters are variable. So I created this:

' compose SQL string 1 for the back end (BE) - DB2
'
strSQLselect1 = _
"SELECT distinct " & _
"CXLIB.UT420AP.UTCSID, " & _
"CXLIB.UT420AP.UTLCID, " & _
"CXLIB.UT420AP.UTRCLS, " & _
"CXLIB.UT420AP.UTSVC, " & _
"CXLIB.UT420AP.UTPEYY, " & _
"CXLIB.UT420AP.UTPEMM, " & _
"CXLIB.UT420AP.UTAGE, " & _
"CXLIB.UT420AP.UTTTYP, " & _
"CXLIB.UT420AP.UTTDSC, " & _
"CXLIB.UT420AP.UTTAMT, " & _
"CXLIB.UT420AP.UTUNPD "

strSQLfrom1 = _
"FROM CXLIB.UT420AP "

strSQLwhere1 = _
"WHERE " & _
"((CXLIB.UT420AP.UTAGE='" & strAgeGroup & "') AND " & _
"(((CXLIB.UT420AP.UTPEMM)=" & int1stMM & ") AND ((CXLIB.UT420AP.UTPEYY)="
& int1stYY & ")) Or " & _
"(((CXLIB.UT420AP.UTPEMM)=" & int2ndMM & ") AND ((CXLIB.UT420AP.UTPEYY)="
& int2ndYY & "))) "

strSQLorderby1 = _
"ORDER BY " & _
"CXLIB.UT420AP.UTRCLS, " & _
"CXLIB.UT420AP.UTSVC, " & _
"CXLIB.UT420AP.UTPEYY, " & _
"CXLIB.UT420AP.UTPEMM, " & _
"CXLIB.UT420AP.UTTTYP, " & _
"CXLIB.UT420AP.UTTDSC; "

strSQLstatement1 = _
strSQLselect1 & _
strSQLfrom1 & _
strSQLwhere1 & _
strSQLorderby1

' compose SQL string 2 for the front end (FE) - Access
'
strSQLinsert2 = _
"INSERT INTO tblAcctsRecAging_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear, " & _
"PeriodMonth, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
"CurrentAmtBilled, " & _
"CurrentUnPaid ) "

strSQLselect2 = _
"SELECT (" & _
strSQLstatement1 & ".UTCSID, " & _
strSQLstatement1 & ".UTLCID, " & _
strSQLstatement1 & ".UTRCLS, " & _
strSQLstatement1 & ".UTSVC, " & _
strSQLstatement1 & ".UTPEYY, " & _
strSQLstatement1 & ".UTPEMM, " & _
strSQLstatement1 & ".UTAGE, " & _
strSQLstatement1 & ".UTTTYP, " & _
strSQLstatement1 & ".UTTDSC, " & _
strSQLstatement1 & ".UTTAMT, " & _
strSQLstatement1 & ".UTUNPD "

strSQLfrom2 = _
"FROM " & _
"tblAcctsRecAging_Details RIGHT JOIN " & strSQLstatement1 & " ON " & _
"tblAcctsRecAging_Details.LocID = " & strSQLstatement1 & ".UTLCID; )"

strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2

So NOW what's my problem? Access doesn't like this. I'm getting an error msg:

Error # 3075 was generated by MSAcess
Syntax error. in the query expression '(SELECT distinct SELECT distinct
CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC, CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTAGE, CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC,
CXLIB.UT420AP.UTTAMT, CXLIB.UT420AP.UTUNPD'.

(Remember, I was using Access's code as an example...the code that WORKED.) I
didn't understand what it didn't like, so...

I changed the above to this:

strSQLinsert2 = _
"INSERT INTO tblAcctsRecAging_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear, " & _
"PeriodMonth, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
strWhichAmtBilled & "AmtBilled, " & _
strWhichAmtUnpaid & "AmtUnpaid ) "

strSQLselect2 = _
"SELECT " & _
"UTCSID, " & _
"UTLCID, " & _
"UTRCLS, " & _
"UTSVC, " & _
"UTPEYY, " & _
"UTPEMM, " & _
"UTAGE, " & _
"UTTTYP, " & _
"UTTDSC, " & _
"UTTAMT, " & _
"UTUNPD "

strSQLfrom2 = _
"FROM CXLIB.UT420AP "

strSQLwhere2 = _
"WHERE " & _
"tblAcctsRecAging_Details RIGHT JOIN in (" & strSQLstatement1 & ") ON "
& _
"tblAcctsRecAging_Details.LocID = CXLIB.UT420AP.UTLCID; "

strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2 & _
strSQLwhere2

I've inpected the SQL statements after they're built, and they appear to be
correct. (Well, COMPLETE, anyway. CORRECT I guess is why I'm here asking for
help.)

The error I'm getting is:

Error # 3075 was generated by MSAccess
Syntax error (missing operator) in query expression 'tblAcctsRecAging_Details
RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXL'.

I took out the "in" after "RIGHT JOIN". I'm still getting the same error:

Error # 3075 was generated by MSAccess
Syntax error (missing operator) in query expression 'tblAcctsRecAging_Details
RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXLIB.'.

Since I'm still getting an error, I'm obviously still missing something with
regards to my nested query. Can/will anyone hazard a guess?

Access = 2000
OS = XP

Thanks a lot, in advance... Any help is appreciated...

Let me begin by saying that my familiarity with DB2 on AS/400 is *very*
minimal - a few days several years ago - so I'm not speaking as an expert on
DB2 for AS/400! However, this appears to be mainly an issue of SQL, which I
know reasonably well, and you welcomed *all* replies so I'm going to jump in
and risk making an idiot of myself ;-)

The rest of my remarks are interspersed below.

Rhino

"Tcs" <tsmith@eastpointcity.org> wrote in message
news:nco9i0h6429an0meibun84t86epdecicuf@4ax.com...
> My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I
haven't
> loaded it yet. I'm still using MS Access. And no, I don't believe this
is an
> Access question. (But who knows? I COULD be wrong... I've tried the
> access group...twice...and all I get is "Access doesn't like ".", which I
know,
> or that my query names are too long, as there's a limit to the length of
the SQL
> statement(s).

Do you mean that the Access group literally said that they didn't like the
periods in your SQL statements or are you using '.' as a placeholder, like I
might say "such-and-such"? If you are using the period as a placeholder,
what were the specific things they told you about your syntax?

I'm not sure why you are dismissing the remarks people on the Access group
made. If Access really does have specific limits on the length of the
statements or your query names, those would appear to be genuine things that
you need to take into account, unless you plan to abandon Access from your
approach.

> But this works when I don't try to do it from VB.)

*Which* things work when you don't do them from VB? Do you mean that all of
your queries work okay when not done through VB or just particular ones?
Which ones?

When you try them without using VB, just exactly how are you trying them? In
other words, what are you using instead of VB?

> I THINK my
> problem is just a syntax problem, seeing as this is my first experience
with
> running pass thru queries from VB code. So here goes...
>

> I guess the first question to ask is - Can a pass-thru query be a nested
query
> as applies to VB code? (When I run my two queries manually, I get the
results I
> want...)
>
I don't know VB (Visual Basic) at all and I'm not sure what you mean by a
pass-thru query. I'm just telling you that in case some of my later remarks
seem especially clueless.

> I have a local table into which I need to append data I retreive from our
> AS/400. I initially developed both the local and pass thru queries which
work
> just fine together...with hardcoded parameters, and run manually (or even
from
> VB, just not "built" in VB).
>
What kind of machine is your local table sitting on? A PC? Another AS/400?

> The local query code (which Access created):
>
> INSERT INTO tblAcctsRecAging_Details
> ( CustID,
> LocID,
> CustClass,
> Serv,
> PeriodYear,
> PeriodMonth,
> AgeCode,
> ChgType,
> ChgDesc,
> CurrentAmtBilled,
> CurrentUnPaid )
>
> SELECT
> qryAcctsRecAging_0010_Current_420_BE.UTCSID,
> qryAcctsRecAging_0010_Current_420_BE.UTLCID,
> qryAcctsRecAging_0010_Current_420_BE.UTRCLS,
> qryAcctsRecAging_0010_Current_420_BE.UTSVC,
> qryAcctsRecAging_0010_Current_420_BE.UTPEYY,
> qryAcctsRecAging_0010_Current_420_BE.UTPEMM,
> qryAcctsRecAging_0010_Current_420_BE.UTAGE,
> qryAcctsRecAging_0010_Current_420_BE.UTTTYP,
> qryAcctsRecAging_0010_Current_420_BE.UTTDSC,
> qryAcctsRecAging_0010_Current_420_BE.UTTAMT,
> qryAcctsRecAging_0010_Current_420_BE.UTUNPD
>
> FROM
> tblAcctsRecAging_Details RIGHT JOIN qryAcctsRecAging_0010_Current_420_BE
ON
> tblAcctsRecAging_Details.LocID =
qryAcctsRecAging_0010_Current_420_BE.UTLCID;
>
Nothing jumps out at me in the above query.

> And the pass thru query (which I created):
>
> SELECT distinct
> CXLIB.UT420AP.UTCSID,
> CXLIB.UT420AP.UTLCID,
> CXLIB.UT420AP.UTRCLS,
> CXLIB.UT420AP.UTSVC,
> CXLIB.UT420AP.UTPEYY,
> CXLIB.UT420AP.UTPEMM,
> CXLIB.UT420AP.UTAGE,
> CXLIB.UT420AP.UTTTYP,
> CXLIB.UT420AP.UTTDSC,
> CXLIB.UT420AP.UTTAMT,
> CXLIB.UT420AP.UTUNPD
>
> FROM
> CXLIB.UT420AP
>
> WHERE
> ((CXLIB.UT420AP.UTAGE='C') AND
> (((CXLIB.UT420AP.UTPEMM)=7) AND ((CXLIB.UT420AP.UTPEYY)=4)) Or
> (((CXLIB.UT420AP.UTPEMM)=8) AND ((CXLIB.UT420AP.UTPEYY)=4)))
>
> ORDER BY
> CXLIB.UT420AP.UTRCLS,
> CXLIB.UT420AP.UTSVC,
> CXLIB.UT420AP.UTPEYY,
> CXLIB.UT420AP.UTPEMM,
> CXLIB.UT420AP.UTTTYP,
> CXLIB.UT420AP.UTTDSC;
>
Are you sure that the above query is what you really want to do? The keyword
DISTINCT applies to all of the column names that follow it in the SELECT
clause. In effect, you are saying that you want all of the distinct
*combinations* of ALL of those columns: UTCSID, UTLCID, UTRCLS, UTSVC,
UTPEYY, UTPEMM, UTAGE, UTTTYP, UTTDSC, UTTAMT, UTUNPD. I usually see
DISTINCT applied to only 1 or 2 or maybe 3 columns but this is 11 columns.

I don't know if you're a beginner or very experienced but I find that most
beginners with SQL think that DISTINCT only applies to the column name
immediately following the DISTINCT keyword and that all of the other columns
in their SELECT will not be affected by the SELECT. That is simply not true.

> The problem is that my parameters are variable. So I created this:
>
> ' compose SQL string 1 for the back end (BE) - DB2
> '
> strSQLselect1 = _
> "SELECT distinct " & _
> "CXLIB.UT420AP.UTCSID, " & _
> "CXLIB.UT420AP.UTLCID, " & _
> "CXLIB.UT420AP.UTRCLS, " & _
> "CXLIB.UT420AP.UTSVC, " & _
> "CXLIB.UT420AP.UTPEYY, " & _
> "CXLIB.UT420AP.UTPEMM, " & _
> "CXLIB.UT420AP.UTAGE, " & _
> "CXLIB.UT420AP.UTTTYP, " & _
> "CXLIB.UT420AP.UTTDSC, " & _
> "CXLIB.UT420AP.UTTAMT, " & _
> "CXLIB.UT420AP.UTUNPD "
>
> strSQLfrom1 = _
> "FROM CXLIB.UT420AP "
>
> strSQLwhere1 = _
> "WHERE " & _
> "((CXLIB.UT420AP.UTAGE='" & strAgeGroup & "') AND " & _
> "(((CXLIB.UT420AP.UTPEMM)=" & int1stMM & ") AND
((CXLIB.UT420AP.UTPEYY)="
> & int1stYY & ")) Or " & _
> "(((CXLIB.UT420AP.UTPEMM)=" & int2ndMM & ") AND
((CXLIB.UT420AP.UTPEYY)="
> & int2ndYY & "))) "
>
> strSQLorderby1 = _
> "ORDER BY " & _
> "CXLIB.UT420AP.UTRCLS, " & _
> "CXLIB.UT420AP.UTSVC, " & _
> "CXLIB.UT420AP.UTPEYY, " & _
> "CXLIB.UT420AP.UTPEMM, " & _
> "CXLIB.UT420AP.UTTTYP, " & _
> "CXLIB.UT420AP.UTTDSC; "
>
> strSQLstatement1 = _
> strSQLselect1 & _
> strSQLfrom1 & _
> strSQLwhere1 & _
> strSQLorderby1
>
> ' compose SQL string 2 for the front end (FE) - Access
> '
> strSQLinsert2 = _
> "INSERT INTO tblAcctsRecAging_Details " & _
> "( CustID, " & _
> "LocID, " & _
> "CustClass, " & _
> "Serv, " & _
> "PeriodYear, " & _
> "PeriodMonth, " & _
> "AgeCode, " & _
> "ChgType, " & _
> "ChgDesc, " & _
> "CurrentAmtBilled, " & _
> "CurrentUnPaid ) "
>
> strSQLselect2 = _
> "SELECT (" & _
> strSQLstatement1 & ".UTCSID, " & _
> strSQLstatement1 & ".UTLCID, " & _
> strSQLstatement1 & ".UTRCLS, " & _
> strSQLstatement1 & ".UTSVC, " & _
> strSQLstatement1 & ".UTPEYY, " & _
> strSQLstatement1 & ".UTPEMM, " & _
> strSQLstatement1 & ".UTAGE, " & _
> strSQLstatement1 & ".UTTTYP, " & _
> strSQLstatement1 & ".UTTDSC, " & _
> strSQLstatement1 & ".UTTAMT, " & _
> strSQLstatement1 & ".UTUNPD "
>
> strSQLfrom2 = _
> "FROM " & _
> "tblAcctsRecAging_Details RIGHT JOIN " & strSQLstatement1 & " ON "
& _
> "tblAcctsRecAging_Details.LocID = " & strSQLstatement1 &
".UTLCID; )"
>
> strSQLstatement2 = _
> strSQLinsert2 & _
> strSQLselect2 & _
> strSQLfrom2
>
The syntax for the above queries looks *very* odd to me. I'm especially
perplexed by the "& _" tokens that are at the end of most - but not *all* -
of the lines. What do the ampersands and underscores mean in these queries?
At first glance, I thought they might be continuation characters but that
doesn't make sense: why do some lines have both ampersand and underscore,
some have only the underscore, and some have neither?

Did you really have to write the statements like this just to get some
variables into the join? It's a lot easier in most languages. Again, I don't
know VB or DB2 on AS/400 so maybe this is a standard approach to statements
in that environment....


> So NOW what's my problem? Access doesn't like this. I'm getting an error
msg:
>
> Error # 3075 was generated by MSAcess
> Syntax error. in the query expression '(SELECT distinct SELECT distinct
> CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
> CXLIB.UT420AP.UTSVC, CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM,
> CXLIB.UT420AP.UTAGE, CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC,
> CXLIB.UT420AP.UTTAMT, CXLIB.UT420AP.UTUNPD'.
>
Have you copied this correctly? I can certainly understand that it wouldn't
like "SELECT distinct" *twice* at the beginning of the query; the keyword
"DISTINCT" can only be in each SELECT clause *ONCE*. And, of course, the
keyword "SELECT" can only appear once in that context too.

Also, are you saying that the error message is for strSQLstatement1,
strSQLstatement2, or both? I'm really not following the way that you are
generating and executing these statements so forgive my confusion.

> (Remember, I was using Access's code as an example...the code that
WORKED.) I
> didn't understand what it didn't like, so...
>
> I changed the above to this:
>
> strSQLinsert2 = _
> "INSERT INTO tblAcctsRecAging_Details " & _
> "( CustID, " & _
> "LocID, " & _
> "CustClass, " & _
> "Serv, " & _
> "PeriodYear, " & _
> "PeriodMonth, " & _
> "AgeCode, " & _
> "ChgType, " & _
> "ChgDesc, " & _
> strWhichAmtBilled & "AmtBilled, " & _
> strWhichAmtUnpaid & "AmtUnpaid ) "
>
> strSQLselect2 = _
> "SELECT " & _
> "UTCSID, " & _
> "UTLCID, " & _
> "UTRCLS, " & _
> "UTSVC, " & _
> "UTPEYY, " & _
> "UTPEMM, " & _
> "UTAGE, " & _
> "UTTTYP, " & _
> "UTTDSC, " & _
> "UTTAMT, " & _
> "UTUNPD "
>
> strSQLfrom2 = _
> "FROM CXLIB.UT420AP "
>
> strSQLwhere2 = _
> "WHERE " & _
> "tblAcctsRecAging_Details RIGHT JOIN in (" & strSQLstatement1 & ")
ON "
> & _
> "tblAcctsRecAging_Details.LocID = CXLIB.UT420AP.UTLCID; "
>
Your join definitely looks wrong to me. It needs to be something more like:

FROM EMPLOYEE e RIGHT JOIN DEPARTMENT D on e.workdept in ('D11', 'E21')
AND e.workdept = d.deptno

[Personally, I only put join conditions in the FROM clause and put local
predicates like 'e.workdept in ('D11', 'E21')' in my WHERE clause; I find
that easier to read. However, I'm not saying that it is *wrong* to put local
predicates in your FROM clause.]

> strSQLstatement2 = _
> strSQLinsert2 & _
> strSQLselect2 & _
> strSQLfrom2 & _
> strSQLwhere2
>
> I've inpected the SQL statements after they're built, and they appear to
be
> correct. (Well, COMPLETE, anyway. CORRECT I guess is why I'm here asking
for
> help.)
>
> The error I'm getting is:
>
> Error # 3075 was generated by MSAccess
> Syntax error (missing operator) in query expression
'tblAcctsRecAging_Details
> RIGHT JOIN in (SELECT distinct
> CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
> CXLIB.UT420AP.UTSVC,
> CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
> CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXL'.
>
> I took out the "in" after "RIGHT JOIN".

See my remarks about your join, above.

Putting a SELECT statement within an IN clause is not remotely valid syntax
so I can see why Access doesn't like that!

> I'm still getting the same error:
>
> Error # 3075 was generated by MSAccess
> Syntax error (missing operator) in query expression
'tblAcctsRecAging_Details
> RIGHT JOIN in (SELECT distinct
> CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
> CXLIB.UT420AP.UTSVC,
> CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
> CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXLIB.'.
>
> Since I'm still getting an error, I'm obviously still missing something
with
> regards to my nested query. Can/will anyone hazard a guess?
>
Again, I'm still a little confused about which precise statement each of
these error message refers to. I also don't understand why you've written
the statements the way you have with all of this concatenation of strings.
But, again, maybe this is the only way to do these things in your
environment so I'll assume you've done it in the only way possible.
Personally, if I had to write this program, I'd do it using Java.
PreparedStatement objects would do the job quite nicely and a lot more
simply; but that's just me ;-)

> Access = 2000
> OS = XP
>
> Thanks a lot, in advance... Any help is appreciated...
>
I probably haven't been much help but maybe something I've said will ring a
bell and help you see what's wrong. Or, more likely, someone with more
AS/400 and VB experience will see what you're doing wrong and set you
straight.

Good luck!

Rhino

Sorry for the delay in my reply. I've been out the past several days. (Back
problems.)

On Thu, 19 Aug 2004 16:26:03 -0400, "Rhino" <rhino1@NOSPAM.sympatico.ca> wrote:

>Let me begin by saying that my familiarity with DB2 on AS/400 is *very*
>minimal - a few days several years ago - so I'm not speaking as an expert on
>DB2 for AS/400! However, this appears to be mainly an issue of SQL, which I
>know reasonably well, and you welcomed *all* replies so I'm going to jump in
>and risk making an idiot of myself ;-)

ABSOLUTELY! GREAT! THANK YOU!

>
>The rest of my remarks are interspersed below.
>
>Rhino
>
>"Tcs" <tsmith@eastpointcity.org> wrote in message
>news:nco9i0h6429an0meibun84t86epdecicuf@4ax.com.. .
>> My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I
>haven't
>> loaded it yet. I'm still using MS Access. And no, I don't believe this
>is an
>> Access question. (But who knows? I COULD be wrong... I've tried the
>> access group...twice...and all I get is "Access doesn't like ".", which I
>know,
>> or that my query names are too long, as there's a limit to the length of
>the SQL
>> statement(s).
>
>Do you mean that the Access group literally said that they didn't like the
>periods in your SQL statements or are you using '.' as a placeholder, like I
>might say "such-and-such"? If you are using the period as a placeholder,
>what were the specific things they told you about your syntax?

Yes, they said that Access doesn't like '.'. And I know this. When I link a
DB2 table to Access it replaces the "." with '_'. So CXLIB.UT420AP becomes
CXLIB_UT420AP. But for a "PASS-THRU" query, which Access does NOT process, this
shouldn't make any difference. Besides, DB2 doesn't want '_'. Instead, IT
wants '.'.

>
>I'm not sure why you are dismissing the remarks people on the Access group
>made. If Access really does have specific limits on the length of the
>statements or your query names, those would appear to be genuine things that
>you need to take into account, unless you plan to abandon Access from your
>approach.

I don't mean to dismiss them out of hand, but I don't see how they apply, in
this case, at least. (Read next portion.) I'll confess that my names ten to be
long. And in the right circumstance, too long. But so far...Access hasn't
barfed on my names being too long.
>
>> But this works when I don't try to do it from VB.)
>
>*Which* things work when you don't do them from VB? Do you mean that all of
>your queries work okay when not done through VB or just particular ones?
>Which ones?

My two queries. My "PASS-THRU" (backend) query, and my local (frontend) query.
I created both with Access's Query Builder grid. I can run the pass-thru query
interactively, and it returns the data I want. IT WORKS.

(Note: "Pass-thru" query to Access means a query native to the backend which
will NOT be processed by Access. (Access has its syntax and the BE has its
syntax.) It will be sent to the BE, and the returned data used.)

My "Local" (frontend) query. When I run it interactively, it uses the pass-thru
query as input, and goes and retrieves the data, just like it should. IT WORKS.

In VB, I'm building the SQL statements for each. Then I run an Access command
"DoCmd.RunSQL". This takes my SQL statement(s) and runs them. THIS is what
appears to be NOT working. But I think it's my syntax. (I know it may NOT be.)
I haven't dealt with nested queries before. But what I'm asking Access to do
isn't anything that it CAN'T do. IT'S ALREADY done it. Just not thru VB.

And NO ONE...seems to want to comment on my SQL statement. Of the couple
responses I've received, it's been the "Access doesn't like '>', or the "Your
names are too long". And this name, isn't even USED by Access when I try to run
it thru VB.

Look at the SQL statement below. Notice the
"qryAcctsRecAging_0010_Current_420_BE" query name. Too long? Perhaps. BUT IT
DOES WORK. (The "BE" on the end means that this is my pass-sthru, or backend,
query.) This is what Access has built. APPARENTLY it does more processing with
this, as this is NOT a usable statement, in this form. So I've tried to modify
it as I BELIEVE it needs to be modified. And I'm getting an error.

If someone wants to say that "CXLIB.UT420AP.UTCSID" is too long, well then, I
guess I'm SOL. DB2 won't understand anything less. This is the file, table,
and field that I want. On the other hand, if this is too long, then why does it
work when i don't try thru VB?

>
>When you try them without using VB, just exactly how are you trying them? In
>other words, what are you using instead of VB?
>
>> I THINK my
>> problem is just a syntax problem, seeing as this is my first experience
>with
>> running pass thru queries from VB code. So here goes...
>>
>
>> I guess the first question to ask is - Can a pass-thru query be a nested
>query
>> as applies to VB code? (When I run my two queries manually, I get the
>results I
>> want...)
>>
>I don't know VB (Visual Basic) at all and I'm not sure what you mean by a
>pass-thru query. I'm just telling you that in case some of my later remarks
>seem especially clueless.

Not a problem. (See above.) I understand.

>
>> I have a local table into which I need to append data I retreive from our
>> AS/400. I initially developed both the local and pass thru queries which
>work
>> just fine together...with hardcoded parameters, and run manually (or even
>from
>> VB, just not "built" in VB).
>>
>What kind of machine is your local table sitting on? A PC? Another AS/400?

2.6GHz P4, OS = XP, RAM = 1gb.

>
>> The local query code (which Access created):
>>
>> INSERT INTO tblAcctsRecAging_Details
>> ( CustID,
>> LocID,
>> CustClass,
>> Serv,
>> PeriodYear,
>> PeriodMonth,
>> AgeCode,
>> ChgType,
>> ChgDesc,
>> CurrentAmtBilled,
>> CurrentUnPaid )
>>
>> SELECT
>> qryAcctsRecAging_0010_Current_420_BE.UTCSID,
>> qryAcctsRecAging_0010_Current_420_BE.UTLCID,
>> qryAcctsRecAging_0010_Current_420_BE.UTRCLS,
>> qryAcctsRecAging_0010_Current_420_BE.UTSVC,
>> qryAcctsRecAging_0010_Current_420_BE.UTPEYY,
>> qryAcctsRecAging_0010_Current_420_BE.UTPEMM,
>> qryAcctsRecAging_0010_Current_420_BE.UTAGE,
>> qryAcctsRecAging_0010_Current_420_BE.UTTTYP,
>> qryAcctsRecAging_0010_Current_420_BE.UTTDSC,
>> qryAcctsRecAging_0010_Current_420_BE.UTTAMT,
>> qryAcctsRecAging_0010_Current_420_BE.UTUNPD
>>
>> FROM
>> tblAcctsRecAging_Details RIGHT JOIN qryAcctsRecAging_0010_Current_420_BE
>ON
>> tblAcctsRecAging_Details.LocID =
>qryAcctsRecAging_0010_Current_420_BE.UTLCID;
>>
>Nothing jumps out at me in the above query.
>
>> And the pass thru query (which I created):
>>
>> SELECT distinct
>> CXLIB.UT420AP.UTCSID,
>> CXLIB.UT420AP.UTLCID,
>> CXLIB.UT420AP.UTRCLS,
>> CXLIB.UT420AP.UTSVC,
>> CXLIB.UT420AP.UTPEYY,
>> CXLIB.UT420AP.UTPEMM,
>> CXLIB.UT420AP.UTAGE,
>> CXLIB.UT420AP.UTTTYP,
>> CXLIB.UT420AP.UTTDSC,
>> CXLIB.UT420AP.UTTAMT,
>> CXLIB.UT420AP.UTUNPD
>>
>> FROM
>> CXLIB.UT420AP
>>
>> WHERE
>> ((CXLIB.UT420AP.UTAGE='C') AND
>> (((CXLIB.UT420AP.UTPEMM)=7) AND ((CXLIB.UT420AP.UTPEYY)=4)) Or
>> (((CXLIB.UT420AP.UTPEMM)=8) AND ((CXLIB.UT420AP.UTPEYY)=4)))
>>
>> ORDER BY
>> CXLIB.UT420AP.UTRCLS,
>> CXLIB.UT420AP.UTSVC,
>> CXLIB.UT420AP.UTPEYY,
>> CXLIB.UT420AP.UTPEMM,
>> CXLIB.UT420AP.UTTTYP,
>> CXLIB.UT420AP.UTTDSC;
>>
>Are you sure that the above query is what you really want to do? The keyword
>DISTINCT applies to all of the column names that follow it in the SELECT
>clause. In effect, you are saying that you want all of the distinct
>*combinations* of ALL of those columns: UTCSID, UTLCID, UTRCLS, UTSVC,
>UTPEYY, UTPEMM, UTAGE, UTTTYP, UTTDSC, UTTAMT, UTUNPD. I usually see
>DISTINCT applied to only 1 or 2 or maybe 3 columns but this is 11 columns.

Until you asked...I THOUGHT so. Without 'distinct' I get erroneous errors,
duplications. Access has no provision for individual 'distinct's, or at least
none that I'm aware of. I just normally add it after my select statement. When
I've done so, my errors disappear.

Access does not generate a SQL statement with the word 'distinct'. I've only
added it after I see the returned data, and see that it's not correct.

>
>I don't know if you're a beginner or very experienced but I find that most
>beginners with SQL think that DISTINCT only applies to the column name
>immediately following the DISTINCT keyword and that all of the other columns
>in their SELECT will not be affected by the SELECT. That is simply not true.

Beginner? Guilty.

Actually, I don't ever recall thinking of it as applying to ANY particular
column.

>
>> The problem is that my parameters are variable. So I created this:
>>
>> ' compose SQL string 1 for the back end (BE) - DB2
>> '
>> strSQLselect1 = _
>> "SELECT distinct " & _
>> "CXLIB.UT420AP.UTCSID, " & _
>> "CXLIB.UT420AP.UTLCID, " & _
>> "CXLIB.UT420AP.UTRCLS, " & _
>> "CXLIB.UT420AP.UTSVC, " & _
>> "CXLIB.UT420AP.UTPEYY, " & _
>> "CXLIB.UT420AP.UTPEMM, " & _
>> "CXLIB.UT420AP.UTAGE, " & _
>> "CXLIB.UT420AP.UTTTYP, " & _
>> "CXLIB.UT420AP.UTTDSC, " & _
>> "CXLIB.UT420AP.UTTAMT, " & _
>> "CXLIB.UT420AP.UTUNPD "
>>
>> strSQLfrom1 = _
>> "FROM CXLIB.UT420AP "
>>
>> strSQLwhere1 = _
>> "WHERE " & _
>> "((CXLIB.UT420AP.UTAGE='" & strAgeGroup & "') AND " & _
>> "(((CXLIB.UT420AP.UTPEMM)=" & int1stMM & ") AND
>((CXLIB.UT420AP.UTPEYY)="
>> & int1stYY & ")) Or " & _
>> "(((CXLIB.UT420AP.UTPEMM)=" & int2ndMM & ") AND
>((CXLIB.UT420AP.UTPEYY)="
>> & int2ndYY & "))) "
>>
>> strSQLorderby1 = _
>> "ORDER BY " & _
>> "CXLIB.UT420AP.UTRCLS, " & _
>> "CXLIB.UT420AP.UTSVC, " & _
>> "CXLIB.UT420AP.UTPEYY, " & _
>> "CXLIB.UT420AP.UTPEMM, " & _
>> "CXLIB.UT420AP.UTTTYP, " & _
>> "CXLIB.UT420AP.UTTDSC; "
>>
>> strSQLstatement1 = _
>> strSQLselect1 & _
>> strSQLfrom1 & _
>> strSQLwhere1 & _
>> strSQLorderby1
>>
>> ' compose SQL string 2 for the front end (FE) - Access
>> '
>> strSQLinsert2 = _
>> "INSERT INTO tblAcctsRecAging_Details " & _
>> "( CustID, " & _
>> "LocID, " & _
>> "CustClass, " & _
>> "Serv, " & _
>> "PeriodYear, " & _
>> "PeriodMonth, " & _
>> "AgeCode, " & _
>> "ChgType, " & _
>> "ChgDesc, " & _
>> "CurrentAmtBilled, " & _
>> "CurrentUnPaid ) "
>>
>> strSQLselect2 = _
>> "SELECT (" & _
>> strSQLstatement1 & ".UTCSID, " & _
>> strSQLstatement1 & ".UTLCID, " & _
>> strSQLstatement1 & ".UTRCLS, " & _
>> strSQLstatement1 & ".UTSVC, " & _
>> strSQLstatement1 & ".UTPEYY, " & _
>> strSQLstatement1 & ".UTPEMM, " & _
>> strSQLstatement1 & ".UTAGE, " & _
>> strSQLstatement1 & ".UTTTYP, " & _
>> strSQLstatement1 & ".UTTDSC, " & _
>> strSQLstatement1 & ".UTTAMT, " & _
>> strSQLstatement1 & ".UTUNPD "
>>
>> strSQLfrom2 = _
>> "FROM " & _
>> "tblAcctsRecAging_Details RIGHT JOIN " & strSQLstatement1 & " ON "
>& _
>> "tblAcctsRecAging_Details.LocID = " & strSQLstatement1 &
>".UTLCID; )"
>>
>> strSQLstatement2 = _
>> strSQLinsert2 & _
>> strSQLselect2 & _
>> strSQLfrom2
>>
>The syntax for the above queries looks *very* odd to me.

I was just following Access's lead.

>I'm especially
>perplexed by the "& _" tokens that are at the end of most - but not *all* -
>of the lines. What do the ampersands and underscores mean in these queries?

Ampersand is contenation character. "Horse " & "Hockey" equals "Horse Hockey".
The underscore is a line conituation character.

>At first glance, I thought they might be continuation characters but that
>doesn't make sense: why do some lines have both ampersand and underscore,
>some have only the underscore, and some have neither?

Because I'm building the statement IN PIECES. The "INSERT INTO " is separate.
The "SELECT" is separate. The "FROM is separate. Then I pull them all together
at the end, with:

strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2
>
>Did you really have to write the statements like this just to get some
>variables into the join? It's a lot easier in most languages. Again, I don't
>know VB or DB2 on AS/400 so maybe this is a standard approach to statements
>in that environment....

I have formatted the statements the way I did for legibility. It's easier for
me to read and understand. Also, using separate pieces to make one whole
statment is necessary as VB has a '_' character limit of 25 per occurrence. (So
I had to split them up.)

>
>
>> So NOW what's my problem? Access doesn't like this. I'm getting an error
>msg:
>>
>> Error # 3075 was generated by MSAcess
>> Syntax error. in the query expression '(SELECT distinct SELECT distinct
>> CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
>> CXLIB.UT420AP.UTSVC, CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM,
>> CXLIB.UT420AP.UTAGE, CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC,
>> CXLIB.UT420AP.UTTAMT, CXLIB.UT420AP.UTUNPD'.
>>
>Have you copied this correctly? I can certainly understand that it wouldn't
>like "SELECT distinct" *twice* at the beginning of the query; the keyword
>"DISTINCT" can only be in each SELECT clause *ONCE*. And, of course, the
>keyword "SELECT" can only appear once in that context too.
>
>Also, are you saying that the error message is for strSQLstatement1,
>strSQLstatement2, or both? I'm really not following the way that you are
>generating and executing these statements so forgive my confusion.
>
>> (Remember, I was using Access's code as an example...the code that
>WORKED.) I
>> didn't understand what it didn't like, so...
>>
>> I changed the above to this:
>>
>> strSQLinsert2 = _
>> "INSERT INTO tblAcctsRecAging_Details " & _
>> "( CustID, " & _
>> "LocID, " & _
>> "CustClass, " & _
>> "Serv, " & _
>> "PeriodYear, " & _
>> "PeriodMonth, " & _
>> "AgeCode, " & _
>> "ChgType, " & _
>> "ChgDesc, " & _
>> strWhichAmtBilled & "AmtBilled, " & _
>> strWhichAmtUnpaid & "AmtUnpaid ) "
>>
>> strSQLselect2 = _
>> "SELECT " & _
>> "UTCSID, " & _
>> "UTLCID, " & _
>> "UTRCLS, " & _
>> "UTSVC, " & _
>> "UTPEYY, " & _
>> "UTPEMM, " & _
>> "UTAGE, " & _
>> "UTTTYP, " & _
>> "UTTDSC, " & _
>> "UTTAMT, " & _
>> "UTUNPD "
>>
>> strSQLfrom2 = _
>> "FROM CXLIB.UT420AP "
>>
>> strSQLwhere2 = _
>> "WHERE " & _
>> "tblAcctsRecAging_Details RIGHT JOIN in (" & strSQLstatement1 & ")
>ON "
>> & _
>> "tblAcctsRecAging_Details.LocID = CXLIB.UT420AP.UTLCID; "
>>
>Your join definitely looks wrong to me. It needs to be something more like:
>
>FROM EMPLOYEE e RIGHT JOIN DEPARTMENT D on e.workdept in ('D11', 'E21')
>AND e.workdept = d.deptno
>
>[Personally, I only put join conditions in the FROM clause and put local
>predicates like 'e.workdept in ('D11', 'E21')' in my WHERE clause; I find
>that easier to read. However, I'm not saying that it is *wrong* to put local
>predicates in your FROM clause.]

>
>> strSQLstatement2 = _
>> strSQLinsert2 & _
>> strSQLselect2 & _
>> strSQLfrom2 & _
>> strSQLwhere2
>>
>> I've inpected the SQL statements after they're built, and they appear to
>be
>> correct. (Well, COMPLETE, anyway. CORRECT I guess is why I'm here asking
>for
>> help.)
>>
>> The error I'm getting is:
>>
>> Error # 3075 was generated by MSAccess
>> Syntax error (missing operator) in query expression
>'tblAcctsRecAging_Details
>> RIGHT JOIN in (SELECT distinct
>> CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
>> CXLIB.UT420AP.UTSVC,
>> CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
>> CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXL'.
>>
>> I took out the "in" after "RIGHT JOIN".
>
>See my remarks about your join, above.
>
>Putting a SELECT statement within an IN clause is not remotely valid syntax
>so I can see why Access doesn't like that!

But I've seen other messages in the Access group that appear the same when
they're talking about nested queries. That's why I did, what I did. Perhaps
it's a 'backend' vs 'frontend' thing.

>
>> I'm still getting the same error:
>>
>> Error # 3075 was generated by MSAccess
>> Syntax error (missing operator) in query expression
>'tblAcctsRecAging_Details
>> RIGHT JOIN in (SELECT distinct
>> CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
>> CXLIB.UT420AP.UTSVC,
>> CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
>> CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXLIB.'.
>>
>> Since I'm still getting an error, I'm obviously still missing something
>with
>> regards to my nested query. Can/will anyone hazard a guess?
>>
>Again, I'm still a little confused about which precise statement each of
>these error message refers to. I also don't understand why you've written
>the statements the way you have with all of this concatenation of strings.
>But, again, maybe this is the only way to do these things in your
>environment so I'll assume you've done it in the only way possible.
>Personally, if I had to write this program, I'd do it using Java.
>PreparedStatement objects would do the job quite nicely and a lot more
>simply; but that's just me ;-)

I don't know Java.

>
>> Access = 2000
>> OS = XP
>>
>> Thanks a lot, in advance... Any help is appreciated...
>>
>I probably haven't been much help but maybe something I've said will ring a
>bell and help you see what's wrong. Or, more likely, someone with more
>AS/400 and VB experience will see what you're doing wrong and set you
>straight.
>
>Good luck!
>
>Rhino
>

Thank you!!!

Sorry Tcs but I don't have anything significant to add that would help you.
Even with your explanations, I don't have any more to contribute.

I have only a few days AS/400 experience and that was several years and
versions of DB2 ago. I have done a few hours of work with Access but that
was over two years ago. I don't have access to either so there is no way I
can figure out what is going wrong for you.

Here are some options that might help:

1. Others may see this post and offer to help. I'm not optimistic about that
happening; I was the only one that tried helping with your original post in
several days.

2. The manuals for the products that you are using may help. Perhaps some
examples in those manuals or in the Help screens will help you see what you
are doing wrong. You might try doing some very very simply queries with the
tools you are using first, just to make sure you have the technique down
right, before you try a very complicated query.

3. There are probably others who use the combination of tools that you are
using, maybe even within your shop. Ask around, maybe someone else at your
office has has more experience with these tools. After all, someone chose
and purchased those tools; presumably that person has some idea how they
work. If that doesn't pan out, you might try the vendors of those products
directly: you may already be paying for tech support on these products, in
which case you might as well use it. If that doesn't work out, try user
groups. You may very well have DB2, AS/400, or Access user groups in your
area where one of the members has experience with your combination of tools.
Also, consider doing Google web searches for the tool combination that you
are using; maybe someone has written an FAQ or posted some examples of how
to use that combination.

I'm not sure what else to suggest.

Rhino

"Tcs" <tsmith@eastpointcity.org> wrote in message
news:al9pi0h03idlgto9v19of0bnk514vu11sk@4ax.com...
> Sorry for the delay in my reply. I've been out the past several days.
(Back
> problems.)
>
> On Thu, 19 Aug 2004 16:26:03 -0400, "Rhino" <rhino1@NOSPAM.sympatico.ca>
wrote:
>
> >Let me begin by saying that my familiarity with DB2 on AS/400 is *very*
> >minimal - a few days several years ago - so I'm not speaking as an expert
on
> >DB2 for AS/400! However, this appears to be mainly an issue of SQL, which
I
> >know reasonably well, and you welcomed *all* replies so I'm going to jump
in
> >and risk making an idiot of myself ;-)
>
> ABSOLUTELY! GREAT! THANK YOU!
>
> >
> >The rest of my remarks are interspersed below.
> >
> >Rhino
> >
> >"Tcs" <tsmith@eastpointcity.org> wrote in message
> >news:nco9i0h6429an0meibun84t86epdecicuf@4ax.com.. .
> >> My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I
> >haven't
> >> loaded it yet. I'm still using MS Access. And no, I don't believe
this
> >is an
> >> Access question. (But who knows? I COULD be wrong... I've tried
the
> >> access group...twice...and all I get is "Access doesn't like ".", which
I
> >know,
> >> or that my query names are too long, as there's a limit to the length
of
> >the SQL
> >> statement(s).
> >
> >Do you mean that the Access group literally said that they didn't like
the
> >periods in your SQL statements or are you using '.' as a placeholder,
like I
> >might say "such-and-such"? If you are using the period as a placeholder,
> >what were the specific things they told you about your syntax?
>
> Yes, they said that Access doesn't like '.'. And I know this. When I
link a
> DB2 table to Access it replaces the "." with '_'. So CXLIB.UT420AP
becomes
> CXLIB_UT420AP. But for a "PASS-THRU" query, which Access does NOT
process, this
> shouldn't make any difference. Besides, DB2 doesn't want '_'. Instead,
IT
> wants '.'.
>
> >
> >I'm not sure why you are dismissing the remarks people on the Access
group
> >made. If Access really does have specific limits on the length of the
> >statements or your query names, those would appear to be genuine things
that
> >you need to take into account, unless you plan to abandon Access from
your
> >approach.
>
> I don't mean to dismiss them out of hand, but I don't see how they apply,
in
> this case, at least. (Read next portion.) I'll confess that my names ten
to be
> long. And in the right circumstance, too long. But so far...Access
hasn't
> barfed on my names being too long.
> >
> >> But this works when I don't try to do it from VB.)
> >
> >*Which* things work when you don't do them from VB? Do you mean that all
of
> >your queries work okay when not done through VB or just particular ones?
> >Which ones?
>
> My two queries. My "PASS-THRU" (backend) query, and my local (frontend)
query.
> I created both with Access's Query Builder grid. I can run the pass-thru
query
> interactively, and it returns the data I want. IT WORKS.
>
> (Note: "Pass-thru" query to Access means a query native to the backend
which
> will NOT be processed by Access. (Access has its syntax and the BE has
its
> syntax.) It will be sent to the BE, and the returned data used.)
>
> My "Local" (frontend) query. When I run it interactively, it uses the
pass-thru
> query as input, and goes and retrieves the data, just like it should. IT
WORKS.
>
> In VB, I'm building the SQL statements for each. Then I run an Access
command
> "DoCmd.RunSQL". This takes my SQL statement(s) and runs them. THIS is
what
> appears to be NOT working. But I think it's my syntax. (I know it may
NOT be.)
> I haven't dealt with nested queries before. But what I'm asking Access to
do
> isn't anything that it CAN'T do. IT'S ALREADY done it. Just not thru VB.
>
> And NO ONE...seems to want to comment on my SQL statement. Of the couple
> responses I've received, it's been the "Access doesn't like '>', or the
"Your
> names are too long". And this name, isn't even USED by Access when I try
to run
> it thru VB.
>
> Look at the SQL statement below. Notice the
> "qryAcctsRecAging_0010_Current_420_BE" query name. Too long? Perhaps.
BUT IT
> DOES WORK. (The "BE" on the end means that this is my pass-sthru, or
backend,
> query.) This is what Access has built. APPARENTLY it does more
processing with
> this, as this is NOT a usable statement, in this form. So I've tried to
modify
> it as I BELIEVE it needs to be modified. And I'm getting an error.
>
> If someone wants to say that "CXLIB.UT420AP.UTCSID" is too long, well
then, I
> guess I'm SOL. DB2 won't understand anything less. This is the file,
table,
> and field that I want. On the other hand, if this is too long, then why
does it
> work when i don't try thru VB?
>
> >
> >When you try them without using VB, just exactly how are you trying them?
In
> >other words, what are you using instead of VB?
> >
> >> I THINK my
> >> problem is just a syntax problem, seeing as this is my first experience
> >with
> >> running pass thru queries from VB code. So here goes...
> >>
> >
> >> I guess the first question to ask is - Can a pass-thru query be a
nested
> >query
> >> as applies to VB code? (When I run my two queries manually, I get the
> >results I
> >> want...)
> >>
> >I don't know VB (Visual Basic) at all and I'm not sure what you mean by
a
> >pass-thru query. I'm just telling you that in case some of my later
remarks
> >seem especially clueless.
>
> Not a problem. (See above.) I understand.
>
> >
> >> I have a local table into which I need to append data I retreive from
our
> >> AS/400. I initially developed both the local and pass thru queries
which
> >work
> >> just fine together...with hardcoded parameters, and run manually (or
even
> >from
> >> VB, just not "built" in VB).
> >>
> >What kind of machine is your local table sitting on? A PC? Another
AS/400?
>
> 2.6GHz P4, OS = XP, RAM = 1gb.
>
> >
> >> The local query code (which Access created):
> >>
> >> INSERT INTO tblAcctsRecAging_Details
> >> ( CustID,
> >> LocID,
> >> CustClass,
> >> Serv,
> >> PeriodYear,
> >> PeriodMonth,
> >> AgeCode,
> >> ChgType,
> >> ChgDesc,
> >> CurrentAmtBilled,
> >> CurrentUnPaid )
> >>
> >> SELECT
> >> qryAcctsRecAging_0010_Current_420_BE.UTCSID,
> >> qryAcctsRecAging_0010_Current_420_BE.UTLCID,
> >> qryAcctsRecAging_0010_Current_420_BE.UTRCLS,
> >> qryAcctsRecAging_0010_Current_420_BE.UTSVC,
> >> qryAcctsRecAging_0010_Current_420_BE.UTPEYY,
> >> qryAcctsRecAging_0010_Current_420_BE.UTPEMM,
> >> qryAcctsRecAging_0010_Current_420_BE.UTAGE,
> >> qryAcctsRecAging_0010_Current_420_BE.UTTTYP,
> >> qryAcctsRecAging_0010_Current_420_BE.UTTDSC,
> >> qryAcctsRecAging_0010_Current_420_BE.UTTAMT,
> >> qryAcctsRecAging_0010_Current_420_BE.UTUNPD
> >>
> >> FROM
> >> tblAcctsRecAging_Details RIGHT JOIN
qryAcctsRecAging_0010_Current_420_BE
> >ON
> >> tblAcctsRecAging_Details.LocID =
> >qryAcctsRecAging_0010_Current_420_BE.UTLCID;
> >>
> >Nothing jumps out at me in the above query.
> >
> >> And the pass thru query (which I created):
> >>
> >> SELECT distinct
> >> CXLIB.UT420AP.UTCSID,
> >> CXLIB.UT420AP.UTLCID,
> >> CXLIB.UT420AP.UTRCLS,
> >> CXLIB.UT420AP.UTSVC,
> >> CXLIB.UT420AP.UTPEYY,
> >> CXLIB.UT420AP.UTPEMM,
> >> CXLIB.UT420AP.UTAGE,
> >> CXLIB.UT420AP.UTTTYP,
> >> CXLIB.UT420AP.UTTDSC,
> >> CXLIB.UT420AP.UTTAMT,
> >> CXLIB.UT420AP.UTUNPD
> >>
> >> FROM
> >> CXLIB.UT420AP
> >>
> >> WHERE
> >> ((CXLIB.UT420AP.UTAGE='C') AND
> >> (((CXLIB.UT420AP.UTPEMM)=7) AND ((CXLIB.UT420AP.UTPEYY)=4)) Or
> >> (((CXLIB.UT420AP.UTPEMM)=8) AND ((CXLIB.UT420AP.UTPEYY)=4)))
> >>
> >> ORDER BY
> >> CXLIB.UT420AP.UTRCLS,
> >> CXLIB.UT420AP.UTSVC,
> >> CXLIB.UT420AP.UTPEYY,
> >> CXLIB.UT420AP.UTPEMM,
> >> CXLIB.UT420AP.UTTTYP,
> >> CXLIB.UT420AP.UTTDSC;
> >>
> >Are you sure that the above query is what you really want to do? The
keyword
> >DISTINCT applies to all of the column names that follow it in the SELECT
> >clause. In effect, you are saying that you want all of the distinct
> >*combinations* of ALL of those columns: UTCSID, UTLCID, UTRCLS, UTSVC,
> >UTPEYY, UTPEMM, UTAGE, UTTTYP, UTTDSC, UTTAMT, UTUNPD. I usually see
> >DISTINCT applied to only 1 or 2 or maybe 3 columns but this is 11
columns.
>
> Until you asked...I THOUGHT so. Without 'distinct' I get erroneous
errors,
> duplications. Access has no provision for individual 'distinct's, or at
least
> none that I'm aware of. I just normally add it after my select statement.
When
> I've done so, my errors disappear.
>
> Access does not generate a SQL statement with the word 'distinct'. I've
only
> added it after I see the returned data, and see that it's not correct.
>
> >
> >I don't know if you're a beginner or very experienced but I find that
most
> >beginners with SQL think that DISTINCT only applies to the column name
> >immediately following the DISTINCT keyword and that all of the other
columns
> >in their SELECT will not be affected by the SELECT. That is simply not
true.
>
> Beginner? Guilty.
>
> Actually, I don't ever recall thinking of it as applying to ANY particular
> column.
>
> >
> >> The problem is that my parameters are variable. So I created this:
> >>
> >> ' compose SQL string 1 for the back end (BE) - DB2
> >> '
> >> strSQLselect1 = _
> >> "SELECT distinct " & _
> >> "CXLIB.UT420AP.UTCSID, " & _
> >> "CXLIB.UT420AP.UTLCID, " & _
> >> "CXLIB.UT420AP.UTRCLS, " & _
> >> "CXLIB.UT420AP.UTSVC, " & _
> >> "CXLIB.UT420AP.UTPEYY, " & _
> >> "CXLIB.UT420AP.UTPEMM, " & _
> >> "CXLIB.UT420AP.UTAGE, " & _
> >> "CXLIB.UT420AP.UTTTYP, " & _
> >> "CXLIB.UT420AP.UTTDSC, " & _
> >> "CXLIB.UT420AP.UTTAMT, " & _
> >> "CXLIB.UT420AP.UTUNPD "
> >>
> >> strSQLfrom1 = _
> >> "FROM CXLIB.UT420AP "
> >>
> >> strSQLwhere1 = _
> >> "WHERE " & _
> >> "((CXLIB.UT420AP.UTAGE='" & strAgeGroup & "') AND " & _
> >> "(((CXLIB.UT420AP.UTPEMM)=" & int1stMM & ") AND
> >((CXLIB.UT420AP.UTPEYY)="
> >> & int1stYY & ")) Or " & _
> >> "(((CXLIB.UT420AP.UTPEMM)=" & int2ndMM & ") AND
> >((CXLIB.UT420AP.UTPEYY)="
> >> & int2ndYY & "))) "
> >>
> >> strSQLorderby1 = _
> >> "ORDER BY " & _
> >> "CXLIB.UT420AP.UTRCLS, " & _
> >> "CXLIB.UT420AP.UTSVC, " & _
> >> "CXLIB.UT420AP.UTPEYY, " & _
> >> "CXLIB.UT420AP.UTPEMM, " & _
> >> "CXLIB.UT420AP.UTTTYP, " & _
> >> "CXLIB.UT420AP.UTTDSC; "
> >>
> >> strSQLstatement1 = _
> >> strSQLselect1 & _
> >> strSQLfrom1 & _
> >> strSQLwhere1 & _
> >> strSQLorderby1
> >>
> >> ' compose SQL string 2 for the front end (FE) - Access
> >> '
> >> strSQLinsert2 = _
> >> "INSERT INTO tblAcctsRecAging_Details " & _
> >> "( CustID, " & _
> >> "LocID, " & _
> >> "CustClass, " & _
> >> "Serv, " & _
> >> "PeriodYear, " & _
> >> "PeriodMonth, " & _
> >> "AgeCode, " & _
> >> "ChgType, " & _
> >> "ChgDesc, " & _
> >> "CurrentAmtBilled, " & _
> >> "CurrentUnPaid ) "
> >>
> >> strSQLselect2 = _
> >> "SELECT (" & _
> >> strSQLstatement1 & ".UTCSID, " & _
> >> strSQLstatement1 & ".UTLCID, " & _
> >> strSQLstatement1 & ".UTRCLS, " & _
> >> strSQLstatement1 & ".UTSVC, " & _
> >> strSQLstatement1 & ".UTPEYY, " & _
> >> strSQLstatement1 & ".UTPEMM, " & _
> >> strSQLstatement1 & ".UTAGE, " & _
> >> strSQLstatement1 & ".UTTTYP, " & _
> >> strSQLstatement1 & ".UTTDSC, " & _
> >> strSQLstatement1 & ".UTTAMT, " & _
> >> strSQLstatement1 & ".UTUNPD "
> >>
> >> strSQLfrom2 = _
> >> "FROM " & _
> >> "tblAcctsRecAging_Details RIGHT JOIN " & strSQLstatement1 & "
ON "
> >& _
> >> "tblAcctsRecAging_Details.LocID = " & strSQLstatement1 &
> >".UTLCID; )"
> >>
> >> strSQLstatement2 = _
> >> strSQLinsert2 & _
> >> strSQLselect2 & _
> >> strSQLfrom2
> >>
> >The syntax for the above queries looks *very* odd to me.
>
> I was just following Access's lead.
>
> >I'm especially
> >perplexed by the "& _" tokens that are at the end of most - but not
*all* -
> >of the lines. What do the ampersands and underscores mean in these
queries?
>
> Ampersand is contenation character. "Horse " & "Hockey" equals "Horse
Hockey".
> The underscore is a line conituation character.
>
> >At first glance, I thought they might be continuation characters but that
> >doesn't make sense: why do some lines have both ampersand and underscore,
> >some have only the underscore, and some have neither?
>
> Because I'm building the statement IN PIECES. The "INSERT INTO " is
separate.
> The "SELECT" is separate. The "FROM is separate. Then I pull them all
together
> at the end, with:
>
> strSQLstatement2 = _
> strSQLinsert2 & _
> strSQLselect2 & _
> strSQLfrom2
> >
> >Did you really have to write the statements like this just to get some
> >variables into the join? It's a lot easier in most languages. Again, I
don't
> >know VB or DB2 on AS/400 so maybe this is a standard approach to
statements
> >in that environment....
>
> I have formatted the statements the way I did for legibility. It's easier
for
> me to read and understand. Also, using separate pieces to make one whole
> statment is necessary as VB has a '_' character limit of 25 per
occurrence. (So
> I had to split them up.)
>
> >
> >
> >> So NOW what's my problem? Access doesn't like this. I'm getting an
error
> >msg:
> >>
> >> Error # 3075 was generated by MSAcess
> >> Syntax error. in the query expression '(SELECT distinct SELECT
distinct
> >> CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
> >> CXLIB.UT420AP.UTSVC, CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM,
> >> CXLIB.UT420AP.UTAGE, CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC,
> >> CXLIB.UT420AP.UTTAMT, CXLIB.UT420AP.UTUNPD'.
> >>
> >Have you copied this correctly? I can certainly understand that it
wouldn't
> >like "SELECT distinct" *twice* at the beginning of the query; the keyword
> >"DISTINCT" can only be in each SELECT clause *ONCE*. And, of course, the
> >keyword "SELECT" can only appear once in that context too.
> >
> >Also, are you saying that the error message is for strSQLstatement1,
> >strSQLstatement2, or both? I'm really not following the way that you are
> >generating and executing these statements so forgive my confusion.
> >
> >> (Remember, I was using Access's code as an example...the code that
> >WORKED.) I
> >> didn't understand what it didn't like, so...
> >>
> >> I changed the above to this:
> >>
> >> strSQLinsert2 = _
> >> "INSERT INTO tblAcctsRecAging_Details " & _
> >> "( CustID, " & _
> >> "LocID, " & _
> >> "CustClass, " & _
> >> "Serv, " & _
> >> "PeriodYear, " & _
> >> "PeriodMonth, " & _
> >> "AgeCode, " & _
> >> "ChgType, " & _
> >> "ChgDesc, " & _
> >> strWhichAmtBilled & "AmtBilled, " & _
> >> strWhichAmtUnpaid & "AmtUnpaid ) "
> >>
> >> strSQLselect2 = _
> >> "SELECT " & _
> >> "UTCSID, " & _
> >> "UTLCID, " & _
> >> "UTRCLS, " & _
> >> "UTSVC, " & _
> >> "UTPEYY, " & _
> >> "UTPEMM, " & _
> >> "UTAGE, " & _
> >> "UTTTYP, " & _
> >> "UTTDSC, " & _
> >> "UTTAMT, " & _
> >> "UTUNPD "
> >>
> >> strSQLfrom2 = _
> >> "FROM CXLIB.UT420AP "
> >>
> >> strSQLwhere2 = _
> >> "WHERE " & _
> >> "tblAcctsRecAging_Details RIGHT JOIN in (" & strSQLstatement1 &
")
> >ON "
> >> & _
> >> "tblAcctsRecAging_Details.LocID = CXLIB.UT420AP.UTLCID; "
> >>
> >Your join definitely looks wrong to me. It needs to be something more
like:
> >
> >FROM EMPLOYEE e RIGHT JOIN DEPARTMENT D on e.workdept in ('D11', 'E21')
> >AND e.workdept = d.deptno
> >
> >[Personally, I only put join conditions in the FROM clause and put local
> >predicates like 'e.workdept in ('D11', 'E21')' in my WHERE clause; I find
> >that easier to read. However, I'm not saying that it is *wrong* to put
local
> >predicates in your FROM clause.]
>
> >
> >> strSQLstatement2 = _
> >> strSQLinsert2 & _
> >> strSQLselect2 & _
> >> strSQLfrom2 & _
> >> strSQLwhere2
> >>
> >> I've inpected the SQL statements after they're built, and they appear
to
> >be
> >> correct. (Well, COMPLETE, anyway. CORRECT I guess is why I'm here
asking
> >for
> >> help.)
> >>
> >> The error I'm getting is:
> >>
> >> Error # 3075 was generated by MSAccess
> >> Syntax error (missing operator) in query expression
> >'tblAcctsRecAging_Details
> >> RIGHT JOIN in (SELECT distinct
> >> CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
> >> CXLIB.UT420AP.UTSVC,
> >> CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
> >> CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXL'.
> >>
> >> I took out the "in" after "RIGHT JOIN".
> >
> >See my remarks about your join, above.
> >
> >Putting a SELECT statement within an IN clause is not remotely valid
syntax
> >so I can see why Access doesn't like that!
>
> But I've seen other messages in the Access group that appear the same when
> they're talking about nested queries. That's why I did, what I did.
Perhaps
> it's a 'backend' vs 'frontend' thing.
>
> >
> >> I'm still getting the same error:
> >>
> >> Error # 3075 was generated by MSAccess
> >> Syntax error (missing operator) in query expression
> >'tblAcctsRecAging_Details
> >> RIGHT JOIN in (SELECT distinct
> >> CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
> >> CXLIB.UT420AP.UTSVC,
> >> CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
> >> CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXLIB.'.
> >>
> >> Since I'm still getting an error, I'm obviously still missing something
> >with
> >> regards to my nested query. Can/will anyone hazard a guess?
> >>
> >Again, I'm still a little confused about which precise statement each of
> >these error message refers to. I also don't understand why you've written
> >the statements the way you have with all of this concatenation of
strings.
> >But, again, maybe this is the only way to do these things in your
> >environment so I'll assume you've done it in the only way possible.
> >Personally, if I had to write this program, I'd do it using Java.
> >PreparedStatement objects would do the job quite nicely and a lot more
> >simply; but that's just me ;-)
>
> I don't know Java.
>
> >
> >> Access = 2000
> >> OS = XP
> >>
> >> Thanks a lot, in advance... Any help is appreciated...
> >>
> >I probably haven't been much help but maybe something I've said will ring
a
> >bell and help you see what's wrong. Or, more likely, someone with more
> >AS/400 and VB experience will see what you're doing wrong and set you
> >straight.
> >
> >Good luck!
> >
> >Rhino
> >
>
> Thank you!!!
>
>
>