473,836 Members | 1,580 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Closing a recordset in VBA

Seth Schrock
2,965 Recognized Expert Specialist
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:
Expand|Select|Wrap|Line Numbers
  1. Dim intPronounCount as Integer
  2. 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
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim rstPronoun as DAO.Recordset
  2. Dim intPronounCount as integer
  3.  
  4. Set rstPronoun = CurrentDb.OpenRecordset("SELECT * FROM tblProunouns")
  5. intPronounCount = rstPronoun.RecordCount
  6.  
  7. rstPronoun.close
  8. Set rstPronoun = Nothing
Dec 28 '12
14 21217
Seth Schrock
2,965 Recognized Expert Specialist
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.
Dec 29 '12 #11
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Dec 31 '12 #12
zmbd
5,501 Recognized Expert Moderator Expert
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.
Dec 31 '12 #13
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Dec 31 '12 #14
Seth Schrock
2,965 Recognized Expert Specialist
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.
Dec 31 '12 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

8
2309
by: dmiller23462 | last post by:
My brain is nuked....Can anybody tell me right off the bat what is wrong with this code? Along with any glaring errors, please let me know the syntax to display a message (Response.Write would be fine I think) that will say "I'm sorry but the data you requested cannot be found" or something along those lines.... This code is on an archive page I have on my company's intranet....The end result is to show 3 records at a time pulled from an...
4
1781
by: James | last post by:
Quick question about closing recordsets and connection objects. We're in the process of rewriting a TON of bad code. None of it is even remotely tabbed properly, it's impossible to read half the time and it never closes connection objects or recordsets. Unfortunately, I've been assigned the task of closing them. I assume I'm just doing: recordset.close Set recordset = Nothing conn.close
1
3315
by: andrewdreib | last post by:
I am still very new to ASP and am trying to create an ASP page that gets records from a database. Right not I can successfully get one field of information at a time and randomize it, but I need the whole string. I've been searching the internet trying to find an answer to this question and I found these two commands Request.querystring and Recordset.GetString, but I don't think I know how to use them properly. Here's the code I have...
7
1659
by: Arpan | last post by:
Assume that an ASP page has 3 SQL queries. Can a recordset object be populated with the records of all these 3 queries at the same time? Please note that the 3 queries are distinct queries, not related to each other in any way. Also please note that I am not talking about populating the recordset with the records of the 1st query, then closing it & again opening that recordset, populating it with the records of the 2nd query so on & so...
5
29857
by: Simone | last post by:
Hello I hope you guys can help me. I am very new to ADO... I am creating a ADODB connection in a module and trying to access it from a command button in a form. Function fxEIDAssgn(plngEID As Long) As Boolean Dim rsAssignedUser As ADODB.Recordset Dim strSelectUser As String
12
8367
by: (Pete Cresswell) | last post by:
Say I've got a RecordSet: ----------------------------------------------------------- Set myRS = CurrentDB.OpenRecordset(SomeSQL, dbOpenDynaset) ----------------------------------------------------------- Is there any way to dynamically filter/unfilter/re-filter that RS without doing any .Opens or .Closes? -- PeteCresswell
1
1692
by: Uros | last post by:
When closing some forms in MSAccess 2003 Access is crashed (Microsoft Office Access has encounteres a problem and needs to close. We are sorry .......). If I close form without changes work OK, but when I change and save the form then access is crashed, but not always, in 10% working OK. I hawe ower 150 forms but only a few forms make me trouble, one forms (Continuous Form) have link SQL tables as a recordset, one have RO passthrough query...
18
6153
by: Darryl Kerkeslager | last post by:
When I open an ADO Recordset, I close it. However, it seems that there may be some difference in this manner of opening a Recordset: Dim rL As ADODB.Recordset Set rL = New ADODB.Recordset src = "SELECT Count(*) FROM reviewer INNER JOIN pp_officer " & _ "ON reviewer.reviewer_id = pp_officer.ppo_rev_id " & _ "WHERE rev_login = 'EllisonL'" Set rL = CurrentProject.Connection.Execute(src, , adCmdText)
10
12316
by: Roger Withnell | last post by:
I'm using ASP, VBScript and SQL Server. I'm also using UTF-8 character set and so my codepage is 65001 and SQL Server datatype nvarchar. I can insert unicode characters correctly into the database table using INSERT.... (field1) ...VALUES ......... (N'Characters'). How do I do this using Rs.Update viz-a-viz:
6
6834
by: blue875 | last post by:
Hello helper people who are smarter than me: I have a form that needs to submit multiple queries to different tables during one Sub's execution. Some sections are as simple as: 1| With rst 2| .Open query1 3| .Close 4| .Open query2 5| End With
0
9668
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10843
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9371
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5648
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4448
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4015
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3112
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.