Calling Procedure in Class

[scottsanpedro]

Hi,
I have a simple call to an ADO subroutine that refreshes a recordset from SQL SERVER 2005 to my ACCESS 2007 DB.

Set cmd = New ADODB.Command
    cmd.ActiveConnection = gcnn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = mCommandText
    
    Set rstServer = New ADODB.Recordset
    rstServer.CursorLocation = adUseClient
    rstServer.CursorType = adOpenKeyset
    rstServer.Open cmd
    
    Set Form_subfrmRenewalErrors.Recordset = rstServer
    Form_subfrmRenewalErrors.Requery
    
    Set cmd = Nothing
    Set rstServer = Nothing

Notice that there is a variable 'mCommandText' in the cmd.CommandText.
All I want to do is pass the name of the SP across to the class and it to run. But I'm getting errors.
Automating a general ADO call via a class.
Thanks if you can help
Scott

 

[scottsanpedro]

Error is;
Description Method 'Recordset' of object .....failed

 

[HarleyQuinn]

How have you tried to pass the command text to the class that gives you errors?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[scottsanpedro]

I'm trying to pass the text to the CommandText. It works if I hard code the SP names, in this case as follows (calling procedure from form)

Dim ClearLog As clsRenewals

Set ClearLog = New clsRenewals

ClearLog.CommandText = "CM_PROCESS_ERROR_LOG_CLEAR"
ClearLog.ClearErrorLog

 

[scottsanpedro]

to make myself clear.
If I hardcode the CommandText in the Class, then it works fine. Passing the string variable creates an error!
Scott