Creating Queries in DAO vs ADOX

[Greg Strong]

Hello All,

Is it better to create a query in DAO where a report has 4 sub-reports
each of whose record source is a query created at runtime and
everything is in 1 MDB file?

From what I've read and experienced it appears DAO is the way to go in
this situation, so when is it good to use ADOX to create queries?

Why do I ask the question? I've created a MDB file which uses DAO, but
wanted to port everything to ADO. I kind of ran up against the wall on
the queries underlying the report. It seems like the queries do NOT
reflect any data until after the report is generated. I am getting an
error initially on the following line of code:

catDB.Views.Append "qryTempRptOrderMTD", cmd1

The error reads as follows:

Run-time error: Object or provider is not capable of performing
requested operation.

After stepping out of the line in VB it appears the code runs to
completion stopping in the 'end if' in the form to run the report, but
the report has no data in it. When I look at the database window the
queries are not visible, but when I select tables followed by queries
the temporary queries are now there. If I click on one, then data
shows. Needless to say the problem appears to be documented at
http://msdn.microsoft.com/library/de...adocreateq.asp.
Hence my original question.

For the original error I've created a new workgroup per
http://support.microsoft.com/kb/286376/ which eliminated the first
error. I'm see an error which says the report can not see the 1st
query in the 1st sub-report, but the report when in preview still has
nothing in it & the queries are not visible until changing focus to
another object tab & back to the query tab. So I'm still back to
square one and my question.

TIA!

--
Regards,

Greg Strong

[lylefair]

I never use ADOX for creating queries (I never use ADOX for anything).

Private Sub CreateQuery(ByVal Name As Variant, ByVal SQL As String)
With CurrentProject.Connection
On Error Resume Next
.Execute "DROP PROCEDURE " & Name
On Error GoTo 0
.Execute "Create Procedure " & Name & " AS " & SQL
End With
End Sub

Sub test()
CreateQuery "QueryTemp", "SELECT * FROM Table1"
End Sub

Of course, one can also specify parameters as in (from the help file):

