Odd Behaviour with VBA Generated SQL

[Tim Marshall]

For all my apps in A97 and A2003, I write VBA code that constructs SQL
statements for MS Jet. I'm encountering something really odd. I won't
post the SQL, but describe the behaviour to see if anyone has
encountered this.

Consider the following code:

dim strS as string
dim rst as DAO.recordset

strS = "Select blah, blah"

debug.print strS

dbs.openrecordset(strs, dbopensnapshot)

When the code executes, the recordset returns no records when there
should be some.

When I paste the strs from the immediate window into the SQL window of a
querydef, I get no records either.

But when I then open the querydef in the Access query design view and
save it and then run it, I get records!!!!!

The SQL does not change. For simpler SQL (the stuff above is not overly
complicated - it draws data from 4 tables - I have far more complicated
queries in this app and others) this does not happen.

Does anyone have any suggestions?

I've compacted, decompiled (as per David Fenton), compacted, cocmpiled,
compacted both FE and BE. I still get the same behaviour.

Thanks very much in advance for any suggestions.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[jlepack]

Is dbs defined properly? Are you using "Option Explicit" That's the
only thing that jumps to my mind.

Cheers,
Jason Lepack

Tim Marshall wrote:

For all my apps in A97 and A2003, I write VBA code that constructs SQL
statements for MS Jet. I'm encountering something really odd. I won't
post the SQL, but describe the behaviour to see if anyone has
encountered this.

Consider the following code:

dim strS as string
dim rst as DAO.recordset

strS = "Select blah, blah"

debug.print strS

dbs.openrecordset(strs, dbopensnapshot)

When the code executes, the recordset returns no records when there
should be some.

When I paste the strs from the immediate window into the SQL window of a
querydef, I get no records either.

But when I then open the querydef in the Access query design view and
save it and then run it, I get records!!!!!

The SQL does not change. For simpler SQL (the stuff above is not overly
complicated - it draws data from 4 tables - I have far more complicated
queries in this app and others) this does not happen.

Does anyone have any suggestions?

I've compacted, decompiled (as per David Fenton), compacted, cocmpiled,
compacted both FE and BE. I still get the same behaviour.

Thanks very much in advance for any suggestions.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[David F Cox]

Have you tried pasting the working SQL back into the VBA code to see if that
works now. There may be some very subtle change, perhaps a non-diplyable
character.
I recall seeing similar problems posted before, and I believe there was an
issue with references there.

"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in message
news:el**********@coranto.ucs.mun.ca...

For all my apps in A97 and A2003, I write VBA code that constructs SQL
statements for MS Jet. I'm encountering something really odd. I won't
post the SQL, but describe the behaviour to see if anyone has encountered
this.

Consider the following code:

dim strS as string
dim rst as DAO.recordset

strS = "Select blah, blah"

debug.print strS

dbs.openrecordset(strs, dbopensnapshot)

When the code executes, the recordset returns no records when there should
be some.

When I paste the strs from the immediate window into the SQL window of a
querydef, I get no records either.

But when I then open the querydef in the Access query design view and save
it and then run it, I get records!!!!!

The SQL does not change. For simpler SQL (the stuff above is not overly
complicated - it draws data from 4 tables - I have far more complicated
queries in this app and others) this does not happen.

Does anyone have any suggestions?

I've compacted, decompiled (as per David Fenton), compacted, cocmpiled,
compacted both FE and BE. I still get the same behaviour.

Thanks very much in advance for any suggestions.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[rkc]

Tim Marshall wrote:

For all my apps in A97 and A2003, I write VBA code that constructs SQL
statements for MS Jet. I'm encountering something really odd. I won't
post the SQL, but describe the behaviour to see if anyone has
encountered this.

Consider the following code:

dim strS as string
dim rst as DAO.recordset

strS = "Select blah, blah"

debug.print strS

dbs.openrecordset(strs, dbopensnapshot)

When the code executes, the recordset returns no records when there
should be some.

When I paste the strs from the immediate window into the SQL window of a
querydef, I get no records either.

But when I then open the querydef in the Access query design view and
save it and then run it, I get records!!!!!

The SQL does not change. For simpler SQL (the stuff above is not overly
complicated - it draws data from 4 tables - I have far more complicated
queries in this app and others) this does not happen.

