Please help me. Excel VBA is throwing this error: Multiple-step OLE DB Operation generated errors
on the line : cmd.Parameters("[days]") = ActiveWorkbook.Sheets("MABI_Extracts").Range("B18").Value
I'm trying to run a query in access named "AGBA_Conversion" with an integer parameter. How do I resolve? thanks in advance :)
Sub RECT_MBTCLeads2_Extracts()
Dim cmd As New ADODB.Command, rs As ADODB.Recordset
Dim sht As Worksheet
Dim b As String
Dim d As Long
Dim a As String
Set sht = ActiveWorkbook.Sheets("AGBA_Conversion")
sht.Range("B1").ClearContents
cmd.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\nemberga\OneDrive - AXA\Documents\Automation\MBTC Conversion.accdb"
cmd.CommandType = adCmdText
cmd.CommandText = "AGBA_Conversion" '<<<--here is where you write your query sql
cmd.Parameters("[days]") = ActiveWorkbook.Sheets("MABI_Extracts").Range("B18").Value
Options = adCmdTable
Set rs = cmd.Execute
sht.Range("A1").CopyFromRecordset rs '--bulk copy to the Excel sheet
rs.Close
cmd.ActiveConnection.Close
MsgBox "All data were successfully retrieved from the queries!", vbInformation, "Done"
End Sub
Parameters is a collection of Parameter. You need first to create the object using CreateParameter, then to add it to the collection using Append.
Replace
cmd.Parameters("[days]") = ActiveWorkbook.Sheets("MABI_Extracts").Range("B18").Value
By
Dim p
Set p = cmd.CreateParameter("days", adInteger, adParamInput)
p.Value = ActiveWorkbook.Sheets("MABI_Extracts").Range("B18").Value
cmd.Parameters.Append p