Syntax Error or Access Violation

[Oludare]

Hello All,
Please help I received the above error when calling a stored procedure in Access VBA. The error occurred at the rs.open line.
Please find below the code.
Regards and thanks

Private Sub RefreshValidationReason()
On Error GoTo RefreshValReason_ERR

Dim strBordVal As String
Dim intBordItemVal As Integer

strBordVal = Me.CboBordereau
intBordItemVal = Forms!frmReissuePaymentsMain!frmReissueDatasheet.Form!BordItem

'Connect to SQL Server
Dim cn As ADODB.Connection
Set cn = modDataHelper.GetAdodbConnection

'Configure the command
Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandTimeout = cn.CommandTimeout
cmd.CommandText = "spRefreshReissuePaymentData '" & strBordVal & "' " ', '" & strFailValReason & "' "
cmd.CommandType = adCmdStoredProc
Dim prm As ADODB.Parameter
Set prm = cmd.CreateParameter("@BordNoVal", adVarChar, adParamInput, 50, strBordVal)
cmd.Parameters.Append prm


'Open the recordset
Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenKeyset, adLockReadOnly

'Bind to Sub Form of Main Form
'Set Forms!frmReissuePaymentsMain!frmReissueDatasheet.Form.Recordset = rs
DimSet Me.frmReissueDatasheet.Form.Recordset = rs

'Bind to Sub Form of Main Form
'Set Me.Recordset = rs
'Set Forms!frmReissuePaymentsMain!frmReissueDatasheet.Form.Recordset = rs
'Set Me.txtBordNo.Value = rs.Fields(1).Value

'Close and release ADO resources

On Error Resume Next

If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
If Not cmd Is Nothing Then
Set cmd = Nothing
End If
If Not cn Is Nothing Then
cn.Close
Set cn = Nothing
End If

RefreshValReason_Exit:
Exit Sub

RefreshValReason_ERR:

modMessageHelper.ShowErrorMessage Err.Description, "frmReissuePaymentsMain.RefreshValReason", Err.Number
Resume RefreshValReason_Exit

End Sub

 

[bastanu]

Can you try to leave on the CommandText just the name of the procedure:

cmd.CommandText = "spRefreshReissuePaymentData"
Also I cannot see where you declare the second paramter (strFailValReason) and where you set its value. Try to use the .CreateParameter like for the first one.
Here is a link that might help you:

Parameters Collection (ADO) - ActiveX Data Objects (ADO)

Parameters Collection (ADO)

docs.microsoft.com

Cheers,

 

[MajP]

I am a little curious on how this resolves. Can you do a debug.print as per below?

dim strCmd as string
strCmd = "spRefreshReissuePaymentData '" & strBordVal & "' " ', '" & strFailValReason & "' "
debug.print strCmd
cmd.CommandText = strCmd

 

[Cronk]

cmd.CommandText = "spRefreshReissuePaymentData '" & strBordVal & "' " ', '" & strFailValReason & "' "

doesn't look correct.
Maybe that should be something like

cmd.CommandText = "spRefreshReissuePaymentData '" & strBordVal & "' " & ", ''" & strFailValReason & "' "

 

[arnelgp]

doesn't look correct.

see post #3.

 

[Galaxiom]

You need to set the values for the Parameters before appending them to the parameters collection. Then just call the procedure

Alternatively use adCmdText instead of adCmdStoredProc and this command.

EDIT: There is a problem with the board code. It spits the dummy when I try to post this code.

 

[Galaxiom]

Here it is again but it works now. Maybe there was somethng wrong with the post

cmd.CommandText = "EXEC spRefreshReissuePaymentData '" & strBordVal & "', '" & strFailValReason & "' "

 

[arnelgp]

i think you don't need EXEC, to run a stored procedure.
if there are 2 parameters:

cmd.CommandText = "spRefreshReissuePaymentData('" & strBordVal & "','" & strFailValReason & "')"

Private Sub RefreshValidationReason()
On Error GoTo RefreshValReason_ERR

    Dim strBordVal As String
    Dim intBordItemVal As Integer
    
    strBordVal = Me.CboBordereau
    intBordItemVal = Forms!frmReissuePaymentsMain!frmReissueDatasheet.Form!BordItem
    
    'Connect to SQL Server
    Dim cn As ADODB.Connection
    Set cn = modDataHelper.GetAdodbConnection
        
    'arnelgp, don't forget to open the connetion
    cn.Open
    
    'Configure the command
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandTimeout = cn.CommandTimeout
    cmd.CommandText = "spRefreshReissuePaymentData('" & strBordVal & "','" & strFailValReason & "')"
    cmd.CommandType = adCmdStoredProc
    
    'arnelgp
    'you do not set this
    'Dim prm As ADODB.Parameter
    'Set prm = cmd.CreateParameter("@BordNoVal", adVarChar, adParamInput, 50, strBordVal)
    'cmd.Parameters.Append prm
    
    
    'Open the recordset
    Dim rs As New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open cmd, , adOpenKeyset, adLockReadOnly
    
    'Bind to Sub Form of Main Form
    'Set Forms!frmReissuePaymentsMain!frmReissueDatasheet.Form.Recordset = rs
    DimSet Me.frmReissueDatasheet.Form.Recordset = rs
    
    'Bind to Sub Form of Main Form
    'Set Me.Recordset = rs
    'Set Forms!frmReissuePaymentsMain!frmReissueDatasheet.Form.Recordset = rs
    'Set Me.txtBordNo.Value = rs.Fields(1).Value
    
    'Close and release ADO resources
    
    On Error Resume Next
    
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not cmd Is Nothing Then
        Set cmd = Nothing
    End If
    If Not cn Is Nothing Then
        cn.Close
        Set cn = Nothing
    End If
    
RefreshValReason_Exit:
    Exit Sub
    
RefreshValReason_ERR:
    
    modMessageHelper.ShowErrorMessage Err.Description, "frmReissuePaymentsMain.RefreshValReason", Err.Number
    Resume RefreshValReason_Exit

End Sub

 

[Oludare]

Hello All, Thanks for your suggestions. I applied the code suggestion from Arnelgp, and the error was 3705 Operation not allowed when the object is open.

 

[arnelgp]

click Debug and post the code where it faults.
it has something to do with Connection object?

 

[Oludare]

I have fixed the error by rewriting the line below.

'Configure the command
Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandTimeout = cn.CommandTimeout
cmd.CommandText = "spRefreshReissuePaymentData"
cmd.CommandType = adCmdStoredProc

Dim prm As ADODB.Parameter
Set prm = cmd.CreateParameter("@BordNoVal", adVarChar, adParamInput, 50, strBordVal)
cmd.Parameters.Append prm

Thanks for all your help.

 

[arnelgp]

so, you only have 1 parameter?
without using ADODB.Parameter:

Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandTimeout = cn.CommandTimeout
cmd.CommandText = "spRefreshReissuePaymentData('" & strBordVal & "')"
cmd.CommandType = adCmdStoredProc

'Open the recordset
Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenKeyset, adLockReadOnly

!!EDIT:
whatever i am posting is what i am testing on my SQLExpress on my pc.

 

[Galaxiom]

i think you don't need EXEC, to run a stored procedure.
if there are 2 parameters:

cmd.CommandText = "spRefreshReissuePaymentData('" & strBordVal & "','" & strFailValReason & "')"

The EXECUTE keyword became optional for Stored Procedures from SQL Server 2008, provided it is the first statement in a batch. (The ADODB call would constitute a batch.)

The parentheses around the parameter list has always been optional.

 

[arnelgp]

can you post a thread where EXEC was used
in adodb.command to run a stored proc.