Does anyone have any suggestions?

I've compacted, decompiled (as per David Fenton), compacted, cocmpiled,
compacted both FE and BE. I still get the same behaviour.

Thanks very much in advance for any suggestions.

The first thing I would do is stop hard coding sql strings
into VBA code. Write and test the sql in the query builder
and paste it into a table. Then it's a simple matter of reading
it into a variable when you need it.

[Lyle Fairfield]

Tim Marshall <TI****@PurplePandaChasers.Moertheriumwrote in
news:el**********@coranto.ucs.mun.ca:

For all my apps in A97 and A2003, I write VBA code that constructs SQL
statements for MS Jet. I'm encountering something really odd. I
won't post the SQL, but describe the behaviour to see if anyone has
encountered this.

Consider the following code:

dim strS as string
dim rst as DAO.recordset

strS = "Select blah, blah"

debug.print strS

dbs.openrecordset(strs, dbopensnapshot)

When the code executes, the recordset returns no records when there
should be some.

When I paste the strs from the immediate window into the SQL window of
a querydef, I get no records either.

But when I then open the querydef in the Access query design view and
save it and then run it, I get records!!!!!

The SQL does not change. For simpler SQL (the stuff above is not
overly complicated - it draws data from 4 tables - I have far more
complicated queries in this app and others) this does not happen.

Does anyone have any suggestions?

I've compacted, decompiled (as per David Fenton), compacted,
cocmpiled, compacted both FE and BE. I still get the same behaviour.

Thanks very much in advance for any suggestions.

I think we need to see the SQL.

We do not know what Access does with SQL script that we post into the
Query Design window. It sometimes (often?) modifies our script before it
sends it off to JET for action. We may be able to find the final string
by looking at the SQL window after the query has been saved and run. We
may be able to get an sense of what is happening by examining the
showplan.out file which holds information such as: --- temp query ---

- Inputs to Query -
Table 'MSysRelationships'
- End inputs to Query -

01) Restrict rows of table MSysRelationships
using rushmore
for expression "(szObject=wszTableName) OR
(szReferencedObject=wszTableName)"

but sometimes Access does whatever it likes without revealing anything.

--
Lyle Fairfield

http://www.ffdba.com/toyota/BurlingtonToyotaLease.htm

(just a sad story - read if bored)

[Tim Marshall]

jlepack wrote:

Is dbs defined properly? Are you using "Option Explicit" That's the
only thing that jumps to my mind.

Thanks for taking the time to respond, Jason... yes to both.

I've been using David Fenton's "dblocal" function for a couple of years
now in Oracle and MS Jet Access apps:

http://tinyurl.com/yxoxbk

And it's been a wonderful development tool, working flawlessly. I did
try running my proc the "traditional" way by defining the dbs variable
within the procedure and got the same behaviour.

