creating a recordset in VBA

[Guest]

In Access VBA, must I use ADO or DAO to create a recordset that I can
manipulate using VBA, or is there an easier way, assuming that the recordset
is coming from the Access database that is currently open.

 

[Steve Schapel]

Jared,

Your question is unrelated to Macros. I am happy to answer your
question here, of course, but maybe
microsoft.public.access.modulesdaovba may have been a more applicable
newsgroup.

ADO and DAO both have a Recordset object which you can use in VBA. I
think most Access developers would use DAO normally, this is designed to
integrate with the Jet database engine. Other than this, I don't know
of any other way to define a recordset. There are various ways to open
a recordset, e.g. based on a SQL statment which you write within the VBA
code, or based on a saved query, or based on the RecordsetClone of a form.

 

[Brendan Reynolds]

You can open a DAO recordset in four lines of code, of which two are just
declarations ...

Dim db AS DAO.Database
Dim rst AS DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM YourTable")

How much easier can it get?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Steve Schapel]

.... and of which two are redundant:

Dim rst AS DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM YourTable")

 

[Steve Schapel]

Brendan,

Also, what is the reason for the SELECT * statement? Is there any
disadvantage to doing like this:
Set rst = CurrentDb.OpenRecordset("YourTable")

 

[Ken Snell [MVP]]

One possible reason?

Unless you specify the dbOpenDynaset value for the second argument, this
statement will default to the dbOpenTable format, which limits your ability
to use / modify the data. Using a query, it defaults to dbOpenDynaset.

 

[Steve Schapel]

Aha! Thanks, Ken, I never thought of that. I guess I would so seldom
want a recordset that included all the fields and all the records in a
table, that the question is only of academic interest anyway

 

[Ken Snell [MVP]]

Although I sometimes open a recordset using just the table name, I almost
always open it as a Dynaset because almost all of my tables are linked, and
of course dbOpenTable will not work with a linked table!

< g >

 

[Brendan Reynolds]

Not if you really want all of the columns from all of the rows, no, but as
you say elsewhere in this thread, that is seldom what you want. I'm
expecting the OP to replace the sample SQL statement with one more
appropriate to the requirements of the application. I'm leaving it to the OP
to do that because, of course, I don't know what those requirements are!

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.