如何使用 VBA 动态 SQL SELECT 语句调用 MS Access 参数查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21713716/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to call MS Access Parameter queries using VBA dynamic SQL SELECT statements
提问by user3299120
I've searched MSDN, StackOverflow, SQLServer Central and too many sites to mention. I've been trying for a couple of days to find a way to execute parameter queries in MS Access using dynamic SQL SELECT statements in Excel VBA code. The system I'm using works extremely well with SQL Server TVFs, but I have to convert it to Access due to losing server support. The VBA code starts by looping through an ‘input' sheet in Excel picking up parameter values and function/query names that is used to build dynamic SQL SELECT statements. Here is the code that builds the connection and calls the Access Query for just one of the queries (there are 20) that requires only 1 input paramter:
我搜索过 MSDN、StackOverflow、SQLServer Central 和太多无法提及的站点。几天来，我一直在尝试寻找一种在 Excel VBA 代码中使用动态 SQL SELECT 语句在 MS Access 中执行参数查询的方法。我使用的系统与 SQL Server TVF 配合得非常好，但由于失去服务器支持，我必须将其转换为 Access。VBA 代码首先循环遍历 Excel 中的“输入”表，选取用于构建动态 SQL SELECT 语句的参数值和函数/查询名称。这是构建连接并为仅需要 1 个输入参数的查询之一（有 20 个）调用访问查询的代码：
Dim strSQL
' set up our connection
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\PathToDB Tables 2013-12-13.accdb;"
Set conn = CreateObject("ADODB.Connection")
conn.Open strConnection
' open the view and create the report
Set rs = CreateObject("ADODB.recordset")
Application.ODBCTimeout = 0
conn.CommandTimeout = 0
strSQL = "select * FROM "
strSQL = strSQL & strFunction
strSQL = strSQL & " (" & strParameters & ");"
'strSQL = strSQL & strOrderBy
rs.Open strSQL, conn
The value in strSQL at this point is SELECT * FROM Report_1_2_StaffAdds (#12/31/2013#);
此时 strSQL 中的值为 SELECT * FROM Report_1_2_StaffAdds (#12/31/2013#);
The error occurs in the line rs.Open strSQL, conn With the error message ‘Syntax error in FROM clause.'
错误发生在 rs.Open strSQL, conn 行中，错误消息为“FROM 子句中的语法错误”。
Here is the MS Access Query: (Query name is Report_1_2_StaffAdds)
这是 MS Access 查询：（查询名称是 Report_1_2_StaffAdds）
PARAMETERS [previous_month] DateTime;
SELECT [1_2_StaffAddsPart1].Unit, [1_2_StaffAddsPart1].Role, [1_2_StaffAddsPart1].Start_Date, [1_2_StaffAddsPart2].First_Worked, [1_2_StaffAddsPart2].Last_Worked, [1_2_StaffAddsPart1].Emp_Name, [1_2_StaffAddsPart1].Emp_Id, [1_2_StaffAddsPart2].Hours_to_Date
FROM 1_2_StaffAddsPart1 INNER JOIN 1_2_StaffAddsPart2 ON [1_2_StaffAddsPart1].Emp_Id = [1_2_StaffAddsPart2].Emp_Id;
PARAMETERS [previous_month] DateTime;
SELECT [1_2_StaffAddsPart1].Unit, [1_2_StaffAddsPart1].Role, [1_2_StaffAddsPart1].Start_Date, [1_2_StaffAddsPart2].First_Worked, [1_2_StaffAddsPart2].Last_Worked, [1_2_StaffAddsPart1].Emp_Name, [1_2_StaffAddsPart1].Emp_Id, [1_2_StaffAddsPart2].Hours_to_Date
FROM 1_2_StaffAddsPart1 INNER JOIN 1_2_StaffAddsPart2 ON [1_2_StaffAddsPart1].Emp_Id = [1_2_StaffAddsPart2].Emp_Id;
Any help will be much appreciated. I believe if I can get an answer to this, I can revise it to include up to 3 input parameters, depending on which Access query is being executed at a given time.
任何帮助都感激不尽。我相信如果我能得到答案，我可以修改它以包含最多 3 个输入参数，具体取决于在给定时间执行的 Access 查询。
回答by HansUp
It looks to me like you're attempting to include the parameter value in the string which contains your SELECTstatement. However, I'm not sure whether I understand what you're trying to do, so I'll offer you a simple tested parameter query which opens a recordset from an ADODB.Commandobject.
在我看来，您正试图在包含您的SELECT语句的字符串中包含参数值。但是，我不确定我是否理解您要执行的操作，因此我将为您提供一个简单的经过测试的参数查询，该查询从ADODB.Command对象打开记录集。
This is the Immediate window output from the code below:
这是以下代码的立即窗口输出：
PARAMETERS which_date DateTime;
SELECT * FROM tblFoo
WHERE datetime_field = [which_date];
id datetime_field
27 2/11/2014 10:16:58 AM
Code:
代码：
Dim cmd As Object ' ADODB.Command
Dim conn As Object ' ADODB.Connection
Dim rs As Object ' ADODB.Recordset
Dim strConnection As String
Dim strSql As String
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\share\Access\database1.mdb;"
Set conn = CreateObject("ADODB.Connection")
conn.Open strConnection
strSql = "PARAMETERS which_date DateTime;" & vbCrLf & _
"SELECT * FROM tblFoo" & vbCrLf & _
"WHERE datetime_field = [which_date];"
Debug.Print strSql
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = strSql
Set rs = cmd.Execute(, CDate("2/11/2014 10:16:58 AM"))
With rs
If Not (.BOF And .EOF) Then
Debug.Print "id", "datetime_field"
Debug.Print !id, !datetime_field
End If
.Close
End With
