Closing a recordset in VBA

[Seth Schrock]

I have read that every recordset that you open should be explicitly closed. In this particular case, I have opened the recordset in the following way:

Dim intPronounCount as Integer
intPronounCount = CurrentDb.OpenRecordset("SELECT * FROM tblProunouns").RecordCount

I have no variables set as DAO.Recordset, so how do I close this recordset? Do I just put

CurrentDb.OpenRecordset("SELECT * FROM tblPronouns").Close

Or do I need to worry about closing the recordset since I haven't set it to a variable?

*Another Thought*
Or do I just need to add the little bit of extra code like this:

Dim rstPronoun as DAO.Recordset
Dim intPronounCount as integer

Set rstPronoun = CurrentDb.OpenRecordset("SELECT * FROM tblProunouns")
intPronounCount = rstPronoun.RecordCount

rstPronoun.close
Set rstPronoun = Nothing

[NeoPa]

Your *Another Thought* was basically the answer Seth. You do need to close recordsets. Setting the variable to Nothing is less important, but recordsets need to be closed. Another thing to avoid is using the return value of the function CurrentDb() as a reference as you have. This should be assigned to a DAO.Database variable and that variable used.

Dim dbVar As DAO.Database
Dim rstPronoun As DAO.Recordset
Dim intPronounCount As Integer

Set dbVar = CurrentDb()
Set rstPronoun = dbVar.OpenRecordset("SELECT * FROM [tblProunouns]")
intPronounCount = rstPronoun.RecordCount
rstPronoun.Close

Of course, a much more straightforward approach altogether, would be :

Dim intPronounCount As Integer

intPronounCount = DCount(Expr:="*", Domain:="[tblPronouns]")

Clearly though, that misses out on the learning process involved with the DAO Recordsets ;-)

[ADezii]

Another point that I feel is worth mentioning is that you may not know if a Recordset was actually created in the first place. This can typically occur in a Local Procedure with Error Trapping whereas an Error can occur either prior to, or after, a Recordset was actually created. In this scenario, you need to check for the existence of the Recordset Object itself, as in:

Private Sub cmdRecordsetClose_Click()
On Error GoTo Err_cmdRecordsetClose_Click
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset

'Potentially Error producing Code intentionally omitted

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("SELECT * FROM Products", dbOpenDynaset)

'Potentially Error producing Code intentionally omitted

Exit_cmdRecordsetClose_Click:
  If Not rst Is Nothing Then
    rst.Close
      Set rst = Nothing
  End If
    Exit Sub

Err_cmdRecordsetClose_Click:
  MsgBox Err.Description
    Resume Exit_cmdRecordsetClose_Click
End Sub

[Seth Schrock]

@ADezii I have actually run into having errors when trying to close a recordset that due to the path the code ran through If/Then statements was never opened. I couldn't think of a way to test to see if it was opened so I just moved the opening code to where it would always run. Your code helps me with that angle, so thank you for point this out.

@NeoPa I do think that your second post will be the avenue that I choose for my database, but I will choose your first as the answer as it answers my question the best.

Possibly, better asked in a different thread, but why is it better to declare a variable for the CurrentDb instead of just using CurrentDb.OpenR ecordset? I have often seen a variable used in examples online, but I just figured that it was so that less would have to be typed if it was needed multiple places (often db or dbs was the variable used). I didn't think that there was a difference between using a variable and using the straight value.

[NeoPa]

Seth:
@NeoPa I do think that your second post will be the avenue that I choose for my database, but I will choose your first as the answer as it answers my question the best.

You're right Seth. It made more sense to merge them into a single post in the end though. I should have seen that earlier.

[zmbd]

Possibly, better asked in a different thread, but why is it better to declare a variable for the CurrentDb instead of just using CurrentDb.OpenR ecordset?

Not too far off topic as we're still talking about recordsets..

Notice that the variable is a specific type of database DAO, ADO, or ADODB. By type casting the specific record set you ensure that the expected functionality will be implemented. The following has a chart comparing the different types and available functionality: Choosing ADO or DAO for Working with Access Databases

[Seth Schrock]

Ah... so the purpose of using the variable is so that you can explicitly choose which type of database (something that I know you are supposed to do and I try to always do). That makes sense.

[TheSmileyCoder]

I would argue its a bit more complicated then that, allthough it is a valid point made.

First off, its important to understand that CurrentDB() is a function call, that returns a DB variable. It is not a DB. So it has more overhead then using a variable, if you use it multiple times.

Example:

set RS1=CurrentDB.Openrecordset("....")
set RS2=CurrentDB.Openrecordset("....")
set RS3=CurrentDB.Openrecordset("....")
set RS4=CurrentDB.Openrecordset("....")

