VBA, Strange Behavior with queries in code

Glenn Robbin
#1 / 2

Strange Behavior with queries in code

I'm experiencing some strange behavior running queries modified in code
(Access 2.0 running on Win95):

I'm setting some underlying base queries WHERE clauses to speed performance
of a complex main query, all works OK (until the problem discussed below),
here is how I change one of three underlying clauses:

Set MyQry = db.QueryDefs("qryForecastBase")
QSQL = Trim$(MyQry.SQL)
If InStr(QSQL, "WHERE ") Then
QSQL = Left$(QSQL, InStr(QSQL, "WHERE ") - 1)'strip any existing
End If

If InStr(QSQL, ";") Then
QSQL = Left$(QSQL, InStr(QSQL, ";") - 1)
End If
QSQL = QSQL & MyCriteria & ";"
MyQry.SQL = QSQL
db.QueryDefs.Refresh

Now I open the main query:
Set RS = db.OpenRecordset("ShellReport", DB_OPEN_SNAPSHOT)

All runs fine. Now the problem: if the user hits the button to run this
procedure again, the error "Query Too Complex" is generated when it
attempts to open the recordset (even on the same criteria). If the user
then clicks 'OK' on the error message, and AGAIN tries to run the
procedure, all is OK; or If you leave Acces and come back, you can run it
again.

It's a Form procedure that is running, I close the recordset, set MyQry to
nothing, etc., but Access is doing something funny with the code, something
that generating an error once seems to correct. Any help on what's
happening or how to work around would be appreciated.
GR

Fri, 24 Sep 1999 03:00:00 GMT

Richard C. Vander Ar
#2 / 2

Strange Behavior with queries in code

This is a multi-part message in MIME format.

------=_NextPart_000_01BC44B4.2067B200
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

I'm not sure where the problem is, but here are a few suggestions to
troubleshoot:
1) What is MyCriteria? How does that get created?

2) you need to compare the query before and after your mods. Before the
MyQry.SQL = QSQL line, do a Debug.Print QSQL and then copy the result to a
new query in SQL view.

3) Have you tried using parameter queries?

Hope this helps!
--
Ric Vander Ark
LORIC Computing & Electronics
*** Advanced Consulting Solutions ***

Quote:

> I'm experiencing some strange behavior running queries modified in code
> (Access 2.0 running on Win95):

> I'm setting some underlying base queries WHERE clauses to speed
performance
> of a complex main query, all works OK (until the problem discussed
below),
> here is how I change one of three underlying clauses:

> Set MyQry = db.QueryDefs("qryForecastBase")
> QSQL = Trim$(MyQry.SQL)
> If InStr(QSQL, "WHERE ") Then
> QSQL = Left$(QSQL, InStr(QSQL, "WHERE ") - 1)'strip any existing
> End If

> If InStr(QSQL, ";") Then
> QSQL = Left$(QSQL, InStr(QSQL, ";") - 1)
> End If
> QSQL = QSQL & MyCriteria & ";"
> MyQry.SQL = QSQL
> db.QueryDefs.Refresh

------=_NextPart_000_01BC44B4.2067B200
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<html><head></head><BODY bgcolor=3D"#FFFFFF">I'm not sure where the problem is, but =
here are a few suggestions to troubleshoot: 1) What is MyCriteria? =
How does that get created? 2) you need to compare the query =
before and after your mods. Before the MyQry.SQL =3D QSQL line, do a =
Debug.Print QSQL and then copy the result to a new query in SQL view. =
  3) Have you tried using parameter queries? Hope =
this helps! -- Ric Vander Ark LORIC Computing & =
Electronics *** Advanced Consulting Solutions =
*** Glenn Robbins <> wrote in article <>... > I'm experiencing some strange =
behavior running queries modified in code > (Access 2.0 running on =
Win95): > > I'm setting some underlying base queries WHERE =
clauses to speed performance > of a complex main query, all works =
OK (until the problem discussed below), > here is how I change one =
of three underlying clauses: > > Set MyQry =3D =
db.QueryDefs("qryForecastBase") > QSQL =3D =
Trim$(MyQry.SQL) >     If InStr(QSQL, =
"WHERE ") Then > =
        QSQL =3D Left$(QSQL, =
InStr(QSQL, "WHERE ") - 1)'strip any existing > =
    End If > =
            &=
nbsp;        > =
    If InStr(QSQL, ";") Then > =
        QSQL =3D Left$(QSQL, =
InStr(QSQL, ";") - 1) >     End =
If > QSQL =3D QSQL & MyCriteria & ";" > =
MyQry.SQL =3D QSQL > db.QueryDefs.Refresh > 
</body></html>
------=_NextPart_000_01BC44B4.2067B200--

Sun, 26 Sep 1999 03:00:00 GMT