"garbage collection" in VBA SQLS insert

[David Penz]

The overall VBA application executes automatically on a polling cycle to read instructions from a SQL Server table. On receiving certain instructions, the VBA app reads data files into Excel, Excel massages the data and then VBA inserts the data along with status information into other SQLS tables. The example below shows a small portion of the code, a subroutine that inserts a new status record into the SQLS status table.

A "setup" subroutine (not shown here) executes when Excel loads, to assign variable names and to open the SQLS connection. The connection remains open while Excel is open (this is a local SQLS instance, the server is the same PC that runs Excel/VBA). On Excel close, the statement "Set strs = nothing" executes. The example code runs fine, however, there are reports that Excel "hangs up" now and then.

I'm a newbie when it comes to interfacing Excel/VBA with SQLS. Does this code create a new Recordset object every time it executes? Do these objects persist and accumulate, and eventually cause the computer to run out of memory space?

Is the best practice to close out the transaction with "Set strs = nothing" within this subroutine? Or would a better solution be to create the Recordset object one time as a Global, and simply refer to it when needed?

[6StringJazzer]

Welcome to the Forum David Penz!

Please do not attach text files that we must download and open in an editor just to be able to see your code. Paste code directly into your post. Use code tags when doing so.

Attribute VB_Name = "Status"
Option Explicit

Dim StatusQuery As String

Sub WriteStatus()

'====================================================================================
'This subroutine inserts into SQL Server one record from "status" data table in Excel
'====================================================================================

' Excel worksheet that contains the source data

    Set sh = Worksheets("12_status")

' Transfer status data

    StatusQuery = "test_data.data_status"
    Set strs = CreateObject("ADODB.Recordset")

    With strs
        .Open StatusQuery, conn, 1, 3
        .AddNew
    
        .Fields("test_run_num").Value = sh.Cells(9, 1).Value
        .Fields("data_status").Value = sh.Cells(9, 2).Value
        .Fields("report_status").Value = sh.Cells(9, 3).Value
        .Fields("sn_label_status").Value = sh.Cells(9, 4).Value
        .Fields("flag_tag_status").Value = sh.Cells(9, 5).Value
        .Fields("metal_tag_status").Value = sh.Cells(9, 6).Value
   
        .Update
        .Close
    End With

End Sub

[6StringJazzer]

Where is variable strs declared? No declaration is shown in this code. I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

Does this code create a new Recordset object every time it executes?

In general, when a variable is assigned as an object, such as

Set strs = CreateObject("ADODB.Recordset")

any prior object referenced by strs will be released, and garbage collection should automatically reclaim the storage. There is some concern in the VBA community that garbage collection is not as effective or as timely as it should be, but I don't have firsthand knowledge of Microsoft's implementation. Some people also explicitly set these variables to Nothing before leaving a Sub; I do not see the value in that since the variable is released anyway when the sub exits. If explicitly setting it to Nothing forces garbage collection to happen immediately rather than waiting for the process to come along and find it, maybe it has value, but again--I don't know for a fact that this is the case, and I doubt that you can do anything in VBA that forces garbage collection.

So in your case, when Sub WriteStatus is called, it assigns the variable strs to a new object. However, we can't tell what is the scope of the variable because you haven't shown us all your code. If it is not declared anywhere, then it will be scoped to the Sub WriteStatus and default to a Variant type (not a best practice). It is loaded into memory when the sub is called and its stack frame is loaded, and then released when the sub exits. Garbage collection should release the object it pointed to after this exit. However, if it is declared as a Public variable somewhere else, the variable itself will persist after the Sub exits. Because the same variable is being reused, each time it is assigned to a new object the prior object should be garbage collected. In a worst-case scenario, the garbage collection is not timely and you could have these old recordsets pile up until either garbage collection occurs or Excel exits. But that would be a bad implementaion on Microsoft's part and lead to memory leaks.

If you want to explicitly set the variable to Nothing you should do it at the end of this sub, if it is not referenced elsewhere outside the sub, rather than waiting for Excel to close. It's pointless to do that when Excel closes, because all of Excel's memory is released at that point.

Do these objects persist and accumulate, and eventually cause the computer to run out of memory space?

Is your computer running out of memory space?

[David Penz]

Thank you for your patience with me, and thorough response. Below I post what I hope are all the relevant parts of the code, in addition to the earlier snippet.

I do not know for a fact that the computer is running (or ever did) run out of memory space. The application is on a remote computer, and I don't have regular access. Mainly I hear anecdotal notice that "something went wrong". It is never enough information to diagnose any particular fault.

' DataCycle module
Attribute VB_Name = "DataCycle"
Option Explicit
Global sh As Object
Global strs As Object