Option Explicit is present in all modules involved (the one with the
errant procedure and the one that holds David's dblocal function).

I've fixed the problem - see my response to David Cox.

Again, thanks for your suggestions.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

rkc wrote:

The first thing I would do is stop hard coding sql strings
into VBA code. Write and test the sql in the query builder
and paste it into a table. Then it's a simple matter of reading
it into a variable when you need it.

Thanks, this is a reasonable approach, but I don't think it's practical
with most of the apps I deal with? By that I mean I usually construct
SQL based on a large number of criteria specified by the user on one or
more forms.

In the current app from which my question is coming, I have a digital
map with 320 large squares superimposed on it. The app tracks movement
of units which are given orders that are stored in a table. The SQL I'm
having trouble with is trying to nail down what users have their units
in a square, so the SQL has to reflect the orders given, the sides
involved and the square. That's at least 320 statements if two users
with units are involved, I think, and much more when there are more than
2 users and any number of them can have units in a square then the
possibilities increase.

The variables in my function which is causing the problem include the
name of the square involved and a loop has to be run for each user in
the exercise...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

David F Cox wrote:

Have you tried pasting the working SQL back into the VBA code to see if that
works now. There may be some very subtle change, perhaps a non-diplyable
character.
I recall seeing similar problems posted before, and I believe there was an
issue with references there.

References were fine, but your first suggestion worked! I should have
thought of doing this but was too cocky in my own abilities.

Still, this is extremely odd behaviour. Lyle's comments probably have a
lot of bearing here, but as I've said, I've done this sort of thing
using MS Jet as a backend many times and with far more complex query
construction.

I suspect there may be something wrong with my data structure, though I
pride myself on robust design. Perhaps too much pride, eh?

Thanks very much for your suggestion, it's got me going again.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Lyle Fairfield wrote:

I think we need to see the SQL.

I'll post it afterward, just for interest's sake, though David Cox's
suggestion got me running again.

We do not know what Access does with SQL script that we post into the
Query Design window. It sometimes (often?) modifies our script before it
sends it off to JET for action. We may be able to find the final string
by looking at the SQL window after the query has been saved and run.

That's often my approach - creating the query in the design window and
then copying and modifying it into VBA. I do this mainly because I am
not as proficient with remembering how the join system in Jet works,
which is one of the reasons I prefer working with Oracle - joins are in
the where clause and are so much easier to do out of your head.

but sometimes Access does whatever it likes without revealing anything.

I've never encountered this before, but perhaps my problem is a
manifestation of the behaviour you've described. I'm convinced that
I've somehow botched up my structure, but can't see how.

The other possibility is that my application somehow became corrupt in a
way that decompile and compacting didn't take care of. Just to be sure,
I'm going to import all the elements into a new mdb and try my way of
writing the SQL again. As I've mentioned, the way D Cox suggested
worked. I tend to hate the way the Access query builder constructs SQL
with all the redundant table names in front of field names and so many
parenthesis in the where clause and usually get rid of such. However,
writing the SQL the "Access way" seems to have solved my dilemma and
it's the first time I've encountered such a thing.

Thanks for the reply.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Tim Marshall wrote:

Lyle Fairfield wrote:

>I think we need to see the SQL.

I'll post it afterward, just for interest's sake, though David Cox's
suggestion got me running again.

Ooops forgot the SQL. Perhaps it's a bit long and it's OK if you just
say shag it and don't look at it. But I can't see any difference.

Here's what works (gives 2 records):

SELECT
TBL_SIDE.SID_PK,
[SID_NAME] & IIf(IsNull([SID_NAME_2]),"",", " & [SID_NAME_2]) AS Side,
TBL_SIDE.SID_NAME

FROM
TBL_SIDE INNER JOIN (TBL_CONTACTS INNER JOIN
(TBL_CONTACTS_UNITS INNER JOIN TBL_UNITS ON
TBL_CONTACTS_UNITS.CTU_UNT_FK = TBL_UNITS.UNT_PK)
ON TBL_CONTACTS.CON_PK = TBL_CONTACTS_UNITS.CTU_CON_FK) ON
TBL_SIDE.SID_PK = TBL_UNITS.UNT_SID_FK

WHERE
(((TBL_CONTACTS.CON_SQUARE) = "6-18") And
((TBL_SIDE.SID_GAM_FK) = 11) And ((TBL_CONTACTS.CON_GAM_FK) = 11) And
((TBL_CONTACTS_UNITS.CTU_TURN_ENDS) <= 1) And
((TBL_CONTACTS_UNITS.CTU_TURN) >= 1) And
((TBL_CONTACTS_UNITS.CTU_RESULT) Is Null) And
((TBL_CONTACTS_UNITS.CTU_OBS_SQUARE) = "-") And
((TBL_SIDE.SID_PK) <48))
Or
(((TBL_CONTACTS.CON_SQUARE) = "6-18") And
((TBL_SIDE.SID_GAM_FK) = 11) And ((TBL_CONTACTS.CON_GAM_FK) = 11) And
((TBL_CONTACTS_UNITS.CTU_TURN_ENDS) <= 1) And
((TBL_CONTACTS_UNITS.CTU_TURN) >= 1) And
((TBL_CONTACTS_UNITS.CTU_RESULT) Is Null) And
((TBL_SIDE.SID_PK) = 48))

GROUP BY
TBL_SIDE.SID_PK,
[SID_NAME] & IIf(IsNull([SID_NAME_2]),"",", " & [SID_NAME_2]),
TBL_SIDE.SID_NAME

Here's what doesn't work and gives me no records:

SELECT
SID_PK,
[SID_NAME] & IIf(IsNull([SID_NAME_2]),"",", " & [SID_NAME_2]) AS Side,
sid_name

FROM TBL_SIDE INNER JOIN (TBL_CONTACTS INNER JOIN
(TBL_CONTACTS_UNITS INNER JOIN TBL_UNITS ON
TBL_CONTACTS_UNITS.CTU_UNT_FK = TBL_UNITS.UNT_PK)
ON TBL_CONTACTS.CON_PK = TBL_CONTACTS_UNITS.CTU_CON_FK) ON
TBL_SIDE.SID_PK = TBL_UNITS.UNT_SID_FK

WHERE
(CON_SQUARE = "6-18" And
SID_GAM_FK = 11 And
con_GAM_FK = 11 And
CTU_TURN_ENDS <= 1 And
CTU_TURN >= 1 And
ctu_result = Null and
CTU_OBS_SQUARE = "-" and sid_pk <48)
or
(CON_SQUARE = "6-18" And
SID_GAM_FK = 11 And
con_GAM_FK = 11 And
CTU_TURN_ENDS <= 1 And
CTU_TURN >= 1 And
ctu_result = Null and
sid_pk = 48)

GROUP BY
SID_PK,
[SID_NAME] & IIf(IsNull([SID_NAME_2]),"",", " & [SID_NAME_2]),
sid_name

Curiously enough the second statement is somehow "transmogrified" (as in
Calvin and Hobbes) into the first statement when it is opened in
design view and saved. And then it works.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tom van Stiphout]

