Looking for critique of VBA snippet (Familar with C++/databases on Unix)

[darlingm]

I'm hoping someone can comment on the code block below. I'm using Access 2007.

I'm very familiar with C, C++, and C#, and using those languages to interact with databases in Unix, but this is my first time using VBA and Access together.

I'm looking to basically learn if a seasoned VBA programmer would look at the code below and do it much differently, or more efficiently.

I'm also wondering whether the ".Close" and "= Nothing" are important in VBA for memory leaks, or whether variables in VBA falling out of scope automatically destruct and free their memory

The objective of this subroutine is to check if the Access 2007 database has a row in a table with certain criteria. If so, it updates a field in that row that wasn't part of the criteria. Otherwise, it creates a new row that has the criteria and the new data.

The variable names have been changed to protect the guilty.

NOTE: The code snippets below are identical, except for deletions which are underlined.

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM MyTable WHERE FieldA=""Field Data"";", CurrentProject.Connection, adOpenDynamic, adLockPessimistic

If rs.EOF Then
[U]   rs.Close
   rs = Nothing
   Set rs = New ADODB.Recordset
   rs.Open "MyTable", CurrentProject.Connection, adOpenDynamic, adLockPessimistic[/U]
   rs!FieldA = "Field Data"
End If

rs!FieldB = "Something Else"

rs.Update
rs.Close

Set rs = Nothing

Is there something simpler that can be done in VBA? Like...

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM MyTable WHERE FieldA=""Field Data"";", CurrentProject.Connection, adOpenDynamic, adLockPessimistic

If rs.EOF Then
   rs!FieldA = "Field Data"
End If

rs!FieldB = "Something Else"

rs.Update
[U]rs.Close

Set rs = Nothing[/U]

Or...

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM MyTable WHERE FieldA=""Field Data"";", CurrentProject.Connection, adOpenDynamic, adLockPessimistic

If rs.EOF Then
   rs!FieldA = "Field Data"
End If

rs!FieldB = "Something Else"

rs.Update

 

[Moniker]

Neither of the latter examples will work. If the recordset is empty (EOF is true), then trying to set a field in it will result in an error.

Change to this:

If rs.EOF Then
    rs.AddNew
    rs!FieldA = "Field Data"
    rs.Update
Else
    rs!FieldB = "Something Else"
End If

And yes, any object where you have to set it (Set rs = New ADODB.Recordset, for example), it's best practice to set it to nothing as you've done. You don't have to close, then set to nothing, then set it again. The close and then an open would be fine. It's just when you're done with the object, set it to nothing.