Enumerate a collection?

[MikeB]

I hope that's even the correct term. if I have a collection and I want
to see what's (and how many) items are in the collection, how do I go
about it?

Specifically, I'm messing about with DAO and there's a DBEngine Object
that in turn has Workspaces that has Databases that has Tabledefs that
has Fields. Just to get my bearings, I'd like to be able to list each
of these things to see what is in each collection.

Ideally, I'd like to programatically map out the name of each
collection in DBEngine, then the items in each sub-collection and so
on.

Thanks.

 

[John W. Vinson]

I hope that's even the correct term. if I have a collection and I want
to see what's (and how many) items are in the collection, how do I go
about it?

IME practially every collection has a .Count property; and you should be able
to use

FOR EACH <whatever> IN <collection>

to loop through them.

 

[Klatuu]

As an example using the tables in a database:

Dim tdf As TableDef

For each tdf In CurrentDb.TableDefs
Debug.Print tdf.Name
Next tdf

 

[MikeB]

thanks to both Klatuu and John Vinson, that's very helpful

If I understand DAO correctly, there is always only ever a single
DBEngine object and one cannot create another.

I enter
Debug.Print "Name of DBEngine objects: " & DBEngine(0).Name()

and get

Name of DBEngine objects: #Default Workspace#

So does the DBengine have the same name as the Workspace? If I
understand it correctly, I should be able to create additional
workspaces. (Ihaven't figured out yet why I might want to).

But if I try to enumerate the workspaces in DBEngine(0), I get errors.
This is the code I've tried:

Dim wkspc as Workspace
or
Dim wkspc as DBEngine.Workspace

Both fail to autocomplete, leading me to believe that that is
syntactically wrong.

I can get this to work

Debug.Print DBEngine.Workspaces.Count

and it autocompletes differently if I type
Debug.Print DBengine(0).

So any explanation would be helpful.

I do have the Wrox programming book Access 2007 VBA Programmers
Reference, but I'm not getting my head around these things.

Thanks again.

 

[David W. Fenton]

m:

If I understand DAO correctly, there is always only ever a single
DBEngine object and one cannot create another.

I enter
Debug.Print "Name of DBEngine objects: " & DBEngine(0).Name()

and get

Name of DBEngine objects: #Default Workspace#

So does the DBengine have the same name as the Workspace? If I
understand it correctly, I should be able to create additional
workspaces. (Ihaven't figured out yet why I might want to).

DBEngine(0) is short for DBEngine.Workspaces(0), so you are, in
fact, operating on a collection.

DBEngine(0)(0) is short for DBEngine.Workspaces(0).Databases(0).

Any time you've got an index number (which is what the 0 is), you've
got a collection.

But if I try to enumerate the workspaces in DBEngine(0), I get
errors. This is the code I've tried:

Dim wkspc as Workspace
or
Dim wkspc as DBEngine.Workspace

Both fail to autocomplete, leading me to believe that that is
syntactically wrong.

The first one should work -- it's a child of DAO.

You should learn how to use the Object Browser -- hit F2 while in
the VBE and type "workspace" into the search box.

I can get this to work

Debug.Print DBEngine.Workspaces.Count

and it autocompletes differently if I type
Debug.Print DBengine(0).

So any explanation would be helpful.

You stumbled over the shortcut versions of the DBEngine collections,
where Workspaces is the default collection of the DBEngine object,
and Databases the default collection of each workspace object.

I do have the Wrox programming book Access 2007 VBA Programmers
Reference, but I'm not getting my head around these things.

You could benefit from studying the DAO object model, I think.

 

[Dirk Goldgar]

thanks to both Klatuu and John Vinson, that's very helpful

If I understand DAO correctly, there is always only ever a single
DBEngine object and one cannot create another.

I enter
Debug.Print "Name of DBEngine objects: " & DBEngine(0).Name()

and get

Name of DBEngine objects: #Default Workspace#

So does the DBengine have the same name as the Workspace? If I
understand it correctly, I should be able to create additional
workspaces. (Ihaven't figured out yet why I might want to).

But if I try to enumerate the workspaces in DBEngine(0), I get errors.

There are no Workspaces in DBEngine(0), because DBEngine(0) is not a
DBEngine object, it's a Workspace object. Notice that there is no DBEngines
collection (that is, no plural of DBEngine), because there is only one
DBEngine and it's not a member of a collection.

"DBEngine(0)" is short for "DBEngine.Workspaces(0)" -- a reference to the
first Workspace in the DBEngine object's Workspaces collection.

I can get this to work

Debug.Print DBEngine.Workspaces.Count

and it autocompletes differently if I type
Debug.Print DBengine(0).

Right. "DBEngine.Workspaces." will autocomplete to offer the methods and
properties of the DBEngine.Workspaces collection, while "DBengine(0)." will
autocomplete to offer the methods and properties of the Workspace object.

 

[MikeB]

Thanks for all the help and explanations so far, I'm making progress.

I've got this code to work now.

Sub ExploreDAO()
Debug.Print "Hello Mike. Today is " & Date & " " & Time()
Debug.Print "Enumerate DBEngine:"
Debug.Print "Number of Workspaces: " & DBEngine.Workspaces.Count()

Dim wkspcctr As Integer
wkspcctr = 0
Dim wkspc As Workspace
For Each wkspc In DBEngine.Workspaces
wkspcctr = wkspcctr + 1
Debug.Print "Workspace " & wkspcctr & ":" & wkspc.Name

Debug.Print "Number of databases: " & wkspc.Databases.Count()
Dim dbctr As Integer
dbctr = 0
Dim db As Database
For Each db In wkspc.Databases
dbctr = dbctr + 1
Debug.Print "Database " & dbctr & ":" & db.Name
Next db
Next wkspc

Debug.Print "End of Run."
End Sub

Now I have a new, different problem. If I try to enumerate the Groups
or Users in the Workspace,

with this code

==> Debug.Print "Number of Groups: " & wkspc.Groups.Count
'Dim grpctr As Integer
'grpctr = 0
'Dim grp As Group
'For Each grp In wkspc.Groups
' grpctr = grpctr + 1
' Debug.Print "Group " & grpctr & ":" & grp.Name
' Next grp


I get this error:

Run-time error: 91
Object variable or With Block variable not set.

Now I'm assuming that this is because there are no groups in the
default workspace collection.

I've looked at the methods for the Workspace object and I cannot
figure out a way to ask it what objects are in its collection. So how
do I write a procedure that can safely determine whether there are
Groups (and Users) in the Workspace collection before attempting to
enumerate them?

Thanks again.

 

[MikeB]

Thanks! I just came here to apologize for asking this since I just
discovered that I made a wrong placement as well.

Next question coming up as soon as I've finished struggling with
it