I've been racking my brain with an error for quite awhile now regarding adding a parameter to a collection for an ADO Command object. The scenario is that I have a SQL query called from Access against a SQL Server database that has one character parameter.
The relevant piece of code is:
Dim param As ADODB.Parameter
Set param = cmd.CreateParameter("@p1", adVarChar, adParamInput, 20, val)
cmd.Parameters.Append param
The ADO documentation for the CreateParameter method states:
This method does not automatically append the Parameter object to the Parameters collection of a Command object. This lets you set additional properties whose values ADO will validate when you append the Parameter object to the collection.
When I ran my test function it failed with Object Required and an error number of 424. I finally noticed that after I called CreateParameter that there was indeed a parameter in the collection. So it turns out that the ADO documentation in this case is wrong.
I was able to resolve the problem by directly instantiating the Parameter and then appending it to the collection -- that is I did not use the CreateParameter method.