Code to run stored procedure returning Invalid use of property

[antonycat]

We have an Access 2002 frontend and SQL Server 2000 backend.

I am trying to get this function to work but am getting Invalid use of property, when the code gets to cmdSQL.CommandType

Here is the code I have written:


Public Function GetStoredProcedure() As String
On Error GoTo Err_GetStoredProcedure_Click

Dim strConn As String
Dim cnnDB As ADODB.Connection
Dim cmdSQL As New ADODB.Command

Set cnnDB = New ADODB.Connection

strConn = "Driver={SQL Server};" & _
"Server=OurServer;"&_
"Database=DatabaseName;"&_
"Trusted_Connection=Yes"

cnnDB.Open strConn

'Set the connection for the command to your opened connection
cmdSQL.ActiveConnection = cnnDB

'Set the command object type to stored procedure
cmdSQL.CommandType adCmdStoredProc

'Set the stored procedure name
cmdSQL.CommandText = "CreateReferenceID"

'Execute the command
cmdSQL.Execute

'Close the connection
cnnDB.Close

Exit_GetStoredProcedure_Click:
Exit Function

Err_GetStoredProcedure_Click:
MsgBox Err.Description
Resume Exit_GetStoredProcedure_Click

End Function

There are no parameters in the stored procedure.

It would be greatly appreciated if someone could tell me where I have gone wrong.

Thanking you in advance.

Regards
Antony

 

[genomon]

Not real familiar with Access Data Projects, but I "cmdSQL.CommandType adCmdStoredProc" is not available in a "regular" .mdb, but only .adp's.
Also the correct syntax would be
"cmdSQL.CommandType = adCmdStoredProc" (using the equal sign). Hope this helps....

The Microsoft mascot is a butterfly.
A butterfly is a bug.
Think about it....

 

[antonycat]

Thanks for pointing out the oversight on my part and it does work in our mdb.

Regards
Antony

 

[genomon]

That's good to know! I have only created a couple adp's; our shop standards need dusting off as we still use a home-cooked ODBC procedure in VBA to execute procs on SQL Server for all our Access mdb's (although a canned pass-through query would work for most). Annual reviews coming up - maybe time for me to update some code and shoot for that big raise (ya right)!. So anyway, did adding the equal sign get rid of the error?

I have great faith in fools; self-confidence my friends call it.
-Poe