Error on closing a ADODB.Recordset VBA

Question

Dear all,

I have built this VBA function but I'm getting the below error (see the next screenshot) in the bold portion of the code:

Public Sub UserLogoutDetail(UserEmail As String) 

    Dim MySQL As String 

    Dim RstUserAccess As New ADODB.Recordset 

    Dim CmdUserAccess  As New ADODB.Command 

    Dim i As Integer 

    'Setup the command 

    Set CmdUserAccess.ActiveConnection = fGetConn 

    CmdUserAccess.CommandType = adCmdText 

    MySQL = "UPDATE User_Access_TABLE SET EXIT_DATA = #" & Now & "# WHERE (((ID)=" & GetID_ToUpdate(UserEmail) & "))" 

    CmdUserAccess.CommandText = MySQL 

    RstUserAccess.CursorLocation = adUseClient 

    RstUserAccess.CursorType = adOpenDynamic 

    RstUserAccess.LockType = adLockOptimistic 

    'Open the connection 

    RstUserAccess.Open CmdUserAccess 

Set RstUserAccess.ActiveConnection = Nothing 

RstUserAccess.Close

    'Cleanup 

    If CBool(CmdUserAccess.State And adStateOpen) = True Then 

        Set CmdUserAccess = Nothing 

    End If 

    If CBool(fGetConn.State And adStateOpen) = True Then 

        CloseConn 

    End If 

 End Sub

Do you have any suggestion?

Thanks

Answer 1

You should close the recordset before setting the connection to nothing. But why use a recordset at all? Since your SQL string is an Update statement, you can use

CmdUserAccess.Execute

to execute the SQL.

Answer 2

...still issue on below RstUserAccess.Close coding:

Thanks

Answer 3

1. There is no need to set the ActiveConnection to Nothing if the recordset has already been closed, so you can simple delete that line.
2. I repeat: why use a recordset at all?

Answer 4

1. There is no need to set the ActiveConnection to Nothing if the recordset has already been closed, so you can simple delete that line.
2. I repeat: why use a recordset at all?

1. Issue again:

2. These are routine done in the past linked to an MSAccess database.

The only thing I have to do is changing few parameters on my query except the line which is giving me the error :(

Regards

Answer 5

Remove the line that sets the ActiveConnection to Nothing!!!!!!!!