Acc2002 equivalent---for each fld in tdf.fieds

[BF]

Hello,

I got this code from Dirk Goldgar (thank you very much)
for looping through a table collection in Access2002

Dim ao As AccessObject

For Each ao In CurrentData.AllTables
Debug.Print ao.Name
Next ao

where before in Acc97 I was using a DAO database and
tabledef object. Dirk also explained that I could make a
reference to DAO 3.6 and still use the methods from
Acc97. My question is if there is an Access2002 method
for looping through the fields in a tabledef object?

I was experimenting with the AccessObject datatype but
can't figure out how to access a field object. I can
still access a field object with DAO 3.6 using the Acc97
method

Dim DB..., tdf, fld
....
For Each fld In tdf.Fields...

I am just hoping to migrate my code to Access2002 version
of coding where applicable.

Thanks

 

[BF]

Well, here is something that I tried, but still open for
suggestions if there is a more efficient way to to this in
Acc2002:

Dim conn As ADODB.Connection
Dim RS As ADODB.Recordset, fld As ADODB.Field

Set conn = CurrentProject.Connection
Set RS = New ADODB.Recordset

RS.Open "tblx", conn, , , adCmdTable

For Each fld In RS.Fields
Debug.Print fld.Name
Next

This works, but is there any advantage between using ADODB
vs DAO here? I am just asking for edification. If the
only advantage is just consistency with Access 2002 which
ADO is default vs DAO that is good enough for me.

 

[Dirk Goldgar]

Hello,

I got this code from Dirk Goldgar (thank you very much)
for looping through a table collection in Access2002

Dim ao As AccessObject

For Each ao In CurrentData.AllTables
Debug.Print ao.Name
Next ao

where before in Acc97 I was using a DAO database and
tabledef object. Dirk also explained that I could make a
reference to DAO 3.6 and still use the methods from
Acc97. My question is if there is an Access2002 method
for looping through the fields in a tabledef object?

I was experimenting with the AccessObject datatype but
can't figure out how to access a field object. I can
still access a field object with DAO 3.6 using the Acc97
method

Dim DB..., tdf, fld
...
For Each fld In tdf.Fields...

I am just hoping to migrate my code to Access2002 version
of coding where applicable.

Thanks

DAO *is* the Access 2002 way of doing this. The various collections of
AccessObjects -- AllTables, AllForms, AllReports, etc. -- that were
introduced with Access 2000 have limited uses; primarily these1)
enumerating the members of the collections, (2) determining whether an
object exists in the database, and (3) finding out whether an object is
currently loaded, and if so, in what view.

DAO did not become obsolete with Access 2000. I do think that MS
originally thought DAO would become obsolete, because they thought (at
the time) that ADPs -- interfacing directly with SQL Server -- would
replace MDBs, and DAO is optimized for work with Jet databases; that
is, with the MDB file format. They've change their tune now, and are
recognizing the proven value of Jet databases. They're even suggesting
that an MDB file with linked SQL-Server tables may have advatages over
an ADP file. In light of thise, they've restored the default DAO
reference in Access 2003.

Anyone but the most die-hard ADO partisan will tell you that DAO is
better for work with Jet databases than ADO -- more efficient and more
powerful. If you still want to use ADO for working with tables and
fields as objects, you'll need to use ADOX in conjunction with ADO.
ADOX is short for the Microsoft ADO Extensions for DDL and Security,
which shows up in my references dialog as "Microsoft ADO Ext. 2.7 for
DDL and Security". ADOX provides various objects similar to those
provided by DAO, but more generalized. There are properties you can
read and set for DAO TableDef and Field objects (in an MDB) that you
can't get at with ADOX.

 

[Brian]

Well, here is something that I tried, but still open for
suggestions if there is a more efficient way to to this in
Acc2002:

Dim conn As ADODB.Connection
Dim RS As ADODB.Recordset, fld As ADODB.Field

Set conn = CurrentProject.Connection
Set RS = New ADODB.Recordset

RS.Open "tblx", conn, , , adCmdTable

For Each fld In RS.Fields
Debug.Print fld.Name
Next

This works, but is there any advantage between using ADODB
vs DAO here? I am just asking for edification. If the
only advantage is just consistency with Access 2002 which
ADO is default vs DAO that is good enough for me.

Don't be misled by ADO being the "default" in Access 2002. I once tried to
use ADO with Jet, but I soon gave up, it really doesn't work anything like
as well as DAO. Just reference DAO, and use your existing code. If you
don't like the idea of lagging behind the "bleeding edge" of technology,
just remind yourself that ADO is old hat now, the new thing is ADO.Net
(which shares nothing with ADO except the letters "ADO"). So, if you are
going to use an "obsolete" technology then you may as well use the best one
for the job which, when using Jet, is DAO.

 

[BF]

Wow! Thanks all for your replies. My thing is that I
have a nasty habit of re-inventing the wheel a lot of
times. So I thought I would check to see if this was one
of those times. I guess not. Yes, I prefer com ADO when
interfacing with Sql Server over ODBC (having a problem
right now with ODBC). But I am glad to know that DAO is
still a mainstay with Access/Jet. Yes, of course ADO.Net
is even nicer with Sql and OLEDB, use it all the time in
VB.Net apps.

Well, thanks all for re-assuring me that I am not re-
inventing the wheel by sticking with DAO when using Jet.

 

[david epsom dot com dot au]

You don't need the DAO type library to do this -
all the methods are still there in Access:

dim obj as object

for each obj in application.currentdb.Tabledefs
debug.print obj.Name
next obj

You don't need to use the fully qualified name,
'Application' is assumed, so you can just write:

for each obj in CurrentDB.TableDefs
debug.print obj.Name
next obj


(david)

 

[Brian]

Wow! Thanks all for your replies. My thing is that I
have a nasty habit of re-inventing the wheel a lot of
times. So I thought I would check to see if this was one
of those times. I guess not. Yes, I prefer com ADO when
interfacing with Sql Server over ODBC (having a problem
right now with ODBC). But I am glad to know that DAO is
still a mainstay with Access/Jet. Yes, of course ADO.Net
is even nicer with Sql and OLEDB, use it all the time in
VB.Net apps.

Well, thanks all for re-assuring me that I am not re-
inventing the wheel by sticking with DAO when using Jet.

If you are using ODBC linked tables then you are still using Jet, and DAO
still works better. For example, using transaction protection with Jet,
through ADO, is quite staggeringly slow (like, 1 minute to commit a
transaction), whereas it works fine with DAO.