Sub DataCycle()
[snip]
    Call WriteStatus
[snip]
End Sub

' Status module
Attribute VB_Name = "Status"
Option Explicit
Dim StatusQuery As String

Sub WriteStatus()

'====================================================================================
'This subroutine inserts into SQL Server one record from "status" data table in Excel
'====================================================================================

' Excel worksheet that contains the source data

    Set sh = Worksheets("12_status")

' Transfer status data

    StatusQuery = "test_data.data_status"
    Set strs = CreateObject("ADODB.Recordset")

    With strs
        .Open StatusQuery, conn, 1, 3
        .AddNew
        .Fields("test_run_num").Value = sh.Cells(9, 1).Value
        .Fields("data_status").Value = sh.Cells(9, 2).Value
        .Fields("report_status").Value = sh.Cells(9, 3).Value
        .Fields("sn_label_status").Value = sh.Cells(9, 4).Value
        .Fields("flag_tag_status").Value = sh.Cells(9, 5).Value
        .Fields("metal_tag_status").Value = sh.Cells(9, 6).Value
        .Update
        .Close
    End With

End Sub

Sub Auto_Close()
[snip]
    Set sh = Nothing
    Set strs = Nothing
[snip]
End Sub

The global object type variables "sh" and "strs" are declared in the DataCycle module.
The subroutine "DataCycle" runs every two seconds per the scheduler. This sub performs a variety of tasks, one of which is to trigger sub "WriteStatus" to update the "test_data.data_status" SQLS table.
As of now, the "sh" and "strs" objects are released in the "AutoClose" sub.

From your post #3 I think I understand that the code is OK as is, as VBA should release the earlier recordset object when the latest one is created. However, for sure it does no good to "Set strs = Nothing" as Excel is closing, and it will not do harm and may do good to include the "Set strs = Nothing" statement after the insert operation in the sub WriteStatus().

[6StringJazzer]

Global is a legacy keyword from VB that is preserved for backwards compatibility, but has been replaced by Public.

strs and sh are used exclusively in Sub WriteData. I see no need for them to be Public.

I do not see how (or why) you are using the Attribute keyword here.

Insert blue code, delete red code.

' DataCycle module

Attribute VB_Name = "DataCycle" ' ?????
Option Explicit

Global sh As Object
Global strs As Object


Sub DataCycle()
[snip]
    Call WriteStatus
[snip]
End Sub

' Status module

Attribute VB_Name = "Status" ' ?????
Option Explicit
Dim StatusQuery As String

Sub WriteStatus()

'====================================================================================
'This subroutine inserts into SQL Server one record from "status" data table in Excel
'====================================================================================



   Dim sh As Object
   Dim strs As Object
   Dim StatusQuery As String



' Excel worksheet that contains the source data

    Set sh = Worksheets("12_status")

' Transfer status data

    StatusQuery = "test_data.data_status"
    Set strs = CreateObject("ADODB.Recordset")

    With strs
        .Open StatusQuery, conn, 1, 3
        .AddNew
        .Fields("test_run_num").Value = sh.Cells(9, 1).Value
        .Fields("data_status").Value = sh.Cells(9, 2).Value
        .Fields("report_status").Value = sh.Cells(9, 3).Value
        .Fields("sn_label_status").Value = sh.Cells(9, 4).Value
        .Fields("flag_tag_status").Value = sh.Cells(9, 5).Value
        .Fields("metal_tag_status").Value = sh.Cells(9, 6).Value
        .Update
        .Close
    End With


    Set sh = Nothing
    Set strs = Nothing


End Sub

Sub Auto_Close()
[snip]

    Set sh = Nothing
    Set strs = Nothing

[snip]
End Sub

[David Penz]

Thank you very much, sir. I think I have learned the following lessons:

• Use Public rather than Global
• Declare variable in the sub in which it is used, or in the module, if the variable is used in several subs within the module
• Release object variable after each use, in the same sub

I believe that VBA automatically enters the "Attribute VB_name" statement at the top of a new module. If it does nothing else, it identifies the module when editing. Is it harmful, and should I delete these statements?

[6StringJazzer]

It's not harmful but I've never seen it in the code. VBA inserts it into the underlying module but you do not normally see it in your code editor.

Global works. It's not an error. But it's obsolete, so better to use Public.

Declare variable in the sub in which it is used, or in the module, if the variable is used in several subs within the module

This is actually a big topic and relates to software design. I have a CS degree and many decades of professional software development experience. The rule of thumb I can give you is:

• If a variable is used only within a particular sub, it should be declared local to that sub
• If a variable is used in multiple places, it should be passed as a parameter rather than declared globally

There are many exceptions to this, and I could talk at length about this but am unable to here.