CREATE PROCEDURE procedure
[param1 datatype[, param2 datatype[, ...]] AS sqlstatement

*****

CREATE VIEW view [(field1[, field2[, ...]])] AS selectstatement

(TTBOMK this has the same result as

CREATE Procedure procedure [(field1[, field2[, ...]])] AS
selectstatement

*****

I have, of course, a reference to an up-to-date version of ADO.

[MacDermott]

Perhaps you can explain why you wanted to port everything to ADO when it's
all within one MDB file. DAO is optimized for use with the Jet engine, so
why use anything else?

Also -
Do your queries really need to be created at runtime? Wouldn't
parameter queries do the trick?

"Greg Strong" <NoJunk@NoJunk4U².com> wrote in message
news:qn********************************@4ax.com...

Hello All,

Is it better to create a query in DAO where a report has 4 sub-reports
each of whose record source is a query created at runtime and
everything is in 1 MDB file?

From what I've read and experienced it appears DAO is the way to go in
this situation, so when is it good to use ADOX to create queries?

Why do I ask the question? I've created a MDB file which uses DAO, but
wanted to port everything to ADO. I kind of ran up against the wall on
the queries underlying the report. It seems like the queries do NOT
reflect any data until after the report is generated. I am getting an
error initially on the following line of code:

catDB.Views.Append "qryTempRptOrderMTD", cmd1

The error reads as follows:

Run-time error: Object or provider is not capable of performing
requested operation.

After stepping out of the line in VB it appears the code runs to
completion stopping in the 'end if' in the form to run the report, but
the report has no data in it. When I look at the database window the
queries are not visible, but when I select tables followed by queries
the temporary queries are now there. If I click on one, then data
shows. Needless to say the problem appears to be documented at
http://msdn.microsoft.com/library/de...adocreateq.asp. Hence my original question.

For the original error I've created a new workgroup per
http://support.microsoft.com/kb/286376/ which eliminated the first
error. I'm see an error which says the report can not see the 1st
query in the 1st sub-report, but the report when in preview still has
nothing in it & the queries are not visible until changing focus to
another object tab & back to the query tab. So I'm still back to
square one and my question.

TIA!

--
Regards,

Greg Strong

[Greg Strong]

On Sun, 06 Nov 2005 00:08:13 GMT, "MacDermott" <ma********@nospam.com>
wrote:

Perhaps you can explain why you wanted to port everything to ADO when it's
all within one MDB file. DAO is optimized for use with the Jet engine, so
why use anything else?

To learn of the differences. I had working MDB with DAO so decided to
make switch. Anyhow from what I've read DAO is no longer being develop
by MS with ADO being the preferred way. At least that is what I recall
reading.

--
Regards,

Greg Strong

[Greg Strong]

On 5 Nov 2005 15:59:54 -0800, "lylefair" <ly***********@aim.com> wrote:

I never use ADOX for creating queries (I never use ADOX for anything).

From what I recall reading DAO is no longer being developed. As you
know I'm learning, so I thought I'd try to change everything over to
ADO. So if you have to create a query that is the record source of a
report what do you use? TIA!

--
Regards,

Greg Strong

[lylefair]

CurrentProject.Connection.Execute "CREATE PROCEDURE QueryName AS
SELECT Redheads, Telephone FROM Girls WHERE Proclivity = 'Many'"

[Greg Strong]

On 5 Nov 2005 15:59:54 -0800, "lylefair" <ly***********@aim.com> wrote:

I never use ADOX for creating queries (I never use ADOX for anything).
Ok!
Private Sub CreateQuery(ByVal Name As Variant, ByVal SQL As String)
With CurrentProject.Connection
On Error Resume Next
.Execute "DROP PROCEDURE " & Name
On Error GoTo 0
.Execute "Create Procedure " & Name & " AS " & SQL
End With
End Sub

Sub test()
CreateQuery "QueryTemp", "SELECT * FROM Table1"
End Sub

I used this for my code with minor changes. Works fine.

Back to the my original trend of thought. Do you use DAO or ADO when
you have to create queries in code? Or does it depend upon the
circumstances? BTW we are even up in the humor department. :)

TIA!

--
Regards,

Greg Strong

[lylefair]

I use ADO by choice for everything or almost everything. It has many
capabilities that DAO does not have. It can be used easily within other
technologies such as ASP, HTAs and JScript, so I do not have to switch
from DAO to ADO when using these; I just always use ADO. It can be used
to connect to and modify MS-SQL directly through OLEDB connections. Its
providers enable FTP-like file manipulation, recordset saving to XML or
text files (and loading from same as well), and querying Indexing
Service Catalogs. Its Schema provide almost complete information about
the database to which it is connected. It can disconnect its
recordsets, modify them, reconnect and batch update the changed
records. Even within JET, it has some capabilities in column creation
that DAO does not. Its objects are released without the SET
Object=Nothing requirement of DAO.
When dealing with JET, DAO may have a minimal speed advantage. I am
unaware of any situation in my work where this speed advantage is
noticeable.

I have programmed very extensively in DAO, but I've stopped doing so.

I have programmed very extensively in ADO and I continue to do so.

[Allen Browne]

Greg, that information is out of date. Microsoft was pushing that line 5
years ago, so will still read it some places. Back then, they dropped the
DAO library as a default on new A2000 and A2002 databases. In Access 2003,
DAO is back by default.

ADO is now dead (replaced by the very different ADO.NET), and DAO lives on.

There are some things you can only do in DAO, such as setting the Format or
DisplayControl of a field. There are some things you can only do in ADOX,
such as setting the Seed of an Autoincrement column. There are some things
you can only do via DDL, such as changing the data type of an existing
field.

Unfortunately, this means that if you want to know everything, you have to
learn the entire hotchpotch. But if you are working with Access (JET) tables
in an Access database (mdb), DAO is the native Access library, and will
therefore be the most natural approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Greg Strong" <NoJunk@NoJunk4U².com> replied in message
news:au********************************@4ax.com...

On Sun, 06 Nov 2005 00:08:13 GMT, "MacDermott" <ma********@nospam.com>
wrote:

... Anyhow from what I've read DAO is no longer being develop
by MS with ADO being the preferred way. At least that is what I recall
reading.

[Greg Strong]

On 5 Nov 2005 19:33:51 -0800, "lylefair" <ly***********@aim.com> wrote:

I have programmed very extensively in DAO, but I've stopped doing so.

I have programmed very extensively in ADO and I continue to do so.

Well I guess this says it all. Thanks for all of the information.

--
Regards,

Greg Strong

[Greg Strong]

On Sun, 6 Nov 2005 11:55:48 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

Thanks for all of the information.

ADO is now dead (replaced by the very different ADO.NET), and DAO lives on.
In Access 2003 or what? Anyhow not to change the subject and all, but
you did mention '.NET' which is another subject really. If I recall
correctly this is really MS strategy with development languages to help
solidify their OS base. Pretty smart business strategy actually. Only
question is whether the '.NET' languages will provide operational
benefits to the developers. I am in no position to answer this question.
I read and learn. From someone who is learning it almost seems like the
methodology used depends upon who you listen/read. I learn by doing.
Unfortunately, this means that if you want to know everything, you have to
learn the entire hotchpotch.
I guess! LOL!
But if you are working with Access (JET) tables
in an Access database (mdb), DAO is the native Access library, and will
therefore be the most natural approach.

This is good to know, however 'what if' the possibility exists to upsize
to MSDE or SQL Server? Again just thinking out loud, and I have a LOT
of hotchpotch to learn before I get there. Thanks for all of the good
information!!!

--
Regards,

Greg Strong

[DFS]

lylefair wrote:

CurrentProject.Connection.Execute "CREATE PROCEDURE QueryName AS
SELECT Redheads, Telephone FROM Girls WHERE Proclivity = 'Many'"

Return.Empty.ResultSet

[Lyle Fairfield]

Allen Browne wrote:

ADO is now dead (replaced by the very different ADO.NET), and DAO lives on.
It seems that MS isn't leveling with us about this as its site continues
to say:

"# ADO: ActiveX Data Objects (ADO) provides a high-level programming
model that will continue to be enhanced. Although a little less
performant than coding to OLE DB or ODBC directly, ADO is
straightforward to learn and use, and can be used from script languages
such as Microsoft Visual Basic® Scripting Edition (VBScript) or
Microsoft JScript®."

This is really strange! Did they really say "CONTINUE TO BE ENHANCED?"

So how do all those web sites running ASP only and connected to MS-SQL
databases do it with their ADO being "dead"?

(A few years ago when MS had "replaced" DAO as the default in AC2000 and
AC2002 with ADO, Developers implied that this was lamentable and not a
sufficient reason to switch to ADO. Now that MS has switched back to DAO
as the default in AC2003 [this means only that DAO is above ADO in the
references list so that if we dim r as recordset (would anyone do this
without specifying ADODB.Recordset or DAO>Recordset?) it will default to
a DAO recordset], Developers cite this as evidence that DAO lives and
ADO is dead. So MS's default means everything when the default is DAO,
but it means nothing when it's ADO? uh HUH).
There are some things you can only do in DAO, such as setting the Format or
DisplayControl of a field.
These are Access properties and not properties of the database per se.
They are also NOT properties which any advanced developer would use.
There are many many things one CANNOT do in DAO which one CAN do in ADO.
There are some things you can only do in ADOX,
such as setting the Seed of an Autoincrement column.
The Access help file says this will work:

With CurrentProject.Connection

'if the column exists, delete its index, then the column)
If Not .OpenSchema(adSchemaColumns, Array(Empty, Empty, "Table1",
"ID")).BOF Then
If Not .OpenSchema(adSchemaIndexes, Array(Empty, Empty,
"PrimaryKey", Empty, "Table1")).BOF Then
.Execute "DROP INDEX PrimaryKey ON Table1"
End If
.Execute "ALTER TABLE Table1 DROP COLUMN ID"
End If

' add the column setting seed and increment
Application.CurrentProject.Connection.Execute "ALTER TABLE Table1 ADD
COLUMN ID IDENTITY (100, 10) CONSTRAINT PrimaryKey PRIMARY KEY"
End With
There are some things
you can only do via DDL, such as changing the data type of an existing
field.
ADO executes DDL quite satisfactorily:

Public Sub TestingColumnDefinesWithADO()
With CurrentProject.Connection

'if the column exists, delete its index, then the column)
If Not .OpenSchema(adSchemaColumns, Array(Empty, Empty, "Table1",
"Column1")).BOF Then
If Not .OpenSchema(adSchemaIndexes, Array(Empty, Empty,
"Idx_Column1", Empty, "Table1")).BOF Then
.Execute "DROP INDEX Idx_Column1 ON Table1"
End If
.Execute "ALTER TABLE Table1 DROP COLUMN Column1"
End If

' add the column
..Execute "ALTER TABLE Table1 ADD COLUMN Column1 TEXT(10)"

' change the width of the column
..Execute "ALTER TABLE Table1 ALTER COLUMN Column1 TEXT(255)"

' set the value of the column to a random numeric string
Randomize
..Execute "UPDATE Table1 SET Column1 = CSTR(Rnd(ID) * 1000000)"

' index the column
..Execute "CREATE INDEX Idx_Column1 ON Table1 (Column1 ASC) WITH DISALLOW
NULL"

' change the datatype of the column
..Execute "ALTER TABLE Table1 ALTER COLUMN Column1 DECIMAL(18,9)"

End With
End Sub

(Last time I checked the last execute could not be run from DAO; ie ADO
executes DDL which DAO CANNOT execute).
Unfortunately, this means that if you want to know everything, you have to
learn the entire hotchpotch.
Or you could just learn (use) ADO.
But if you are working with Access (JET) tables
in an Access database (mdb), DAO is the native Access library, and will
therefore be the most natural approach.

It's pretty good. For several years I worked extensively with DAO. From
1998-2000 (memory only here) I posted in this newsgroup many DAO
solutions, some of which were original in concept. But I don't use DAO
anymore. ADO is simpler stronger better!

I think everyone should use what he or she feels comfortable with. I do.

--
Lyle Fairfield

[Douglas J. Steele]

In actual fact, it's ADO that's not longer being developed: it's been
replaced by ADO.Net which, despite the similarity in names, is significantly
different than ADO (and which doesn't work from within Access, at least not
at present)

If you read http://blogs.msdn.com/access/archive...13/480870.aspx
(written by Erik Rucker, the Group Program Manager for Microsoft Access),
you'll see that the next version of Access (currently not even in Beta) will
have a revamped version of the Jet Engine, and DAO is certainly a part of
that.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Greg Strong" <NoJunk@NoJunk4U².com> wrote in message
news:au********************************@4ax.com...

On Sun, 06 Nov 2005 00:08:13 GMT, "MacDermott" <ma********@nospam.com>
wrote:

Perhaps you can explain why you wanted to port everything to ADO when it's
all within one MDB file. DAO is optimized for use with the Jet engine, so
why use anything else?

To learn of the differences. I had working MDB with DAO so decided to
make switch. Anyhow from what I've read DAO is no longer being develop
by MS with ADO being the preferred way. At least that is what I recall
reading.

--
Regards,

Greg Strong

[Larry Linson]

"Greg Strong" wrote

From what I recall reading DAO is no
longer being developed.
Remember the old saw: Don't believe anything you hear and only half of what
you see(read). It's been the most alive "dead technology" I have ever seen.
As you know
I'm learning, so I thought I'd try to change
everything over to ADO.
"Classic ADO", the ADO in Access, has already been succeeded by ADO.NET,
which, despite the quote in another thread, is not at all an "evolutionary"
change. No current version of Access "supports" ADO.NET -- I am not
sufficiently familiar with it to know if you could use it by referencing an
appropriate library. I am sticking with MDB and DAO, unless/until
customers/clients insist otherwise, or until Microsoft really determines the
database engine of the future for Access.
So if you have
to create a query that is the record source
of a report what do you use? TIA!

For Jet databases, use the Query Builder within an MDB file -- one view of
which is SQL view, if you have a masochistic streak and want to write SQL
from scratch. For SQL Server databases, with an .ADP as a client you don't
really have "Queries" -- you have SQL-Server-Compatible SQL, for which there
are a number of products, either included with SQL Server, or as separate,
standlone products, and for stored procedures in Microsoft SQL Server, you
have T-SQL.

Larry Linson
Microsoft Access MVP

[david epsom dot com dot au]

> When I look at the database window the queries

are not visible, but when I select tables
followed by queries the temporary queries are
now there.
This is just repainting the screen. If MIGHT
also be true that the querydef collection needs
to be refreshed (I don't know enough about ADO
to tell you that): You may wish to try refreshing
the querydef collection, or the ADO equivalent.

Even if you do that, you will still need to refresh
the screen if you wish to have the screen refreshed.
Needless to say the problem appears to be documented at
http://msdn.microsoft.com/library/de...adocreateq.asp.
That is completely different. This article talks
about queries that are NEVER visible in the database
window, and can't be run from DAO.
Anyway, why create new queries? I would just change the
sql in existing queries.

(david)


"Greg Strong" <NoJunk@NoJunk4U².com> wrote in message
news:qn********************************@4ax.com... Hello All,

Is it better to create a query in DAO where a report has 4 sub-reports
each of whose record source is a query created at runtime and
everything is in 1 MDB file?

From what I've read and experienced it appears DAO is the way to go in
this situation, so when is it good to use ADOX to create queries?

Why do I ask the question? I've created a MDB file which uses DAO, but
wanted to port everything to ADO. I kind of ran up against the wall on
the queries underlying the report. It seems like the queries do NOT
reflect any data until after the report is generated. I am getting an
error initially on the following line of code:

catDB.Views.Append "qryTempRptOrderMTD", cmd1

The error reads as follows:

Run-time error: Object or provider is not capable of performing
requested operation.

After stepping out of the line in VB it appears the code runs to
completion stopping in the 'end if' in the form to run the report, but
the report has no data in it. When I look at the database window the
queries are not visible, but when I select tables followed by queries
the temporary queries are now there. If I click on one, then data
shows. Needless to say the problem appears to be documented at
http://msdn.microsoft.com/library/de...adocreateq.asp.
Hence my original question.

For the original error I've created a new workgroup per
http://support.microsoft.com/kb/286376/ which eliminated the first
error. I'm see an error which says the report can not see the 1st
query in the 1st sub-report, but the report when in preview still has
nothing in it & the queries are not visible until changing focus to
another object tab & back to the query tab. So I'm still back to
square one and my question.

TIA!

--
Regards,

Greg Strong

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:43***********************@per-qv1-newsreader-01.iinet.net.au:

Unfortunately, this means that if you want to know everything, you
have to learn the entire hotchpotch. But if you are working with
Access (JET) tables in an Access database (mdb), DAO is the native
Access library, and will therefore be the most natural approach.

And to clarify:

For those tasks which both DAO and ADO support, DAO will always be
superior to ADO. ADO is only useful for that tiny handful of tasks
that DAO does not support.

The only other reason to program ADO against Jet is if you're going
to upsize to SQL Server, and even then, it's not necessarily going
to be the most efficient approach.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Greg Strong <NoJunk@NoJunk4U².com> wrote in
news:2u********************************@4ax.com:

however 'what if' the possibility exists to upsize
to MSDE or SQL Server? Again just thinking out loud, and I have a
LOT of hotchpotch to learn before I get there. Thanks for all of
the good information!!!

DAO over ODBC to SQL Server works quite well.

Whether it's better than ADO is not something I can judge.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
news:xP********************@rogers.com:

If you read
http://blogs.msdn.com/access/archive...13/480870.aspx
(written by Erik Rucker, the Group Program Manager for Microsoft
Access), you'll see that the next version of Access (currently not
even in Beta) will have a revamped version of the Jet Engine, and
DAO is certainly a part of that.

Is DAO going to be maintained for legacy Jet (i.e., the version
managed by the SQL Server team, not the version in Access), or for
both legacy Jet and Access Jet? It's not clear to me that it will
be. That leaves a bie question about what the best data access
interface for Access Jet will be.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Greg Strong <NoJunk@NoJunk4U².com> wrote in
news:nm********************************@4ax.com:

On 5 Nov 2005 19:33:51 -0800, "lylefair" <ly***********@aim.com>
wrote:

I have programmed very extensively in DAO, but I've stopped doing
so.

I have programmed very extensively in ADO and I continue to do so.

Well I guess this says it all. Thanks for all of the information.

No, it says nothing. Lyle is a fanatic who insisted that Access 2000
was wonderful and that it was the future and that we should all
program ADPs against SQL Server and use ADO and drop DAO because ADO
was so much better.

He now doesn't write ADPs any longer, because he ran up against the
awful problems they bring with them.

He also can use ADO more profitably because all of his projects are
running against SQL Server back ends.

If you're running against a Jet back end, then it's DAO, DAO, DAO,
from beginning to end, from top to bottom. Using ADO in that
environment is an exercise in masochism.

I've never used ADO for anything and have no intentions of ever
doing so in an Access application with a Jet back end.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Larry Linson]

