Solved: Again: Is closing a recordset always necessary?

[tyndale2045]

[I screwed up and posted this question in the Excel section. And I can't figure out how to delete it. If any admin folk can delete it for me, or tell me how, that would be great. Anyways . . . to the question, now that I'm posting in the right place.]

I'm trying to learn SQL for a project I'm working on. In doing so, I've adapted the following code from the "Access 2002 Desktop Developer's Handbook." I have one question: don't I need to insert some kind of statement to close the ADO recordset? (I'm not using the DAO part of the code). But if I uncomment the rst.close line of code toward the bottom, I get this error "Operation is not allowed when the object is closed." So, what's the deal? How is it getting closed?

Any info will be appreciated.




Public Sub LearningSQL()

Dim intI As Integer
Dim strSQL As String
Dim sfm As SubForm

strSQL = "INSERT INTO [Inventory Transactions](ProductID, UnitsUsed) VALUES (6,676)"
#If USEDAO Then
' Open a DAO Recordset.
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
#Else
' Open an ADO recordset.
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open Source:=strSQL, _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText
'rst.Close
#End If
End Sub



Thanks,
Jim

[CreganTur]

The issue here is that you cannot create a recordset off of an action query- you can only create a recordset from a SELECT query. You're getting that error because your recordset cannot be opened- it's invalid.

If you want to use an action query look into using the DoCmd.RunSQL Method.


don't I need to insert some kind of statement to close the ADO recordset?
Yes, you must close the recordset when you are done with it, but you can't stop there. After you close the recordset good design dictates that you release the memory allocated for your connections. This means that you would:
rst.Close
Set rst = Nothing 'release memory

[tyndale2045]

Thank you Randy. That answered my question.

[nepotist]

Thanks randy the set rst = nothing
dint knew about it ,
say if I dont set rst = nothing will the size of my application increase every time I run it ?

[CreganTur]

Thanks randy the set rst = nothing
dint knew about it ,
say if I dont set rst = nothing will the size of my application increase every time I run it ?

I'm not sure of all of the specific reasons for this convention excapt that setting the recordset and connection objects to nothing instantly releases the memory they were using.

The memory will be released automatically when you quit your database, this just keeps things from getting cluttered, and if you're working with a very large recordset you could see a slow down of other processes later on if you don't release it.

Does that make sense? :think:

[nepotist]

yahh it does :D

and to remind you I am waiting for your new article that you said you will be writing