Would be written more efficient as:

Dim oDB as Dao.Database
Set oDB=CurrentDB()
set RS1=oDB.Openrecordset("....")
set RS2=oDB.Openrecordset("....")
set RS3=oDB.Openrecordset("....")
set RS4=oDB.Openrecordset("....")
Set oDB=Nothing

That is just a matter of efficiency, and of course as mentioned clarity of type of DB being used.

Another example could be that you are using information from more then 1 access backend. If you use linked tables you can stick to using CurrentDB, but if you sometimes open information from a backend without using linked tables you can do it using a secondary database object (Or from the currentdb, depending on syntax). I realize it may be confusing, but its simply because the choices are quite many, and different ways of doing it can provide benefits in some conditions.

Example:

Dim oPrimaryDB as Dao.Database
Set oPrimaryDB=CurrentDB()
Dim oSecondaryDB as dao.Database
set oSecondaryDB=OpenDatabase("C:\Example.Mdb")

Yet another reasons is that CurrentDB does not return the same handle to the database each time its called. This can be be desribed by the following example:

Currentdb.Execute "Update Table...."
Msgbox "Records updated:" & Currentdb.RecordsAffected

The records affected will always be listed as 0, because the handle used to run the SQL is not the same handle as is being used to get the number of records affected.

Try this instead:

Dim oDB as dao.database
Set oDB=CurrentDB
oDB.Execute "Update Table...."
Msgbox "Records updated:" & oDB.RecordsAffected

This will correctly return the number of records affected. I realise that this concept can also be a bit hard to grasp, and I myself must admit I am still a bit fuzzy on it.

Finally a topic I would consider a bit too far off topic would be getting into Transactions, workspaces and database objects, but that is quite a large subject in itself, so that will be for another time.

Best of luck with your project!
TheSmileyCoder

[Seth Schrock]

That makes it a little clearer. Thanks Smiley.

[TheSmileyCoder]

Heheh, I hope I managed to clear it up, more then I managed to muddy it up. :)

[Seth Schrock]

I got the important part: Always use the variable and not the straight CurrentDb because there is a good reason. :)

I think that I understood several of your arguments fairly well. The rest of it is a little over my head at this point, but hopefully I will be able to come back once my level of knowledge gets a little higher and I will understand everything.

[NeoPa]

I was going to jump in on this one as using the variable instead of the function call is absolutely about more than simply stipulating the type of the object (CurrentDb() returns a DAO.Database object specifically), but Smiley has made pretty well all the points I would have, very well. Not that Z's post wasn't helpful too, but I just felt it gave the impression that there was little more to worry about than the type of object in use (which is far from true).

This issue comes up over and over again because many fail to realise that it is an object returned by a function. The point Smiley made about the object being different every time it's called is very pertinent. Making code work together is very difficult without the realisation of this point. Furthermore, the resource overheads involved maintaining multiple copies of this object (I understand that the object is not too trivial) is such that many people have dbs that run out of resources simply due to using CurrentDb() in their loops rather than using a variable worked out once at the start. It is not a trivial problem and you will certainly be better off for understanding these points, and if not quite understanding all the issues, at least knowing to avoid the reuse of the function wherever you can in your code.

Seth:
I got the important part: Always use the variable and not the straight CurrentDb because there is a good reason. :)

If you get no further than that then you've still picked up the most important point.

Smiley:
Heheh, I hope I managed to clear it up, more then I managed to muddy it up. :)

Definitely. A very good job I thought.

[zmbd]

I purposely simplified the need to type casting and the variable assignment.
For most people the additional information doesn't add to the understanding of how/why to call. This is sort-of like ignoring the small x in the pH function in General Chemistry so as to avoid the quadratic equation. The error is small, and the full equation distracts from the underlying need.

[NeoPa]

I'm not sure I follow that very well Z, but as I was certainly not attempting to cast any aspersions on your post I'm not too sure I need to. I was merely anxious to ensure the points related to resources etc were put down and emphasised as they are the crux of the matter from my point of view. If my wording sounded critical of your post them I must accept responsibility for that of course, and I apologise for my clumsiness.

[Seth Schrock]

I think that I have understood most of the reasons for using the variable and I certainly am going to check all of my previous dbs to find all the places where I took the shortcut and didn't use the variable. Thankfully, I don't think that I ever used it in a loop.

Thanks to everyone for explaining this to me. I always like to understand the reason for doing things a certain way as I feel that I will remember it better and be able to more easily use my knowledge to decide the proper way to do something depending on the circumstances.