"David W. Fenton" wrote

DAO over ODBC to SQL Server works quite well.
I can vouch for this -- most of the paying work I have done with Access has
been MDB-DAO-ODBC-server. And, unlike ADPs, it is not limited to Microsoft
SQL Server, but works with any ODBC-compliant server (and most server DBs do
have available ODBC drivers).
Whether it's better than ADO is not something I can judge.

The admittedly-small amount of ADP/ADO that I have done did not show me any
advantage over MDB/DAO, but, as you might guess, in the client's shop, we
did not have an identical MDB/DAO application against which to compare. I
found development and modification to take a little more time and effort,
but that may be because I was experiencing a learning curve.

In my experience, on a LAN or a modestly-speedy WAN, both give adequate
performance.

Larry Linson
Microsoft Access MVP

[Adam Turner via AccessMonster.com]

David W. Fenton wrote:

If you read
http://blogs.msdn.com/access/archive...13/480870.aspx
(written by Erik Rucker, the Group Program Manager for Microsoft
Access), you'll see that the next version of Access (currently not
even in Beta) will have a revamped version of the Jet Engine, and
DAO is certainly a part of that.

Is DAO going to be maintained for legacy Jet (i.e., the version
managed by the SQL Server team, not the version in Access), or for
both legacy Jet and Access Jet? It's not clear to me that it will
be. That leaves a bie question about what the best data access
interface for Access Jet will be.

