Excel VBA访问查询失败 [英] Excel VBA query to access is failing

问题描述

我正在尝试使用VBA查询Excel中的访问文件，并且在 rs.Open queryStatement，conn，adOpenStatic，adLockOptimistic 之间发出错误。错误是运行时错误-2147217904（80040e10）'：没有给出一个或多个所需参数的值。

 函数queryAccess（）
'输入：filterID，所需的输出
 
 Dim toSheet As Worksheet 
设置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\石棉\STREAMS TO101\Testing\TO101测试Data.mdb; Persist Security Info = False; 
'conn.Provider =Microsoft.Jet.OLEDB.4.0
 
 
设置conn =新建ADODB.Connection 
设置rs =新建ADODB.recordSet 
设置cmd =新建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 
 
 
结束函数$ b $当我在我的connStr和queryStatement上做一个debug.print时，分别得到这个：
 <
 
 
 
 c $ c> 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 Filte rs.FilterID = CH0002;  
 
 
 
我从 http://www.connectionstrings.com/access-2007 标准安全
 
 
 
任何有关如何解决这个错误？
解决方案
您需要查询CH002字符串上的单引号。更改：
  queryStatement =SELECT Filters.NominalLoading FROM Filters WHERE Filters.FilterID =&过滤器ID& ; 
  
至
  queryStatement =SELECT Filters.NominalLoading FROM Filters WHERE Filters.FilterID ='&过滤器ID& ; 
  
 
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."
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;

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

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

Any ideas on how to fix this error?
解决方案
You need single quotes around the CH002 string in your query.  Change:
queryStatement = "SELECT Filters.NominalLoading FROM Filters WHERE Filters.FilterID=" & filterID & ";"
to 
queryStatement = "SELECT Filters.NominalLoading FROM Filters WHERE Filters.FilterID='" & filterID & "';"


                        
这篇关于Excel VBA访问查询失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！