CODEWITHSUNDEEP
excelvbams-access
Nate Agregme
Nate Agregme

Reputation: 93

Multiple-step OLE DB Operation generated errors. Check each OLE DB Status Value

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

Upvotes: 0

Views: 1505

Answers (1)

Vincent G
Vincent G

Reputation: 3188

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

See https://learn.microsoft.com/fr-fr/sql/ado/reference/ado-api/append-and-createparameter-methods-example-vb?view=sql-server-ver15

Upvotes: 1

Related Questions