By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,551 Members | 2,710 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,551 IT Pros & Developers. It's quick & easy.

How long does recordset stay open?

P: n/a
I rarely deal with recordsets directly with code, since I usually use
Access queries, so be patient with this question. I want to open a
recordset with various default variables used by my program. I tried:

Public Sub OpenDefaults()
Dim db As dao.Database
Dim globalRst As dao.Recordset
Set db = CurrentDb()
Set globalRst = db.OpenRecordset("tblDefaults")
End Sub

I am able to pull variable I need when I'm IN this module. e.g.

boolUseFormA = globalRst!bFrmA

Whoever, I am not able to get the variables after executing the module.
I thought the recordset stayed open until I did: 'Set globalRst =
nothing'. I wanted to open the recordset when I started up my
application, then put variables from it as needed.

My questions are:
1) Am I doing something wrong? Should I be able to pull data from the
recordset until I reset it to nothing?
2) If this is impossible, which would be best for performance:
a) Opening a form with the variables and pulling them from
the form, e.g.
boolUseFormA = Forms!globalForm!bFrmA
b) Running the above code with the additional line to pull
the data I need and also additional codes to unset the database and
recordset objects?

Thanks!
Jim M

Aug 25 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Since you declared your variable inside the OpenDefaults procedure, it
ceases to exist as soon as the procedure exits. Its lifetime is just a few
microseconds in your example. The variable is also invisible to anything
else outside this procedure, i.e. its scope is just the procedure you
declared it in.

You can give it wider scope and a longer lifetime if you declare it in the
General Declarations section of your module (at the top, with the Option
statements.) If this is a standard module, its lifetime will last until you
close Access or reset the code. If it is in the module of a form, its
lifetime ceases when you close the form.

Doing that also changes the scope of the variable. In a standard module, it
will default to public scope, i.e. you can use it in any code in this
database. In a form's module, you can use the variable in any procedure in
that module.

Of course, that could have unintended consequences. If you use public
variables, you have to be really careful not to alter the value
inintentionally, and you don't know when you come to use it if some other
process may have altered it. It gets even more confusing if another
module/procedure uses the same name! VBA permits that, so which one is now
being accessed? Which value is being returned from the variable? And which
one is being changed? If you don't use Option Explicit, you now have a
*real* debugging nightmare on your hands!

At a practical level, code that relies on public variables is messy to
develop. You frequently reset during development, so they keep losing their
value, so you can't trust them to hold what you expect. At minimum, you
problaby need another public boolean variable just to hold the value of
whether they are initialized or not, and code to re-initialize them.

To answer your specific questions:
1. It is possible, but not advisable to use variables with a wide scope and
long lifetime.

2. Initializing a control on the form in Form_Open, and then reading the
value of the control when you need it would be faster than repeatedly
opening a recordset to get the same value over and over.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jim M" <ma*****@rci.rutgers.eduwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
>I rarely deal with recordsets directly with code, since I usually use
Access queries, so be patient with this question. I want to open a
recordset with various default variables used by my program. I tried:

Public Sub OpenDefaults()
Dim db As dao.Database
Dim globalRst As dao.Recordset
Set db = CurrentDb()
Set globalRst = db.OpenRecordset("tblDefaults")
End Sub

I am able to pull variable I need when I'm IN this module. e.g.

boolUseFormA = globalRst!bFrmA

Whoever, I am not able to get the variables after executing the module.
I thought the recordset stayed open until I did: 'Set globalRst =
nothing'. I wanted to open the recordset when I started up my
application, then put variables from it as needed.

My questions are:
1) Am I doing something wrong? Should I be able to pull data from the
recordset until I reset it to nothing?
2) If this is impossible, which would be best for performance:
a) Opening a form with the variables and pulling them from
the form, e.g.
boolUseFormA = Forms!globalForm!bFrmA
b) Running the above code with the additional line to pull
the data I need and also additional codes to unset the database and
recordset objects?

Thanks!
Jim M

Aug 25 '06 #2

P: n/a
Jim,
Allen pretty much said it. What I had to add was this: My take on
storing preferences is to keep them in a text file and load them from
the file. Also, these days I am leaning more toward class modules with
the properties & methods I need for the processes I am implementing
instead of doing things in the db engine. The db then becomes a bucket
that holds object properties instead of the container for the data.
So, in your case I'd probably create a class module that held the
preferences & defaults and create an instance of that as something like
"objDefaults" which could be referenced for any default values. Even
better IMHO is in the design of the objects for an app to include code
that embeds a default value in a property.

Allen Browne wrote:
Since you declared your variable inside the OpenDefaults procedure, it
ceases to exist as soon as the procedure exits. Its lifetime is just a few
microseconds in your example. The variable is also invisible to anything
else outside this procedure, i.e. its scope is just the procedure you
declared it in.

You can give it wider scope and a longer lifetime if you declare it in the
General Declarations section of your module (at the top, with the Option
statements.) If this is a standard module, its lifetime will last until you
close Access or reset the code. If it is in the module of a form, its
lifetime ceases when you close the form.

Doing that also changes the scope of the variable. In a standard module, it
will default to public scope, i.e. you can use it in any code in this
database. In a form's module, you can use the variable in any procedure in
that module.

Of course, that could have unintended consequences. If you use public
variables, you have to be really careful not to alter the value
inintentionally, and you don't know when you come to use it if some other
process may have altered it. It gets even more confusing if another
module/procedure uses the same name! VBA permits that, so which one is now
being accessed? Which value is being returned from the variable? And which
one is being changed? If you don't use Option Explicit, you now have a
*real* debugging nightmare on your hands!

At a practical level, code that relies on public variables is messy to
develop. You frequently reset during development, so they keep losing their
value, so you can't trust them to hold what you expect. At minimum, you
problaby need another public boolean variable just to hold the value of
whether they are initialized or not, and code to re-initialize them.

To answer your specific questions:
1. It is possible, but not advisable to use variables with a wide scope and
long lifetime.

2. Initializing a control on the form in Form_Open, and then reading the
value of the control when you need it would be faster than repeatedly
opening a recordset to get the same value over and over.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jim M" <ma*****@rci.rutgers.eduwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
I rarely deal with recordsets directly with code, since I usually use
Access queries, so be patient with this question. I want to open a
recordset with various default variables used by my program. I tried:

Public Sub OpenDefaults()
Dim db As dao.Database
Dim globalRst As dao.Recordset
Set db = CurrentDb()
Set globalRst = db.OpenRecordset("tblDefaults")
End Sub

I am able to pull variable I need when I'm IN this module. e.g.

boolUseFormA = globalRst!bFrmA

Whoever, I am not able to get the variables after executing the module.
I thought the recordset stayed open until I did: 'Set globalRst =
nothing'. I wanted to open the recordset when I started up my
application, then put variables from it as needed.

My questions are:
1) Am I doing something wrong? Should I be able to pull data from the
recordset until I reset it to nothing?
2) If this is impossible, which would be best for performance:
a) Opening a form with the variables and pulling them from
the form, e.g.
boolUseFormA = Forms!globalForm!bFrmA
b) Running the above code with the additional line to pull
the data I need and also additional codes to unset the database and
recordset objects?

Thanks!
Jim M
Aug 25 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.