Do I need to close the recordset in MS Access if I am not creating a recordset object?
I've read that it's important to close Recordset Objects in Access, but with my code, I never create a Recordset object, I always just use an inline reference like:
Dim ClientName As String ClientName = CurrentDB.OpenRecordset([some SQL]).Fields(0).Value
I don't see anything like CurrentDB.CloseRecordset, and I don't think CurrentDB.Close is a good idea. Do I need to close any recordset in this case or does it automatically do it?
I am using MS Access 2007 with a SQL Server backend over an ODBC connection.
If any of my terminology or usage is incorrect, feel free to correct me!
source to share
Your code creates an ephemeral set of records; it goes out of scope immediately upon completion of the statement. Thus, you cannot .Close
recordset because it no longer exists.
The situation is similar to this immediate window session ...
? CurrentDB.Recordsets.Count 0 strSelect = "SELECT Count(*) FROM Dual;" MyVar = CurrentDB.OpenRecordset(strSelect)(0) ? MyVar 1 ? CurrentDB.Recordsets.Count 0
source to share
Apparently CurrentDB.OpenRecordset 'is being attached to the collection of recordsets
This is how this code works, and it indicates what you've added to the collection of recordsets:
Dim ClientName As String
msgbox CurrentDB.Recordsets.Count
ClientName = CurrentDB.OpenRecordset([some SQL]).Fields(0).Value
msgbox CurrentDB.Recordsets.Count
and reaching the limb this works:
Dim ClientName As String
msgbox CurrentDB.Recordsets.Count
ClientName = CurrentDB.OpenRecordset([some SQL]).Fields(0).Value
msgbox CurrentDB.Recordsets.Count
msgbox CurrentDB.Recordsets(0).Close
source to share