"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?