ADO.Net
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1

[Adam Turner via AccessMonster.com]

Greg Strong wrote:

Hello All,

Is it better to create a query in DAO where a report has 4 sub-reports
each of whose record source is a query created at runtime and
everything is in 1 MDB file?

From what I've read and experienced it appears DAO is the way to go in
this situation, so when is it good to use ADOX to create queries?

Why do I ask the question? I've created a MDB file which uses DAO, but
wanted to port everything to ADO. I kind of ran up against the wall on
the queries underlying the report. It seems like the queries do NOT
reflect any data until after the report is generated. I am getting an
error initially on the following line of code:

catDB.Views.Append "qryTempRptOrderMTD", cmd1

The error reads as follows:

Run-time error: Object or provider is not capable of performing
requested operation.

After stepping out of the line in VB it appears the code runs to
completion stopping in the 'end if' in the form to run the report, but
the report has no data in it. When I look at the database window the
queries are not visible, but when I select tables followed by queries
the temporary queries are now there. If I click on one, then data
shows. Needless to say the problem appears to be documented at
http://msdn.microsoft.com/library/de...adocreateq.asp.
Hence my original question.

For the original error I've created a new workgroup per
http://support.microsoft.com/kb/286376/ which eliminated the first
error. I'm see an error which says the report can not see the 1st
query in the 1st sub-report, but the report when in preview still has
nothing in it & the queries are not visible until changing focus to
another object tab & back to the query tab. So I'm still back to
square one and my question.

