Excel VBA Use ADO Command Object to Call Stored Query in Access

Question

I'm trying to call a stored query in my Access database that requires one parameter (criteria). When I try this I get runtime error '3265': "Item cannot be found in the collection corresponding to the requested name or ordinal" when it reaches the line cmd(1)=userName.

The msgbox line above that one shows me that there is a parameter count of 0, but there should be a count of 1.

Function getUserLevelCmd() As String
 Dim conn As ADODB.Connection
 Dim rs As ADODB.Recordset
 Dim cmd As New ADODB.Command

 Set conn = makeConnection()

 cmd.CommandText = "accessLevelByUN"
 cmd.CommandType = adCmdStoredProc

 cmd.ActiveConnection = conn

 cmd.Parameters.Refresh

 MsgBox (cmd.Parameters.Count)

 cmd(1) = userName

 Set rs = cmd.Execute

 rs.Close
 conn.Close
 Set cmd = Nothing
End Function

I double and triple checked all my spellings. I know that the connection to my database is good, because I am able to use it to do other SQL queries. It's just stored queries that have parameters that I'm stuck on. I based my attempt on this tutorial from Microsoft: https://learn.microsoft.com/en-us/sql/ado/guide/data/calling-a-stored-procedure-with-a-command?view=sql-server-ver15

Here is what the design-view of this simple query looks like in Access (the parameter being circled in red):

The SQL-view of the of the query in access:

Anyone have any tips?

Answer 1

adCmdStoredProc doesn't seem to be designed for MsAccess queries. Change it to adCmdTable. Also, when setting the parameter values they are indexed from zero.

 Dim conn As ADODB.Connection
 Dim rs As ADODB.Recordset
 Dim cmd As New ADODB.Command

 Set conn = makeConnection()
 cmd.CommandText = "accessLevelByUN"
 cmd.CommandType = adCmdTable

 cmd.ActiveConnection = conn

 cmd.Parameters.Refresh

 MsgBox (cmd.Parameters.Count)

 cmd.Parameters(0) = userName

 Set rs = cmd.Execute
...