What DAO do , Can ADO also do ?

[Albert]

Hello experts,

I have coded by using DAO since I start using Access and start using ADO for
a few months. With the subject "How to lock the whole table using ADO ?" I
just posted in microsoft.public.access.modulesdaovba.ado, It make me wonder
that Can ADO do everything that DAO can ?

I believe that ADO can. But with the problem I'm having, It may have a
trick/technique to solve in ADO way. Oh! forget to tell you that I access
data in .MDB.

TIA,
Albert

 

[Allen Browne]

DAO can do many things that ADO cannot.

That's why there is an ADOX library as well, which tries to extend the
functionality of ADO and handle the data structure (schema) as well.
However, ADOX is buggy and unreliable, so it depends on which version is
installed and even with the latest version some things do not work.

Some things cannot be done at all with the ADO/ADOX combination, such as
setting the Format property of a field in a table, or setting a YesNo
field's DisplayControl to a check box.

Unfortunately, Microsoft has not updated the DAO library to give it the new
functionality in JET 4. For example, it cannot create a field of type
Decimal, handle cascade-to-null relations, or resize an existing field.
These can only be done through ADOX or DDL statements executed under ADO.
Example to increase the size of a text field to 100 characters:
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(100);"
CurrentProject.Connection.Execute strSql

So, at this stage, DAO is the best library to use with JET databases (mdb
files), but you need to learn a little ADO/ADOX to be able to do everything.

 

[Dirk Goldgar]

DAO can do many things that ADO cannot.

That's why there is an ADOX library as well, which tries to extend the
functionality of ADO and handle the data structure (schema) as well.
However, ADOX is buggy and unreliable, so it depends on which version
is installed and even with the latest version some things do not work.

Some things cannot be done at all with the ADO/ADOX combination, such
as setting the Format property of a field in a table, or setting a
YesNo field's DisplayControl to a check box.

Unfortunately, Microsoft has not updated the DAO library to give it
the new functionality in JET 4. For example, it cannot create a field
of type Decimal, handle cascade-to-null relations, or resize an
existing field. These can only be done through ADOX or DDL statements
executed under ADO. Example to increase the size of a text field to
100 characters: strSql = "ALTER TABLE MyTable ALTER COLUMN
MyField TEXT(100);" CurrentProject.Connection.Execute strSql

So, at this stage, DAO is the best library to use with JET databases
(mdb files), but you need to learn a little ADO/ADOX to be able to do
everything.

Although I agree with the bulk of your reply, Allen, I don't quite
follow this last bit. The SQL statement you give as an example can be
executed as readily via DAO as ADO:

strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(100);"
CurrentDb.Execute strSql

 

[Allen Browne]

[snip] only be done through ADOX or DDL statements
executed under ADO. Example to increase the size of a text field to
100 characters: strSql = "ALTER TABLE MyTable ALTER COLUMN
MyField TEXT(100);" CurrentProject.Connection.Execute strSql

Although I agree with the bulk of your reply, Allen, I don't quite
follow this last bit. The SQL statement you give as an example can be
executed as readily via DAO as ADO

Darn, Dirk. You just destroyed a good argument for ADO.
You're right, of course.

So, an example of ADO-specific JET 4 features?
Setting properties such as Default Value when creating a table:

strSql = "CREATE TABLE tblReceipt (Amount CURRENCY NOT NULL DEFAULT 0, Item
TEXT(100) WITH COMP NOT NULL DEFAULT 'Cash Sale');"

 

[david epsom dot com dot au]

Cripes! How long has this been going on? MichKa told us in 2001
that it didn't work, and I haven't revisited it since then:

Mark Martel, Can't change text field size using DAO; can't specify size
using ALTER TABLE SQL
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=urNAEyAPBHA.2048@tkmsftngp05


(david)

[snip] only be done through ADOX or DDL statements
executed under ADO. Example to increase the size of a text field to
100 characters: strSql = "ALTER TABLE MyTable ALTER COLUMN
MyField TEXT(100);" CurrentProject.Connection.Execute strSql

Although I agree with the bulk of your reply, Allen, I don't quite
follow this last bit. The SQL statement you give as an example can be
executed as readily via DAO as ADO

Darn, Dirk. You just destroyed a good argument for ADO.
You're right, of course.

So, an example of ADO-specific JET 4 features?
Setting properties such as Default Value when creating a table:

strSql = "CREATE TABLE tblReceipt (Amount CURRENCY NOT NULL DEFAULT 0, Item
TEXT(100) WITH COMP NOT NULL DEFAULT 'Cash Sale');"

 

[Chris]

I know this doesn't really count, just an FYI, the Form's
RecordsetClone property returns a (Access 97-XP, don't
know about 2003) DAO Recordset.


Chris

-----Original Message-----
DAO can do many things that ADO cannot.

That's why there is an ADOX library as well, which tries to extend the
functionality of ADO and handle the data structure (schema) as well.
However, ADOX is buggy and unreliable, so it depends on which version is
installed and even with the latest version some things do not work.

Some things cannot be done at all with the ADO/ADOX combination, such as
setting the Format property of a field in a table, or setting a YesNo
field's DisplayControl to a check box.

Unfortunately, Microsoft has not updated the DAO library to give it the new
functionality in JET 4. For example, it cannot create a field of type
Decimal, handle cascade-to-null relations, or resize an existing field.
These can only be done through ADOX or DDL statements executed under ADO.
Example to increase the size of a text field to 100 characters:
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(100);"
CurrentProject.Connection.Execute strSql

So, at this stage, DAO is the best library to use with JET databases (mdb
files), but you need to learn a little ADO/ADOX to be able to do everything.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

and start using ADO
for

microsoft.public.access.modulesdaovba.ado, It make me

 

[Fred Boer]

Sorry, but this thread's subject has given me a disturbing, recurring
image...

I see Ethel Merman on a Broadway stage, belting out the big hit number from
"Annie Get Your ADH"... And it goes: "Anything A-D-O, D-A-O better, D-A-O
anything better than you... <g>

Fred Boer (with apologies in advance....)

 

[Dirk Goldgar]

Sorry, but this thread's subject has given me a disturbing, recurring
image...

I see Ethel Merman on a Broadway stage, belting out the big hit
number from "Annie Get Your ADH"... And it goes: "Anything A-D-O,
D-A-O better, D-A-O anything better than you... <g>

LOL!