ADO recordset

G

Guest

Hi

I am new to the ADO in Access, so I am hoping somebody can explain
which way is better.

I have a form with 4 buttons for Add, Update, Delete, and Find.

Should I use a global ADODB.Recordset within the form?

OR

Is it better to open and close the Recordset in each Add, Update,
Delete, and Find function?

Thanks!
 
K

Ken Snell [MVP]

I assume that you're not able to use the form's RecordSource as the basis
for the form's data, and just do your work on the form's recordset (rather
than open a new recordset)?

You haven't given us much info about your setup, but, personally, I probably
would go for separate recordsets, as it's likely that you wouldn't be using
the exact same records for each function, and you don't need to refresh the
data each time before you do something with the recordset (assuming that the
data can be accessed by more than one person at a time).
 
G

Guest

Hi Ken

Thanks for reply. Here's how my database is setup. I have a form
containing all unbound fields (around 8 textboxes) . For Example:

ID
Name
Address
...
...

When the user enter an ID that's already in the table, I want to
perform the Ado.Find function (or is it better to do it in the SQL
text?) and fills all the textboxes with the data.

If there is no such ID, the textboxes will be blank for the user to
enter info into. After entering the information, the user will click
on the Update button which will call the ADO.Update function.

I think I best way is to open a recordset on each functions. The only
thing I'm just not sure about is if opening and closing recordsets
will have much affects on the system resources.


Thanks again
 
K

Ken Snell [MVP]

I assume you have a need for a completely unbound form, instead of binding
the form to a query that provides the data. With a bound form, you could use
a combo box in the form's header to "search" for the ID, and if not there,
then to start a new record on the form. And avoid opening/closing all the
recordsets. Note also that you'll need to store the data from the unbound
textboxes when your user is done entering/editing the data.

For your setup, then, there really is no need to use ADO recordset at all.
You could still have an unbound form, but use a DLookup function to identify
if the ID that the person enters is in the table already -- if it is, open a
DAO recordset that contains just that record, and then set the form's
recordset to it. Your user then can use the form's built-in abilities for
editing and deleting.

If the ID is not found, open a DAO recordset to the table as an Append Only
recordset, and then set the form's recordset to it. This allows entry of a
new record.

But it's usually unnecessary for you to write code to do what the form's
design in ACCESS will do for you automatically.

As for performance with your question about opening/closing numerous
recordsets, unless you're opening a recordset that contains thousands of
records, I don't think you're going to see much of a concern here.
 
Top