Syntax Error or Access Violation (1 Viewer)

Oludare

New member
Local time
Today, 08:55
Joined
Jan 28, 2021
Messages
24
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

Active member
Local time
Today, 00:55
Joined
Apr 13, 2010
Messages
689
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:
Cheers,
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:55
Joined
May 21, 2018
Messages
4,763
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

Registered User.
Local time
Today, 17:55
Joined
Jul 4, 2013
Messages
2,487
Code:
cmd.CommandText = "spRefreshReissuePaymentData '" & strBordVal & "' " ', '" & strFailValReason & "' "

doesn't look correct.
Maybe that should be something like
Code:
cmd.CommandText = "spRefreshReissuePaymentData '" & strBordVal & "' " & ", ''" & strFailValReason & "' "
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 17:55
Joined
Jan 20, 2009
Messages
12,268
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.
CrazyCode.PNG
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 17:55
Joined
Jan 20, 2009
Messages
12,268
Here it is again but it works now. Maybe there was somethng wrong with the post

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

arnelgp

error reading drive A:
Local time
Today, 15:55
Joined
May 7, 2009
Messages
12,021
i think you don't need EXEC, to run a stored procedure.
if there are 2 parameters:

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

Code:
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

New member
Local time
Today, 08:55
Joined
Jan 28, 2021
Messages
24
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

error reading drive A:
Local time
Today, 15:55
Joined
May 7, 2009
Messages
12,021
click Debug and post the code where it faults.
it has something to do with Connection object?
 

Oludare

New member
Local time
Today, 08:55
Joined
Jan 28, 2021
Messages
24
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

error reading drive A:
Local time
Today, 15:55
Joined
May 7, 2009
Messages
12,021
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

Super Moderator
Staff member
Local time
Today, 17:55
Joined
Jan 20, 2009
Messages
12,268
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

error reading drive A:
Local time
Today, 15:55
Joined
May 7, 2009
Messages
12,021
can you post a thread where EXEC was used
in adodb.command to run a stored proc.
 

Users who are viewing this thread

Top Bottom