VBA：使用ADODB参数

问题描述：

我想用ADODB运行参数化的SQL查询，但我得到错误消息“必须声明标量变量@Id”。VBA：使用ADODB参数

我是VBA的完整noob，我已经尝试阅读它是如何完成的以及遵循一个示例，但无济于事。

有没有人有我的代码可能是错误的想法？

Public Function IndataTest() 


Dim conn As ADODB.Connection 
Dim ConnString As String 
ConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=DB;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=B5037093;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=DB" 



Dim sqlstr As String Dim rs As Object Dim cmd As Object Dim ParamId As Object Dim Id As String 

Id = 1084924 

sqlstr = sqlstr & "use DB " sqlstr = sqlstr & "Select * " & vbCrLf sqlstr = sqlstr & "From tblA" & vbCrLf sqlstr = sqlstr & "where fldA = @Id" 


Set conn = New ADODB.Connection conn.Open ConnString 

Set cmd = CreateObject("ADODB.Command") cmd.ActiveConnection = conn cmd.CommandText = sqlstr 

Set ParamId = cmd.CreateParameter("@Id", 129, 1, 52, Id) cmd.Parameters.Append ParamId 

Set rs = CreateObject("ADODB.Recordset") rs.Open cmd 


If Not rs.EOF Then 
    ' Transfer result. 
    Sheets("Valuta").Cells(5, 5).CopyFromRecordset rs ' Close the recordset 
    rs.Close Else 
    MsgBox "Error: No records returned.", vbCritical End If 

' Clean up If CBool(conn.State And adStateOpen) Then conn.Close Set conn = Nothing Set rs = Nothing 

End Function

答

不要使用参数，建立自己的价值到您的字符串

Public Function IndataTest() 


Dim conn As ADODB.Connection 
Dim ConnString As String 
ConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=DB;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=B5037093;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=DB" 
    Dim sqlstr As String 
    Dim rs As new adodb.recordset 
    Set conn = New ADODB.Connection 
    conn.Open ConnString 

    sqlstr = "use DB " 
    sqlstr = sqlstr & "Select * " & vbCrLf 
    sqlstr = sqlstr & "From tblA" & vbCrLf 
    sqlstr = sqlstr & "where fldA = " & 1084924 
    set rs = conn.execute(sqlstr)

等

VBA：使用ADODB参数

相关推荐