TIA!

Greg if all you are concerned with is running a query, pick one. When you
narrow your scope to a query only, it doesn't really matter which approach
you use. Overhead is irrelevant if you're just only considering a query.

When your scope broadens, ask the questions again.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1

[Adam Turner via AccessMonster.com]

Lyle Fairfield wrote:

ADO is now dead (replaced by the very different ADO.NET), and DAO lives on.

It seems that MS isn't leveling with us about this as its site continues
to say:

"# ADO: ActiveX Data Objects (ADO) provides a high-level programming
model that will continue to be enhanced. Although a little less
performant than coding to OLE DB or ODBC directly, ADO is
straightforward to learn and use, and can be used from script languages
such as Microsoft Visual Basic® Scripting Edition (VBScript) or
Microsoft JScript®."

This is really strange! Did they really say "CONTINUE TO BE ENHANCED?"

So how do all those web sites running ASP only and connected to MS-SQL
databases do it with their ADO being "dead"?

(A few years ago when MS had "replaced" DAO as the default in AC2000 and
AC2002 with ADO, Developers implied that this was lamentable and not a
sufficient reason to switch to ADO. Now that MS has switched back to DAO
as the default in AC2003 [this means only that DAO is above ADO in the
references list so that if we dim r as recordset (would anyone do this
without specifying ADODB.Recordset or DAO>Recordset?) it will default to
a DAO recordset], Developers cite this as evidence that DAO lives and
ADO is dead. So MS's default means everything when the default is DAO,
but it means nothing when it's ADO? uh HUH).

There are some things you can only do in DAO, such as setting the Format or
DisplayControl of a field.

These are Access properties and not properties of the database per se.
They are also NOT properties which any advanced developer would use.
There are many many things one CANNOT do in DAO which one CAN do in ADO.

There are some things you can only do in ADOX,
such as setting the Seed of an Autoincrement column.

The Access help file says this will work:

With CurrentProject.Connection

'if the column exists, delete its index, then the column)
If Not .OpenSchema(adSchemaColumns, Array(Empty, Empty, "Table1",
"ID")).BOF Then
If Not .OpenSchema(adSchemaIndexes, Array(Empty, Empty,
"PrimaryKey", Empty, "Table1")).BOF Then
.Execute "DROP INDEX PrimaryKey ON Table1"
End If
.Execute "ALTER TABLE Table1 DROP COLUMN ID"
End If

' add the column setting seed and increment
Application.CurrentProject.Connection.Execute "ALTER TABLE Table1 ADD
COLUMN ID IDENTITY (100, 10) CONSTRAINT PrimaryKey PRIMARY KEY"
End With

There are some things
you can only do via DDL, such as changing the data type of an existing
field.

ADO executes DDL quite satisfactorily:

Public Sub TestingColumnDefinesWithADO()
With CurrentProject.Connection

'if the column exists, delete its index, then the column)
If Not .OpenSchema(adSchemaColumns, Array(Empty, Empty, "Table1",
"Column1")).BOF Then
If Not .OpenSchema(adSchemaIndexes, Array(Empty, Empty,
"Idx_Column1", Empty, "Table1")).BOF Then
.Execute "DROP INDEX Idx_Column1 ON Table1"
End If
.Execute "ALTER TABLE Table1 DROP COLUMN Column1"
End If

' add the column
.Execute "ALTER TABLE Table1 ADD COLUMN Column1 TEXT(10)"

' change the width of the column
.Execute "ALTER TABLE Table1 ALTER COLUMN Column1 TEXT(255)"

' set the value of the column to a random numeric string
Randomize
.Execute "UPDATE Table1 SET Column1 = CSTR(Rnd(ID) * 1000000)"

' index the column
.Execute "CREATE INDEX Idx_Column1 ON Table1 (Column1 ASC) WITH DISALLOW
NULL"

' change the datatype of the column
.Execute "ALTER TABLE Table1 ALTER COLUMN Column1 DECIMAL(18,9)"

End With
End Sub

(Last time I checked the last execute could not be run from DAO; ie ADO
executes DDL which DAO CANNOT execute).

Unfortunately, this means that if you want to know everything, you have to
learn the entire hotchpotch.

Or you could just learn (use) ADO.

But if you are working with Access (JET) tables
in an Access database (mdb), DAO is the native Access library, and will
therefore be the most natural approach.

It's pretty good. For several years I worked extensively with DAO. From
1998-2000 (memory only here) I posted in this newsgroup many DAO
solutions, some of which were original in concept. But I don't use DAO
anymore. ADO is simpler stronger better!

I think everyone should use what he or she feels comfortable with. I do.

Severe overkill?
--
Message posted via http://www.accessmonster.com

[Adam Turner via AccessMonster.com]

David W. Fenton wrote:

Unfortunately, this means that if you want to know everything, you
have to learn the entire hotchpotch. But if you are working with
Access (JET) tables in an Access database (mdb), DAO is the native
Access library, and will therefore be the most natural approach.

And to clarify:

For those tasks which both DAO and ADO support, DAO will always be
superior to ADO. ADO is only useful for that tiny handful of tasks
that DAO does not support.

The only other reason to program ADO against Jet is if you're going
to upsize to SQL Server, and even then, it's not necessarily going
to be the most efficient approach.

This is very true.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1

[Adam Turner via AccessMonster.com]

David W. Fenton wrote:

Unfortunately, this means that if you want to know everything, you
have to learn the entire hotchpotch. But if you are working with
Access (JET) tables in an Access database (mdb), DAO is the native
Access library, and will therefore be the most natural approach.

And to clarify:

For those tasks which both DAO and ADO support, DAO will always be
superior to ADO. ADO is only useful for that tiny handful of tasks
that DAO does not support.

The only other reason to program ADO against Jet is if you're going
to upsize to SQL Server, and even then, it's not necessarily going
to be the most efficient approach.

....although support for DAO is quickly fading
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1

[Adam Turner via AccessMonster.com]

lylefair wrote:

I have programmed very extensively in DAO, but I've stopped doing so.

I have programmed very extensively in ADO and I continue to do so.

Good choice
--
Message posted via http://www.accessmonster.com

[Adam Turner via AccessMonster.com]

Greg Strong wrote:

I never use ADOX for creating queries (I never use ADOX for anything).

Ok!

Private Sub CreateQuery(ByVal Name As Variant, ByVal SQL As String)
With CurrentProject.Connection

[quoted text clipped - 8 lines]

CreateQuery "QueryTemp", "SELECT * FROM Table1"
End Sub

I used this for my code with minor changes. Works fine.

Back to the my original trend of thought. Do you use DAO or ADO when
you have to create queries in code? Or does it depend upon the
circumstances? BTW we are even up in the humor department. :)

