IIF（）ADO上的逻辑故障 [英] IIF() logic failure over ADO

问题描述

我在尝试从Access数据库中提取数据以插入带有VBA的excel电子表格时遇到问题。


它一切正常，分开从嵌套的IFF（）语句，当我在Access中手动运行它时工作正常，但是当我尝试通过ADO从VBA运行它时，它评估所有表达式都是假的（不是这种情况）。


我尝试使用ADOX从VBA创建查询以确保语法正确，但是再一次，它在ADO上进行了错误的评估，并且从Access的GUI运行良好。有没有人听过/遇到过这个问题，或者有关于我怎么解决这个问题的提示？


提前致谢。


版本：

MS Access 2003（11.6566.8132）SP2

MS VBA：零售6.4.9972

ADO / ADOX v2.8

I''m having a problem when trying to pull data out of an Access database to plug into an excel spreadsheet with VBA.

It''s all working fine, apart from a nested IFF() statement, which is working fine when I run it manually in Access, but when I try to run it from VBA over ADO, it evaluates as if all the expressions are false (not the case).

I tried creating the query from VBA using ADOX to make sure the syntax was correct, but once again, it evaluates incorrectly over ADO, and is fine run from Access''s GUI. Has anyone heard of/encountered this problem before, or have tips on how else I might tackle this problem?

Thanks in advance.

Versions:
MS Access 2003 (11.6566.8132) SP2
MS VBA: Retail 6.4.9972
ADO/ADOX v2.8

推荐答案

道歉，在我发布之后找到http://www.thescripts.com/forum/thread192015.html。

我引用

" IIF是一个VBA函数，这意味着当这个查询在

中运行时，它会通过表达式服务来评估

函数，当通过DAO运行时不会发生这种情况。


如果对于ADO也是如此，那可能会解释它。那么除了重写我的SQL以不包括IFF（）之外，除了之外没有其他解决方案吗？这是相当令人沮丧的，有点反直觉......：P

Apologies, found http://www.thescripts.com/forum/thread192015.html just after I posted.
And I quote
"IIF is a VBA function which means that when this query is run in
Access it goes through th expression service in order to evaluate the
function, this doesn''t happen when run through DAO."

If this is also true for ADO, that might explain it. So, is there no other solution other than rewriting my SQL to not include IFF()? That''s rather frustrating, and a little counter-intuitive... :P

道歉，发现http：// www。我发布之后的thescripts.com/forum/thread192015.html。

我引用

" IIF是一个VBA函数，这意味着当这个查询在

访问它通过表达式服务来评估

函数，这在通过DAO运行时不会发生。


如果ADO也是如此，那可能会解释它。那么除了重写我的SQL以不包括IFF（）之外，除了之外没有其他解决方案吗？这是相当令人沮丧的，有点反直觉......：P

Apologies, found http://www.thescripts.com/forum/thread192015.html just after I posted.
And I quote
"IIF is a VBA function which means that when this query is run in
Access it goes through th expression service in order to evaluate the
function, this doesn''t happen when run through DAO."

If this is also true for ADO, that might explain it. So, is there no other solution other than rewriting my SQL to not include IFF()? That''s rather frustrating, and a little counter-intuitive... :P

将IIF（）替换为标准 If ... Else。 ..End If 在公共函数中构造，然后使用SQL语句中的函数返回正确的结果。

Replace IIF() with the standard If...Else...End If Construct within a Public Function, then use the Function within the SQL Statement to return the proper result.

谢谢你的回复。


我创建了一个MyFunctions模块，其中包含一个包含相关逻辑的公共函数。当从Access GUI直接调用时也可以正常工作，没有问题。


但是，再一次尝试从VBA调用此函数时会出现问题。错误消息在espression中声明未定义的函数'Blah''。

这是在VBA中构造SQL查询时。

我也尝试过简单选择...进入复制主表的查询，因为它是在VBA中编写引起问题的SQL ...


SELECT Blah（Table1.Fieldname1）AS Fieldname2， * INTO CallTable2

来自CallTable;


但是我又得到了同样的未定义函数''blah''错误信息。


我认为这是因为JET引擎不知道自定义函数。有谁知道是否有插入这个定义的方法？


这样一个令人沮丧的怪癖，我开始相信我必须抓住记录集，转储它进入Excel并处理那里的数据，丑陋而缓慢，就像那样......

Thankyou for your reply.

I''ve created a MyFunctions module containing a public function containing the relevant logic. This also works fine when called directly from the Access GUI, no problems there.

However, once again, a problem crops up when trying to call this function over ADO from VBA. The error message states "Undefined function ''Blah'' in espression."
This is when constructing the SQL query in VBA.
I also tried making a simple Select... Into query that copies the primary table, incase it was writing the SQL in VBA that was causing the problem...

SELECT Blah(Table1.Fieldname1) AS Fieldname2, * INTO CallTable2
FROM CallTable;

But once again, I get the same undefined function ''blah'' error message.

I presume this is because the JET engine is unaware of the custom function. Does anyone know if there''s a way of inserting this definition?

Such a frustrating quirk, I''m beginning to believe I''ll have to grab the recordset, dump it into Excel and process the data there, ugly and slow as that''d be...

这篇关于IIF（）ADO上的逻辑故障的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！