VBA ODBC Update

Question

This is the code I'm using to update an SQL database:

Public Sub main()

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset

    cnn.Open "ConnectionName"
    rst.ActiveConnection = cnn
    rst.CursorLocation = adUseServer

    rst.Source = "Update Table ..."
    rst.Open

    Set rst = Nothing
    Set cnn = Nothing
End Sub

What I want to know is if and how I should deal with the rst object after opening it. Do I close it? When I try doing rst.Close, I get the error: "Operation is not allowed when the object is closed". The code works fine without rst.Close, I'm wondering if there are any dangers to not closing the object.

Answer 1

An update returns no rows, so no need to open a recordset.

You can execute the statement directly using:

Connection.Execute "Update Table ...", [flags]

To answer your question, after .Open the recordset is closed (its .state will be adStateClosed) as no data has been returned, so setting it to nothing is sufficent.

Answer 2

An UPDATE operation does not return a resultset. Therefore, if executed with a Recordset object, it results in an empty and closed recordset. It cannot be closed anyway because it has never been opened.

A rule of thumb is:

if rst.State <> adStateClosed then rst.Close

But, because you are executing a command that's not going to return data anyway, the preferred way is:

dim cm as ADODB.Command
set cm = new adodb.command
set cm.activeconnection = cnn
cm.commandtype = adCmdText
cm.commandtext = "UPDATE ..."

cm.execute ,, adExecuteNoRecords

Or, if your SQL is a fixed string that doesn't have parameters,

cnn.execute "UPDATE ...",, adExecuteNoRecords

Also, please change rst.ActiveConnection = cnn to Set rst.ActiveConnection = cnn.