TIA!

As I've said...pick one if all you want to do is execute a query
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1

[Allen Browne]

"Adam Turner via AccessMonster.com" <u14766@uwe> replied
in message news:5708843f65417@uwe...

...although support for DAO is quickly fading

Not so, Adam.

If you have been reading the blog from Erik Rucker (Access development team)
at:
http://blogs.msdn.com/access/
you know that Access 12 will have its own private version of the JET engine.
That means that DAO (or a derivative of DAO) will be the path to the future.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

[David W. Fenton]

"Adam Turner via AccessMonster.com" <u14766@uwe> wrote in
news:5708843f65417@uwe:

David W. Fenton wrote:

Unfortunately, this means that if you want to know everything,
you have to learn the entire hotchpotch. But if you are working
with Access (JET) tables in an Access database (mdb), DAO is the
native Access library, and will therefore be the most natural
approach.

And to clarify:

For those tasks which both DAO and ADO support, DAO will always be
superior to ADO. ADO is only useful for that tiny handful of tasks
that DAO does not support.

The only other reason to program ADO against Jet is if you're
going to upsize to SQL Server, and even then, it's not necessarily
going to be the most efficient approach.

...although support for DAO is quickly fading

You're simply mistaken on that.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Adam Turner via AccessMonster.com]

Allen Browne wrote:

...although support for DAO is quickly fading

Not so, Adam.

If you have been reading the blog from Erik Rucker (Access development team)
at:
http://blogs.msdn.com/access/
you know that Access 12 will have its own private version of the JET engine.
That means that DAO (or a derivative of DAO) will be the path to the future.