On Sun, 10 Dec 2006 18:16:05 -0330, Tim Marshall
<TI****@PurplePandaChasers.Moertheriumwrote:

"= Null" is a bad idea. "Is Null" is a good one.
-Tom.

>Tim Marshall wrote:

>Lyle Fairfield wrote:

>>I think we need to see the SQL.

I'll post it afterward, just for interest's sake, though David Cox's
suggestion got me running again.

Ooops forgot the SQL. Perhaps it's a bit long and it's OK if you just
say shag it and don't look at it. But I can't see any difference.

Here's what works (gives 2 records):

SELECT
TBL_SIDE.SID_PK,
[SID_NAME] & IIf(IsNull([SID_NAME_2]),"",", " & [SID_NAME_2]) AS Side,
TBL_SIDE.SID_NAME

FROM
TBL_SIDE INNER JOIN (TBL_CONTACTS INNER JOIN
(TBL_CONTACTS_UNITS INNER JOIN TBL_UNITS ON
TBL_CONTACTS_UNITS.CTU_UNT_FK = TBL_UNITS.UNT_PK)
ON TBL_CONTACTS.CON_PK = TBL_CONTACTS_UNITS.CTU_CON_FK) ON
TBL_SIDE.SID_PK = TBL_UNITS.UNT_SID_FK

WHERE
(((TBL_CONTACTS.CON_SQUARE) = "6-18") And
((TBL_SIDE.SID_GAM_FK) = 11) And ((TBL_CONTACTS.CON_GAM_FK) = 11) And
((TBL_CONTACTS_UNITS.CTU_TURN_ENDS) <= 1) And
((TBL_CONTACTS_UNITS.CTU_TURN) >= 1) And
((TBL_CONTACTS_UNITS.CTU_RESULT) Is Null) And
((TBL_CONTACTS_UNITS.CTU_OBS_SQUARE) = "-") And
((TBL_SIDE.SID_PK) <48))
Or
(((TBL_CONTACTS.CON_SQUARE) = "6-18") And
((TBL_SIDE.SID_GAM_FK) = 11) And ((TBL_CONTACTS.CON_GAM_FK) = 11) And
((TBL_CONTACTS_UNITS.CTU_TURN_ENDS) <= 1) And
((TBL_CONTACTS_UNITS.CTU_TURN) >= 1) And
((TBL_CONTACTS_UNITS.CTU_RESULT) Is Null) And
((TBL_SIDE.SID_PK) = 48))

GROUP BY
TBL_SIDE.SID_PK,
[SID_NAME] & IIf(IsNull([SID_NAME_2]),"",", " & [SID_NAME_2]),
TBL_SIDE.SID_NAME

Here's what doesn't work and gives me no records:

