Keep recordset open?

[jpr]

I have a student tracking database where we add about 200 student records a
day. I use an unbound form and vba to add and update records. When a
student is checked in to the database, a new record is added. When the
student is checked out, the record is retrieved and updated. There are about
400 updates to the recordset throughout the day (two per student record)
Which is the best option:
Option 1
Keep the recordset open all day and simply write to it as needed (about 400
times)

Option 2
Open and close the recordset as needed (about 400 times )?

Thank you.

 

[Nick Coe \(UK\)]

Option two is my choice.

If you do not explicitly close a recordset then it could
cause problems if the pc is switched off, the power fails,
some other fault occurs which leaves the data mdb in an
indeterminate state. Often Jet (the database engine itself)
will close recordsets when it thinks they are out of scope
but it's not predictable.

There is another thread in
microsoft.public.access.formscoding titled 'why close a
recordset' which will give you more info.

--
Nick Coe (UK)
Available - Will work for money
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store

In jpr typed:

 

[jpr]

I'm sorry but I didn't state my question well; my subject is misleading. If
you had a database that was open to the same unbound form everyday from
9:00am to 5:00pm and you added/updated an average of 400 records in the same
recordset everyday, there are at least two ways you could use that recordset.
Option 1
-Declare the recordset variable at the module level of the form.
-Open the recordset in the Form_Open event (the form and the recordset would
stay open all day)
-Add or update records as needed. The recordset is visible to all
procedures in the form.
-Close the recordset in the form_close event at the end of the day when the
database is closed.
-or-
Option 2
-Declare/open/close the recordset every time you need to write to it (about
400 times a day)

The recordset could contain up to around 20,000 records.

Both options work but which one is best? Is it better to open the recordset
every time you need it or is it better to open it once, update it as needed
and close it at the end of the day?

 

[Ken Snell [MVP]]

I concur with Nick... I would open the recordset (actually, I probably just
would run code that creates and executes an SQL statement, not use a
recordset) when I need it and close it until I need it again.

 

[david epsom dot com dot au]

I world not use module-level recordsets.

If you are going to use unbound recordsets,
open when required, close as soon as possible.

(david)

 

[Nick Coe \(UK\)]

I'd still go with option 2. Leaving a recordset open is
asking for trouble.

Couple of other things occur to me -
1/ With that volume of edits/additions per day you should be
thinking about frequent backups (perhaps more often than
nightly, weekly incrementals or whatever).
2/ IIRC when you use a bound form data is commited to the
database from the edit buffer on form close or when stepping
to another record or hit esc twice and the buffer reverts
and so on. With a recordset _you_ are controlling when the
buffer contents are commited to the database under most
circumstances. All my experience and instincts lead me to
open an rst, do stuff, then close it.

I like Ken's suggestion for using an sql statement, it's
something I do if possible because it's usually easier than
mucking about with DAO or ADO recordsets. Can't always do
it that way of course.

I would only use module level (as opposed to form class
module) recordsets if performing an old fashioned batch
process or a re-usable function where the rst is opened and
then specifically closed.

My impression is what you're actually juggling with here is
safety, accuracy and coherence of your database against
apparent need to live edit. In that set of circumstances
I'd use a bound form if I could (even if it meant
de-normalising some of my tables) with some unbound controls
and or event driven form class module code to do any fancy
stuff. Of course, your situation may preclude that.

Good luck.

--
Nick Coe (UK)
Available - Will work for money
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store

In jpr typed: