AccXP TableDef how to loop through collection?

[BF]

Hello,

I just got upgraded from Access97 to AccessXP(2002). I
have to start converting several Applications now. In
Acc97 I could loop through the tabledef collection like
this

Dim DB As Database, tdf As TableDef
Set DB = CurrentDB
For Each tdf In DB.TableDefs
Debug.Print tdf.Name
Next

I could probably make a reference to the Access8.0 object
library and still do this. But I would like to migrate my
code to use the Access10.0 object library. How could I
accomplish my loop above in Access10?

Thanks

 

[Dirk Goldgar]

Hello,

I just got upgraded from Access97 to AccessXP(2002). I
have to start converting several Applications now. In
Acc97 I could loop through the tabledef collection like
this

Dim DB As Database, tdf As TableDef
Set DB = CurrentDB
For Each tdf In DB.TableDefs
Debug.Print tdf.Name
Next

I could probably make a reference to the Access8.0 object
library and still do this. But I would like to migrate my
code to use the Access10.0 object library. How could I
accomplish my loop above in Access10?

It's not the Access object library that you're using here, it's the DAO
object library. But Access 2002 doesn't have that reference enabled by
default. Open the Tools -> References... dialog, locate the reference
to the Microsoft DAO 3.6 Object Library, and put a check-mark in the box
beside it. While you're at it, you'll probably find life simpler if you
remove the check-mark next to Microsoft ActiveX Data Objects 2.x
Library, also known as the ADO library. ADO contains some of the same
object names as DAO, so de-selecting it (unless you plan to use it) will
prevent confusion.

 

[Dirk Goldgar]

Hello,

I just got upgraded from Access97 to AccessXP(2002). I
have to start converting several Applications now. In
Acc97 I could loop through the tabledef collection like
this

Dim DB As Database, tdf As TableDef
Set DB = CurrentDB
For Each tdf In DB.TableDefs
Debug.Print tdf.Name
Next

I could probably make a reference to the Access8.0 object
library and still do this. But I would like to migrate my
code to use the Access10.0 object library. How could I
accomplish my loop above in Access10?

I should have added that, with Access 2002, you can also do the same
thing as the above code without actually using the TableDefs collection,
because Access now has something similar built in:

Dim ao As AccessObject

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

However, you can't do the same sorts of things with an AccessObject that
you can with a TableDef.

 

[BF]

Thanks for your reply. Yes, I went on google and
discovered that I have to make a reference to DAO3.6.
That sort of did the trick. And I do use ADO
extensively. But by default I noticed that ADO2.1 is
selected. I generally use ADO2.6. I work with Sql Server
2k a lot. So I guess I need to get used to using
CurrentProject now.

Well, thanks again for your reply.

BF

 

[Dirk Goldgar]

Thanks for your reply. Yes, I went on google and
discovered that I have to make a reference to DAO3.6.
That sort of did the trick. And I do use ADO
extensively. But by default I noticed that ADO2.1 is
selected. I generally use ADO2.6. I work with Sql Server
2k a lot. So I guess I need to get used to using
CurrentProject now.

You can certainly use both DAO and ADO in your project if you want to.
You just have to be sure to qualify the declarations of the objects the
libraries have in common with either "DAO." or "ADODB.". Like this:

Dim rs As DAO.Recordset
Dim rsADO As ADODB.Recordset
Dim fld As DAO.Field
Dim fldADO As ADODB.Field

The objects in common are: Connection, Error, Errors, Field, Fields,
Parameter, Parameters, Property, Properties and Recordset.

Note: the following objects exist with the same names in the ADOX and
DAO models as well: Group, Groups, Index, Indexes, Property,
Properties, User, Users.

I recommend just making a habit of explicitly identifying the library
when declaring any object from those libraries. It saves remembering.

If you have a reference to ADO 2.6 available to you, I don't see any
reason not to uncheck the ADO 2.1 reference and check the ADO 2.6
reference.

 

[BF]

Thanks. That is what I was looking for - the
AccessObject datatype. Much easier than cycling through
a tabledefs collection.

Many thanks for your help,
BF