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?

@June7 I added the sql-view of the Access stored query. Is that what you're looking for? — 155trimmer
– 155trimmer, Commented Feb 27, 2022 at 20:47
Should be text instead of image to allow copy/paste but since image is readable and query short, can live with. I will have to do some testing. I don't normally use dynamic parameterized queries and never needed to do this from Excel. So unless someone else provides answer, I will be back later. Why is Excel involved? — June7
– June7, Commented Feb 27, 2022 at 20:53
@June7 it's school project, so I have to do it the way they say, lol. Otherwise, I wouldn't be using VBA at all. — 155trimmer
– 155trimmer, Commented Feb 27, 2022 at 23:29

tinazmu · Accepted Answer · 2022-02-27 21:55:04Z

2

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
...

answered Feb 27, 2022 at 21:55

tinazmu

5,5442 gold badges12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

155trimmer Over a year ago

That's it! Weird that MS used indexing from 1 in their tutorial, but I would have figured that one out quick. The real problem was the command type. Thank you so much!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related