VBA生成SQL的奇怪行为 [英] Odd Behaviour with VBA Generated SQL
问题描述
对于A97和A2003中的所有应用程序，我编写VBA代码，为MS Jet构建SQL
语句。我遇到了一些非常奇怪的事情。我不会发布SQL，但是要描述行为，看看是否有人遇到过这个问题。
考虑以下代码：
dim strS as string
dim rst as DAO.recordset
strS ="选择等等，等等。
debug.print strS
dbs.openrecordset（strs，dbopensnapshot）
代码执行时，记录集返回时没有记录
应该是一些。
当我将strs从即时窗口粘贴到一个
querydef的SQL窗口，我也没有记录。
但是当我在Access查询设计视图中打开querydef并且
保存然后运行它，我得到记录!!!!!
SQL不会改变。对于更简单的SQL（上面的东西并不过分...... / b $ b复杂 - 它从4个表中抽取数据 - 我在这个应用程序和其他人中有更复杂的
查询）这不是发生。
有没有人有任何建议？
我已经压缩，反编译（按照David Fenton），压缩，cocmpiled，
压缩FE和BE。我仍然有同样的行为。
非常感谢您提出任何建议。
-
Tim http://www.ucs.mun.ca/~tmarshal/
^ o<
/＃）Burp-beep，burp-beep，burp-beep？ - Quaker Jake
/ ^^Whatcha doin？ - 同上TIM-MAY !! - 我
dbs是否正确定义？您使用的是Option Explicit吗？这只是我想到的唯一的事情。
干杯，
Jason Lepack
Tim Marshall写道：
对于A97和A2003中的所有应用程序，我编写构造SQL的VBA代码
MS Jet的声明。我遇到了一些非常奇怪的事情。我不会发布SQL，但是要描述行为，看看是否有人遇到过这个问题。
考虑以下代码：
dim strS as string
dim rst as DAO.recordset
strS ="选择等等，等等。
debug.print strS
dbs.openrecordset（strs，dbopensnapshot）
代码执行时，记录集返回时没有记录
应该是一些。
当我将strs从即时窗口粘贴到一个
querydef的SQL窗口，我也没有记录。
但是当我在Access查询设计视图中打开querydef并且
保存然后运行它，我得到记录!!!!!
SQL不会改变。对于更简单的SQL（上面的东西并不过分...... / b $ b复杂 - 它从4个表中抽取数据 - 我在这个应用程序和其他人中有更复杂的
查询）这不是发生。
有没有人有任何建议？
我已经压缩，反编译（按照David Fenton），压缩，cocmpiled，
压缩FE和BE。我仍然有同样的行为。
非常感谢您提出任何建议。
-
Tim http://www.ucs.mun.ca/~tmarshal/
^ o<
/＃）Burp-beep，burp-beep，burp-beep？ - Quaker Jake
/ ^^Whatcha doin？ - 同上TIM-MAY !! - Me
您是否尝试将工作SQL粘贴回VBA代码以查看
现在是否有效。可能会有一些非常微妙的变化，也许是一个不可沾染的
角色。
我记得以前发过类似的问题，我相信有一个
问题引用那里。
Tim Marshall < TI **** @ PurplePandaChasers.Moertheriumwrote in message
news：el ********** @ coranto.ucs.mun.ca ...
< blockquote class =post_quotes>
对于A97和A2003中的所有应用程序，我编写VBA代码，为MS Jet构造SQL
语句。我遇到了一些非常奇怪的事情。我不会发布SQL，但是要描述一下行为，看看是否有人遇到过
这个。
考虑以下代码：
dim strS as string
dim rst as DAO.recordset
strS ="选择等等，等等。
debug.print strS
dbs.openrecordset（strs，dbopensnapshot）
当代码执行时，记录集不会返回任何记录，而
是一些。
当我将strs从即时窗口粘贴到一个
querydef的SQL窗口，我也没有记录。
但是当我在Access查询设计视图中打开querydef并保存
然后运行它，我得到记录!!!!!
SQL不会改变。对于更简单的SQL（上面的东西并不过分...... / b $ b复杂 - 它从4个表中抽取数据 - 我在这个应用程序和其他人中有更复杂的
查询）这不是发生。
有没有人有任何建议？
我已经压缩，反编译（按照David Fenton），压缩，cocmpiled，
压缩FE和BE。我仍然有同样的行为。
非常感谢您提出任何建议。
-
Tim http://www.ucs.mun.ca/~tmarshal/
^ o<
/＃）Burp-beep，burp-beep，burp-beep？ - Quaker Jake
/ ^^Whatcha doin？ - 同上TIM-MAY !! - 我
Tim Marshall写道：
我所有的应用程序A97和A2003，我编写VBA代码，为MS Jet构造SQL
语句。我遇到了一些非常奇怪的事情。我不会发布SQL，但是要描述行为，看看是否有人遇到过这个问题。
考虑以下代码：
dim strS as string
dim rst as DAO.recordset
strS ="选择等等，等等。
debug.print strS
dbs.openrecordset（strs，dbopensnapshot）
代码执行时，记录集返回时没有记录
应该是一些。
当我将strs从即时窗口粘贴到一个
querydef的SQL窗口，我也没有记录。
但是当我在Access查询设计视图中打开querydef并且
保存然后运行它，我得到记录!!!!!
SQL不会改变。对于更简单的SQL（上面的东西并不过分...... / b $ b复杂 - 它从4个表中抽取数据 - 我在这个应用程序和其他人中有更复杂的
查询）这不是发生。
有没有人有任何建议？
我已经压缩，反编译（按照David Fenton），压缩，cocmpiled，
压缩FE和BE。我仍然有同样的行为。
非常感谢您提出任何建议。
我要做的第一件事是停止硬编码sql字符串
到VBA代码中。在查询生成器中编写并测试sql
并将其粘贴到表中。然后，当你需要它时，将它变成一个变量是一个简单的问题。
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
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
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
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.
这篇关于VBA生成SQL的奇怪行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
