ADO - recordset navigation with code

[Bill Morgan]

I am trying to educate myself in ADO by writing code
behind an unbound form containing unbound text boxes. I
want my form to mimic the action of a bound form and bound
text boxes (the bound form containing standard, system
generated navigation buttons).

I have set up buttons on my unbound form for MoveNext,
MovePrevious.

Question1: With ADO code alone, how do I mimic the action
of the MoveNext and MovePrevious buttons - i.e., the
appearence of moving back and forth through the recordset,
one record at a time? Since the rs = Nothing at the end of
one MoveNext sub procedure, how do I keep track of my
current position so that the next time I press the
MoveNext button it moves to the very next record in the
recordset? Is the standard method to use AbsolutePosition
or Bookmarks with public variables?

Question2: In real world practice, is there ever a need to
do what I am trying to do here? In other words, if for
some reason Access cannot link itself to an external data
source, would the standard procedure be to loop through
the recordset with ADO and populate an Access table with
the data - then use bound forms and text boxes to view the
data?

Thanks for your assistance.

Bill Morgan

 

[Neil]

Hi bill,

1) The easy way (but more error prone) to doing this is to delcare a global
recordset to the forms module. You can then use this as you wish in your
forms code. e.g. Open the recordset in the forms open event and then close
it in the forms unload event. MoveNext and MovePrevious is not supported
with ADO, it is just Move (i think). You pass the number of records that you
want move into the move method (- numbers to move backwards).

2) There are reasons why you would want to do this but normally it would be
the other way round - Another program would extract data from Access (or
another db program) and populate the recordset with table data. Access has
its own way of functioning with bound forms. For example, I am sure you are
aware that if you make changes to a field in a bound form, Access will
automatically save the changes for you. Most users want to see a save record
or submit button and have an option to back out of the changes. Access forms
are good for quick and easy form design and are adequate for even the most
demanding application. However, linking to a database from Visual Basic
itself is where the programmer has the most control over data manipulation
and the way records are changed etc. It is also the most time consuming and
you often find yourself re-inventing the wheel to incorperate the usefull
features Access provides.

Hope this helps,

Neil.

 

[Bill Morgan]

Neil,

Very helpful ... Thank you. I am taking a course in VB
right now, and I sometimes get a little lost in the
translation between VB and Access. I need to experiment
with your suggesstion in point 1 (which will lead to more
questions I'm sure). Thanks again for your help.

One further Question: Do you happen to know how the
navigation buttons on an Access bound form (with bound
text boxes) work? Do they persist the recordset for as
long as the form is open (much as you've described in
point one) and then use ADO code, under the hood, to move
back and forth?

b.

 

[Neil]

Bill,

uh oh, been there, done that

I would say that your assumptions are correct. Access will open the
recordset depending on the properties entered and keep it open until the
form is closed or the properties changed. However, I dont know if it is ADO
that is used for the buttons to move from record to record. I would assume
that the recordset is an object which can in turn, use ADO or DAO depending
on what library is selected. If you notice, you can clone the recordset as
either an ADO or DAO recordset. If one or the other was declared in Access
iteself, you would then only be able to use the RecordSetClone method on one
or the other recordset type (hope that made sense). I dont know if this is
the exact way that it works but i am sure it wont be far away.

HTH,

Neil.