要访问的Excel VBA查询失败

I am trying to query an access file from excel using VBA and it is giving an error on the line rs.Open queryStatement, conn, adOpenStatic, adLockOptimistic. The error is "Run-Time error '-2147217904 (80040e10)': No Value given for one or more required parameters."

我试图使用VBA从excel查询访问文件，它在rs.Open queryStatement，conn，adOpenStatic，adLockOptimistic这一行上给出了错误。错误是“运行时错误”-2147217904（80040e10）'：没有给出一个或多个必需参数的值。“

Function queryAccess()
'inputs: filterID, desired output

Dim toSheet As Worksheet
Set toSheet = ThisWorkbook.Sheets("Sheet3")
Dim filterID As String
filterID = "CH0002"

Dim conn As ADODB.Connection
Dim rs As ADODB.recordSet
Dim connStr As String
Dim queryStatement As String
Dim cmd As ADODB.Command

connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\rtpwfil03\etu2\cat\projects\asbestos\STREAMS TO101\Testing\TO101 Testing Data.mdb;Persist Security Info=False;"
'conn.Provider=""Microsoft.Jet.OLEDB.4.0"


Set conn = New ADODB.Connection
Set rs = New ADODB.recordSet
Set cmd = New ADODB.Command

queryStatement = "SELECT Filters.NominalLoading FROM Filters WHERE Filters.FilterID=" & filterID & ";"

conn.Open connStr
Debug.Print connStr
Debug.Print queryStatement
rs.Open queryStatement, conn, adOpenStatic, adLockOptimistic



toSheet.Range(toSheet.Cells(1, 1)).CopyFromRecordset rs

conn.Close
rs.Close


End Function

when I do a debug.print on my connStr and queryStatement I get this, respectively: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\rtpwfil03\etu2\cat\projects\asbestos\STREAMS TO101\Testing\TO101 Testing Data.mdb;Persist Security Info=False;

当我在我的connStr和queryStatement上执行debug.print时，我得到了这个：Provider = Microsoft.Jet.OLEDB.4.0; Data Source = \\ rtpwfil03 \ etu2 \ cat \ projects \ asbestos \ STREAMS TO101 \ Testing \ TO101 Testing Data.mdb; Persist Security Info = False;

SELECT Filters.NominalLoading FROM Filters WHERE Filters.FilterID=CH0002;

SELECT Filters.NominalLoading FROM Filters WHERE Filters.FilterID = CH0002;

I got the connection string from http://www.connectionstrings.com/access-2007 "standard security"

我从http://www.connectionstrings.com/access-2007获得了连接字符串“standard security”

Any ideas on how to fix this error?

有关如何修复此错误的任何想法？

1 个解决方案

#1

You need single quotes around the CH002 string in your query. Change:

在查询中，您需要在CH002字符串周围使用单引号。更改：

queryStatement = "SELECT Filters.NominalLoading FROM Filters WHERE Filters.FilterID=" & filterID & ";"

to

至

queryStatement = "SELECT Filters.NominalLoading FROM Filters WHERE Filters.FilterID='" & filterID & "';"