通过VBA运行SQL [英] Running SQL through VBA

问题描述

我正在尝试运行以下查询:

I am trying to run the following query:

SELECT Project.* FROM Project WHERE (((Projects.[End Date (PPL)])>=DateAdd('m',-6,Date())));

它在Access(2007)中运行良好，但是当我将其移至VBA时:

It runs fine in Access (2007) however when i move it over to VBA:

strQry = "SELECT Project.* FROM Project WHERE (((Projects.[End Date (PPL)])>=DateAdd('m',-6,Date())));"

我收到一条错误消息，提示没有为一个或多个必需参数提供值"

I get an error message saying 'No value given for one or more required parameters'

我可以使用以下代码来运行它，但是它没有提供正确的信息:

I can get it running with the following code but it doesn't provide the correct information:

strQry = "SELECT Project.* FROM Project WHERE Project.[End Date (PPL)] >= DateAdd('m',-6,Date());"

任何帮助都会很棒.

推荐答案

您的问题的明确答案在StackOverflow中:

The definitive answer to your question is here, in StackOverflow:

Excel ADODB查询中的VBA函数

简单的答案是:

您正在使用一种称为Jet-SQL的SQL方言，大多数人从Microsoft Access(一种使VBA函数可用于SQL引擎的数据库应用程序)中调用它.

You're using a dialect of SQL called Jet-SQL, and most people call it from Microsoft Access, a database application which makes VBA functions available to the SQL engine.

如果从任何其他平台(如ODBC或ADODB包装器)运行Jet SQL，则VBA功能不可用:您所拥有的只是Jet-SQL本机函数.

If you run Jet SQL from any other platform - like an ODBC or ADODB wrapper -the VBA functions aren't available: all you have is the Jet-SQL native functions.

这里有一个本机Jet-SQL函数的列表:它几乎完整，您可以看到它很短:

There's a list of the native Jet-SQL functions here: it's almost complete, and you can see that it's rather short:

MS Access:功能-按类别列出

...而且它不包括NZ()

...And it doesn't include NZ()

这些功能看起来像熟悉的VBA有点令人困惑，并且讨厌列出它们的网页被标记为"MS-Access"，而不是Jet SQL.

It's somewhat confusing that these functions look like the familiar VBA - and annoying that the web page listing them is labelled 'MS-Access' rather than Jet SQL.

该列表中有一个有趣的项目:内联'If'函数 IIF()，您可以将其用作NZ的替代品:

There is an interesting item in that list: the inline 'If' function, IIF(), and you can use it as a substitute for NZ:

 选择IIF(table1.[RIC]不为空，表1. [RIC]，table2.[ISIN] +'，CODE:ISIN')AS [RetrievalCode]，table3.[Value]，table3.[DateStamp]从 *您的其余查询* 

希望有帮助:我怀疑这样做不会，因为您可能正在调用命名查询(或其中包含命名子查询的SQL)，并且除非整个查询都无效，否则这些查询中的所有非本机函数调用均无效从MS-Access内调用该事物.
Hope that helps: I suspect that it won't, because you may be calling a named query (or SQL with a named subquery in it), and all the non-native function calls in those queries are invalid unless the whole thing is called from within MS-Access. 
当然，您可以进行模式"查询并提取SQL，将其解析出来，然后用本机Jet-SQL函数替换所有VBA.但是，当您不在预期的环境(Microsoft Access中的Query-By-Example可视查询生成器)之外时，Jet-SQL的语法就会变得晦涩难懂，因此建议可能比其他方式花费更多的时间和精力.
You can, of course, make a 'schema' query and extract the SQL, parse it out, and replace all the VBA with native Jet-SQL functions. But the syntax for Jet-SQL gets arcane when you're outside it's intended environment - the Query-By-Example visual query builder inside Microsoft Access - so that suggestion may be more time and trouble than doing it some other way.

                        
这篇关于通过VBA运行SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！