SELECT
SID_PK,
[SID_NAME] & IIf(IsNull([SID_NAME_2]),"",", " & [SID_NAME_2]) AS Side,
sid_name

FROM TBL_SIDE INNER JOIN (TBL_CONTACTS INNER JOIN
(TBL_CONTACTS_UNITS INNER JOIN TBL_UNITS ON
TBL_CONTACTS_UNITS.CTU_UNT_FK = TBL_UNITS.UNT_PK)
ON TBL_CONTACTS.CON_PK = TBL_CONTACTS_UNITS.CTU_CON_FK) ON
TBL_SIDE.SID_PK = TBL_UNITS.UNT_SID_FK

WHERE
(CON_SQUARE = "6-18" And
SID_GAM_FK = 11 And
con_GAM_FK = 11 And
CTU_TURN_ENDS <= 1 And
CTU_TURN >= 1 And
ctu_result = Null and
CTU_OBS_SQUARE = "-" and sid_pk <48)
or
(CON_SQUARE = "6-18" And
SID_GAM_FK = 11 And
con_GAM_FK = 11 And
CTU_TURN_ENDS <= 1 And
CTU_TURN >= 1 And
ctu_result = Null and
sid_pk = 48)

GROUP BY
SID_PK,
[SID_NAME] & IIf(IsNull([SID_NAME_2]),"",", " & [SID_NAME_2]),
sid_name

Curiously enough the second statement is somehow "transmogrified" (as in
Calvin and Hobbes) into the first statement when it is opened in
design view and saved. And then it works.

[Matthias Klaey]

Tom van Stiphout <no*************@cox.netwrote:

>On Sun, 10 Dec 2006 18:16:05 -0330, Tim Marshall
<TI****@PurplePandaChasers.Moertheriumwrote:

"= Null" is a bad idea. "Is Null" is a good one.
-Tom.

Tom

I think you found the true reason for the "odd" behaviour that Tim is
observing. Plus, there is a subtle and little known difference between
Access 97 and later versions:

In Access 97,

SELECT * FROM tblTest WHERE X = Null

*does* return records (those where the field X contains a Null),
but from Access 2000 on this never returns any records.

Access 97 would "silently" interpret "X = Null" as "X Is Null",
whereas from Access 2000 on this is more in line with the VBA
behaviour of logical comparisons with Null. Null compared to anything
never is True or False, it always is Null.

Greetings
Matthias Kläy
--
www.kcc.ch

[Lyle Fairfield]

Matthias Klaey <mp**@hotmail.comwrote in
news:rn********************************@4ax.com:

Tom van Stiphout <no*************@cox.netwrote:

>>On Sun, 10 Dec 2006 18:16:05 -0330, Tim Marshall
<TI****@PurplePandaChasers.Moertheriumwrote:

"= Null" is a bad idea. "Is Null" is a good one.
-Tom.

Tom

I think you found the true reason for the "odd" behaviour that Tim is
observing. Plus, there is a subtle and little known difference between
Access 97 and later versions:

In Access 97,

SELECT * FROM tblTest WHERE X = Null

*does* return records (those where the field X contains a Null),
but from Access 2000 on this never returns any records.

Access 97 would "silently" interpret "X = Null" as "X Is Null",
whereas from Access 2000 on this is more in line with the VBA
behaviour of logical comparisons with Null. Null compared to anything
never is True or False, it always is Null.

Greetings
Matthias Kläy

Sometimes one forgets when cutting and pasting from applications which do
accept comparison operators with null, but I suppose "check handling of
nulls" is a homily we should put into the back of our minds (or even into
our programming notes) in the "things to check when my SQL doesn't work
properly" drawer.

Perhaps the differences you note with respect to Access 97 and later
versions are differences between JET 3.5 and JET 4.0, but given Access's
secret processes (you call it silent interpretation) one may never know.

--
Lyle Fairfield

http://www.ffdba.com/toyota/BurlingtonToyotaLease.htm

(just a sad story - read if bored)

[Tim Marshall]

Tom van Stiphout wrote:

"= Null" is a bad idea. "Is Null" is a good one.
-Tom.

Good lord, that is in fact what the problem was.

Thanks so much, I stared at the two versions for a long time and didn't
notice that difference at all.

That's the danger of thinking one is infallible!
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me