ado vs dao - syntax questions

[Laurel]

The following code is from a posting in
the ...access.forms section of this forum, by Van Dinh -
Nov 17, 2003.

Dim db As DAO.Database
Dim qdf As Dao.QueryDef

....
Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs(GRAPH_QCUSTOME) (a string
containing a form name.)
etc.

My problem is that I can't find any syntax that works in
my environment. I've put off really understanding DAO vs
ADO as long as I could, and now I find that the HELP is
just confusing. I don't even really know what I'm using.
Sometimes I think I've pinned it down, and then someone
says something that puts me in confusion again.

Could someone...
1 - Give me syntax for this little bit of code that might
work for me so I can get on with my project. I need to
reference and modify the SQL of a form that is not yet
open.

2 - Give me some pointers for sorting this out beyond
recommending HELP articles (I do believe I've read them
all... or all that I could find.)

Here are clues to my current environment.

- I'm running Access 2000.
- I'm using a mdb, not a "project"
- If I use Option Explicit, the two DIMs cause a runtime
error "User defined type not defined"
- If I turn off Option Explicit, the statement "Set db =
DBEngine(0)(0) stops returning an error, but the variable
in the "locals" window won't expand.
(I have no idea what the 0, 0 are... no clue in HELP
again.)

 

[John Vinson]

On Sat, 29 Nov 2003 09:57:29 -0800, "Laurel"

Answers inline.

Dim db As DAO.Database
Dim qdf As Dao.QueryDef

These Dim's will only work if you include the DAO reference library in
the VBA references. Open the VBA editor and select Tools...
References. Browse down the list until you find "Microsoft DAO x.xx
Obuct Library", and check the box by the highest version number (3.6
for A2000 if I remember correctly).

...
Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs(GRAPH_QCUSTOME) (a string
containing a form name.)
etc.

My problem is that I can't find any syntax that works in
my environment. I've put off really understanding DAO vs
ADO as long as I could, and now I find that the HELP is
just confusing. I don't even really know what I'm using.

You're using whichever one is checked in the References dialog above;
if you have both checked, you must explicitly Dim some objects as DAO
or ADOX. The DAO object model has a Database object, and the ADO model
doesn't (it uses a Connection object instead); but *both* have
Querydef and Recordset objects... *different kinds of* objects!

Sometimes I think I've pinned it down, and then someone
says something that puts me in confusion again.

You're not alone. ADO confuses me no end.

Could someone...
1 - Give me syntax for this little bit of code that might
work for me so I can get on with my project. I need to
reference and modify the SQL of a form that is not yet
open.

If the Form is based on a stored Query your "set qdf =" statement
above will work, but you must use the name *OF THE QUERY*, not the
name of the form. It'll be a bit tricky getting to the Recordsource
property of a Form that isn't open - why do you need it *then*? Note
that in a Form's Open event, the recordsource is defined but has not
yet been opened; if you want to change the SQL of the recordsource,
this is the appropriate event in most cases. You can use code like

Private Sub Form_Open(Cancel as Integer)
Dim strSQL As String
....

2 - Give me some pointers for sorting this out beyond
recommending HELP articles (I do believe I've read them
all... or all that I could find.)

Well... not knowing exactly what you're trying to accomplish I can't
be sure this will help, but the above is my best guess.

Here are clues to my current environment.

- I'm running Access 2000.
- I'm using a mdb, not a "project"
- If I use Option Explicit, the two DIMs cause a runtime
error "User defined type not defined"

Because you're referencing the ADO object library, not the DAO object
library; and the ADO library has no Database type object.

- If I turn off Option Explicit, the statement "Set db =
DBEngine(0)(0) stops returning an error, but the variable
in the "locals" window won't expand.

That's just hiding the error message, not curing the problem.

(I have no idea what the 0, 0 are... no clue in HELP
again.)

It's an alternative syntax. The DBEngine object is the JET database
engine; one of its Methods is the Workspace object; and one of the
Methods of a Workspace is a database. DBEngine(0) is the first
Workspace created (automatically, when you open your database); the
default method of a DBEngine object is the Workspace object, and the
default method of a Workspace is a Database - so

DBEngine(0)(0)

is equivalent to

DBEngine.Workspaces(0).Databases(0)

which is a verbose way of saying "give me a reference to the currently
open Database object".

Instead of DBEngine(0)(0) you can say CurrentDb. There are subtle
differences between the two, but they both give you a pointer to the
current database, and either one will work.

 

[Laurel]

Thanks. This is all very orienting! A response to your
specific questions are below.

If the Form is based on a stored Query your "set qdf =" statement
above will work, but you must use the name *OF THE QUERY*, not the
name of the form. It'll be a bit tricky getting to the Recordsource
property of a Form that isn't open - why do you need it *then*? Note
that in a Form's Open event, the recordsource is defined but has not
yet been opened; if you want to change the SQL of the recordsource,
this is the appropriate event in most cases. You can use

code like

I need this because I find that most methods for
specifying retrieval criteria on the fly don't work for
charts. You get errors from Microsoft Graph or Jet if you
try to put in paramters, or reference Forms! controls or
fields as you would normally do. So I'm following Van
Dinh's advice and modifying the Query that the chart is
originally based on before opening the form that contains
the chart. I think the technical term is that I need to
modify the query in it's state as part of "a collection."
(Is this correct jargon?) So it's not the recordsource of
a form I'm trying to modify. It's the WHERE clause of an
unopened query. But I'm guessing/hoping that your tips on
the syntax for defining a query will let me move ahead.
Thanks much!

 

[Laurel]

I'm still having difficulties. I no longer get compile
time errors, but I get the the "Microsoft Access"
error "Item not found in this collection" when the second
line is executed.

Dim db As DAO.database
Set db = DAO.DBEngine(0)(0)

This is not too surprising to me, since the context-
sensitive help does not show an attribute "database" for
the DAO. object. In other words, when I type DAO.d....
I'm shown a list that looks like this

CursorDriverEnum
DatabaseTypeEnum
DataTypeEnum
dbAppendOnly

So I don't know what environment is required to make the
Set db statement (or, probably, the DIM statement) work.

I made "Microsoft DAO 3.60 Object Library" a reference.
It comes after the "Microsoft ActiveX Data Objects 2.1
Library" in the references list.

BTW - Thanks again for clarifying the relationship between
DAO/ADO, and helping me understand that they don't refer
to the underlying structure of the database itself.

 

[John Vinson]

I'm still having difficulties. I no longer get compile
time errors, but I get the the "Microsoft Access"
error "Item not found in this collection" when the second
line is executed.

Dim db As DAO.database
Set db = DAO.DBEngine(0)(0)

Just leave off the DAO:

Set db = DBEngine(0)(0)

or, alternatively,

Set db = CurrentDb

I made "Microsoft DAO 3.60 Object Library" a reference.
It comes after the "Microsoft ActiveX Data Objects 2.1
Library" in the references list.

If you're not intentionally using ADOX, I'd just uncheck the Microsoft
ActiveX Data Objects.

BTW - Thanks again for clarifying the relationship between
DAO/ADO, and helping me understand that they don't refer
to the underlying structure of the database itself.

Well... in a sense they do; but they are two different methods of
doing so. DAO is specifically for JET (Microsoft Access, basically)
databases; ADO is much more flexible as it can refer to SQL, MySQL,
Oracle, or any number of other data storage media.