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.

GSerg · Accepted Answer · 2010-05-24 20:27:57Z

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.

Alex K. · Accepted Answer · 2010-05-24 20:20:04Z

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.

Collectives™ on Stack Overflow

VBA ODBC Update

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related