Is there any support for it as if yet? ...and will it be DAO(derivative
doesn't imply identity)?

My comment was very specific. Your answer, on the other hand, was not
analogous.

Let me rephrase this...Support for DAO specifically is quickly fading.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1

[Adam Turner via AccessMonster.com]

David W. Fenton wrote:

Unfortunately, this means that if you want to know everything,
you have to learn the entire hotchpotch. But if you are working[quoted text clipped - 13 lines]

...although support for DAO is quickly fading

You're simply mistaken on that.

You are simply an imbecile.

--
Message posted via http://www.accessmonster.com

[Douglas J. Steele]

"Adam Turner via AccessMonster.com" <u14766@uwe> wrote in message
news:571fe8aace616@uwe...

Allen Browne wrote:

...although support for DAO is quickly fading

Not so, Adam.

If you have been reading the blog from Erik Rucker (Access development
team)
at:
http://blogs.msdn.com/access/
you know that Access 12 will have its own private version of the JET
engine.
That means that DAO (or a derivative of DAO) will be the path to the
future.

Is there any support for it as if yet? ...and will it be DAO(derivative
doesn't imply identity)?

AFAIK, it will be DAO. No, there's no support for it yet: it won't be
available until Office 12 is released next year.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

[lylefair]

DAO can't do JET 4.0 SQL now.
So will the new "DAO" do JET 4.0 SQL for backwards compatability?
And will the new "DAO" do the new JET (ACE)?

[Adam Turner via AccessMonster.com]

Douglas J. Steele wrote:

...although support for DAO is quickly fading

[quoted text clipped - 11 lines]

Is there any support for it as if yet? ...and will it be DAO(derivative
doesn't imply identity)?

AFAIK, it will be DAO. No, there's no support for it yet: it won't be
available until Office 12 is released next year.

Do you understand my point? I say...older Ford Mustang parts are getting hard
to find...you say..Not so...Ford published an article stating they're
introducing a new Mustang next year and the parts will be in abundance.

It doen't really justify your disagreement.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1

[jimfortune]

David W. Fenton wrote:

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:43***********************@per-qv1-newsreader-01.iinet.net.au:

Unfortunately, this means that if you want to know everything, you
have to learn the entire hotchpotch. But if you are working with
Access (JET) tables in an Access database (mdb), DAO is the native
Access library, and will therefore be the most natural approach.

And to clarify:

For those tasks which both DAO and ADO support, DAO will always be
superior to ADO. ADO is only useful for that tiny handful of tasks
that DAO does not support.

The only other reason to program ADO against Jet is if you're going
to upsize to SQL Server, and even then, it's not necessarily going
to be the most efficient approach.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

I agree with you. I don't think that coming up with something more
efficient than DAO was much of a motivation for MS. The prospect of
java making MS' OS's unnecessary was staring them in the face. The
idea of creating an Access application that can be plopped up on the
internet (without TS) is sort of a holy grail that can compete very
successfully with java long term. To me, .NET does not fulfill that
holy vision in its entirety, especially the RAD part. Neither does
java. ADO and DAP were simply the first steps in that quest. XML is
another step. Regardless of MS' faulting steps, the quest of RAD
web/intranet development using Access remains noble. This is one of
the reasons I delved into the pdf format. Access can take real-time
data and present it in a consistent browser viewable way. XML has
similar potential. I think Stephen's Access report to pdf is a great
contribution toward the ideal. I like the flexibility I have to go
beyond what Access reports give me with my own faulting steps at pdf;
but the pdf format is not the end of the story for me either,
especially the RAD part :-). I'm trying to keep my eyes on the goal.
I was disappointed that ADO in Access didn't go further than it did
toward reaching the goal. I agree with Allen that DAO is the most
natural way to interact with Jet. Apart from ADO's problems, do you
think it is a more natural way of interacting with SQL Server?

James A. Fortune

[Larry Linson]

<ji********@compumarc.com> wrote

Apart from ADO's problems, do you think it is a more
natural way of interacting with SQL Server?

I think ADO has already been superceded by ADO.NET which is not, as some
have claimed, an "evolutionary improvement," but quite different, not built
on the same object model, and subject to yet another learning curve. The
"classic ADO" only really hangs around in Office, and in _previous_ versions
of VB and other Visual Studio tools, not in the current VS.NET. It'll be
supported for a while, but I don't ever expect to see a "resurrection" as
seems to be happening with Jet/DAO -- I have no inside info to that effect,
just a "gut feel".

I always caution that my experience with ADPs and ADO is limited, but I did
not find it one bit easier, or "more natural", than I have found using MDB
and DAO with ODBC to interface with SQL Server (and other server databases
not supported by ADP and ADO).

Larry Linson
Microsoft Access MVP

[Lyle Fairfield]

ji********@compumarc.com wrote in news:1131588243.074717.277480
@o13g2000cwo.googlegroups.com:

I was disappointed that ADO in Access didn't go further than it did
toward reaching the goal.
What goal? Porting Access reports to PDF? You want a db connection
technology to port Access reports to PDF?
Apart from ADO's problems …

What problems?
************
THAT IT WILL RUN JET 40 SQL WHILE DAO WILL NOT?
************
That it connects to non-Jet DBs with one fewer interface levels than DAO?
That its Recordsets can be saved to text or xml with a simple command
(Save); that theses files can be opened as a recordset with another simple
command, (Open)?
That it will do Indexing Service?
That its Tran Methods allow easy bound but not really bound form editing
with a confirm for changes?
That it will do FTP and File System things?
That VBA pointers to ADO objects don't crash your computer?
That Access wizards don't create extra redundant ADO objects?
That its Schemas give you total information about your db?
That it can be used easily in other technologies?
That MS says on its VERY OWN website (not in a blog of one of its own
employees) that it will continue to be enhanced?

Are you wondering why ADO.Net was not named DAO.Net?

--
Lyle Fairfield

[jimfortune]

Lyle Fairfield wrote:

ji********@compumarc.com wrote in news:1131588243.074717.277480
@o13g2000cwo.googlegroups.com:

I was disappointed that ADO in Access didn't go further than it did
toward reaching the goal.
What goal? Porting Access reports to PDF? You want a db connection
technology to port Access reports to PDF?

I made it clear that Access reports to PDF was only a step toward the
goal of RAD web/intranet development using Access. The ability of PDF
files to be viewed in browsers in a device independent manner is the
reason I consider them a step toward the goal.

Apart from ADO's problems ...
What problems?
************
THAT IT WILL RUN JET 40 SQL WHILE DAO WILL NOT?
************
That it connects to non-Jet DBs with one fewer interface levels than DAO?
That its Recordsets can be saved to text or xml with a simple command
(Save); that theses files can be opened as a recordset with another simple
command, (Open)?
That it will do Indexing Service?
That its Tran Methods allow easy bound but not really bound form editing
with a confirm for changes?
That it will do FTP and File System things?
That VBA pointers to ADO objects don't crash your computer?
That Access wizards don't create extra redundant ADO objects?
That its Schemas give you total information about your db?
That it can be used easily in other technologies?
That MS says on its VERY OWN website (not in a blog of one of its own
employees) that it will continue to be enhanced?

Those aren't problems :-). I've not had any problems with ADO but was
thinking about the concerns you have raised in other posts such as
implementing security and all the other "gotchas" you had to deal with.

Are you wondering why ADO.Net was not named DAO.Net?
I'm not trying to take sides in the ADO vs. DAO war. I believe that
the problems you discovered with ADO will be fixed eventually and that
ADO will supply everything that DAO does plus more, although less
efficiently in some situations :-). I appreciate the new ADO features
and what MS is trying to accomplish with ADO.

--
Lyle Fairfield

James A. Fortune

[lylefair]

James

You are confusing ADP and ADO, I think. I have discovered no problems
with ADO.

Lyle

[jimfortune]

lylefair wrote:

James

You are confusing ADP and ADO, I think. I have discovered no problems
with ADO.

Lyle

Yes, that's right. I stand corrected. My mind tends to blur the
distinction between the two. Thanks for clarifying.

James A. Fortune

[david epsom dot com dot au]

This probably isn't exactly what either of you are talking
about, but look at the methods of

application.CurrentProject
and
application.CurrentDB

From a DB you can open a Recordset.
From a Project, (or from a Project.Connection,) you cannot.

DAO has a simple hierarchical object model.
ADO has a number of intersecting objects.

DAO holds you by the hand and leads you from what you
know towards what you want.

The DAO object model is influenced by the same kind of
thinking that gave us BASIC, (the computer helping the
user), and, to a lesser extent Pascal (tasks allocated
between the user and the computer according to which
task the user and computer find most difficult, and/or
efficient).

On the other hand, the ADO object model is the kind of
model you would get if you gave the task to a computer
programmer instead of a language designer.

The ADO objects are designed for good internal cohesion
and reduced internal coupling, not usability.

The outcome is a set of ADO objects that (in my experience)
many good programmers (object designers, C++ programmers)
actually feel more comfortable with than DAO objects,
mostly because the mechanics of using the ADO objects
is similar to the mechanics of using in-house objects,
built by (themselves or their team).

But which are less useful for developers who are not
personally immersed in object development.

(david)

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:43***********************@per-qv1-newsreader-01.iinet.net.au:

Unfortunately, this means that if you want to know everything, you
have to learn the entire hotchpotch. But if you are working with
Access (JET) tables in an Access database (mdb), DAO is the native
Access library, and will therefore be the most natural approach.

And to clarify:

For those tasks which both DAO and ADO support, DAO will always be
superior to ADO. ADO is only useful for that tiny handful of tasks
that DAO does not support.

The only other reason to program ADO against Jet is if you're going
to upsize to SQL Server, and even then, it's not necessarily going
to be the most efficient approach.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[RoyVidar]

david epsom dot com dot au wrote in message
<43***********************@lon-reader.news.telstra.net> :

This probably isn't exactly what either of you are talking
about, but look at the methods of

application.CurrentProject
and
application.CurrentDB

From a DB you can open a Recordset.
From a Project, (or from a Project.Connection,) you cannot.

[snip]

Probably not very relevant in the discussion, but do you mean
something like the following isn't/shouldn't be possible

dim rs as dao.recordset
set rs = currentdb.openrecordset("<some select>")

dim rs2 as adodb.recordset
set rs2 = currentproject.connection.execute("<some select>")

I think the difference being the ADO recordset defaults to
readonly forwardonly.

--
Roy-Vidar

[lylefair]

The CursorType of the Recordset will vary and may be influenced by the
CursorLocation and the Provider. Even when the CursorType is specified
it may not be available under the conditions of opening and may revert
to something other than that stipulated. In manipulating data with
Access, I expect we use a Client Side Cursor; with JET a Client Side
Cursor, (TTBOMK) defaults to adOpenStatic. If we specify AdOpenKeySet
we still get adOpenStatic, suggesting that JET may not make
adOpenKeySet available, although it's possible there are other reasons
which I have not explored. My experience is that MS-SQL works the same
way.

[RoyVidar]

lylefair wrote in message
<11**********************@g43g2000cwa.googlegroups .com> :

The CursorType of the Recordset will vary and may be influenced by the
CursorLocation and the Provider. Even when the CursorType is specified
it may not be available under the conditions of opening and may revert
to something other than that stipulated. In manipulating data with
Access, I expect we use a Client Side Cursor; with JET a Client Side
Cursor, (TTBOMK) defaults to adOpenStatic. If we specify AdOpenKeySet
we still get adOpenStatic, suggesting that JET may not make
adOpenKeySet available, although it's possible there are other reasons
which I have not explored. My experience is that MS-SQL works the same
way.

dim rs2 as adodb.recordset
set rs2 = currentproject.connection.execute("<some select>")
debug.print rs2.cursorlocation,rs2.cursortype, rs2.locktype
debug.print aduseserver, adopenforwardonly, adlockreadonly

opening a recordset like this through an .execute on a connection, has
given
me forwardonly readonly the times I've checked (it's also what the help
file
claims this method should return).

Help files also claims something quite similar for the .Open method:
"The default cursor for an ADO Recordset is a forward-only, read-only
cursor
on the server"

- unless otherwise specified or coerced, of course;-)

--
Roy-Vidar