[AccessD] When is a collection not a collection?

Heenan, Lambert Lambert.Heenan at aig.com
Tue Oct 25 13:38:38 CDT 2005


Isn't this just a simple case of trying to assign one object to another of
an incompatible type? 

The DAO Properties collection is a class in the DAO library, and as you
said, it has a set of member methods (Append, Delete and Refresh) as well as
the Count and Item properties).

The VBA library has other class "Collection" which has the methods Add,
Item, Count, and Remove. 

These two classes are quite different from each other, and do not have any
inherited relationship (even if access had inheritance), so you cannot
assign one to the other. So as you said from the outset:

The DAO Properties collection is not a VBA Collection, just as a Form is not
a Report is not a Collection. But I don't see where you conclude that "you
can dim a collection and save a pointer to these object collections in your
collection". In the code below all three Set lines cause a Type Mismatch
error in Access 2002.


Dim db As Database
Dim Col As Collection
    
    Set db = CurrentDb
    With db
        Set Col = .TableDefs
        Set Col = .QueryDefs
        Set Col = .Properties
    End With

However code like this will work fine....

Dim db As Database
Dim Col As Collection
Dim Obj As Object
Dim n As Long

    Set db = CurrentDb
    Set Col = New Collection
    With db
        Col.Add .TableDefs
        Col.Add .QueryDefs
        Col.Add .Properties
    End With
    
    For Each Obj In Col
    With Obj
        For n = 0 To .Count - 1
            Debug.Print .Item(n).Name
        Next n
    End With
    Next Obj

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Tuesday, October 25, 2005 1:41 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] When is a collection not a collection?


When it is a properties collection?

Try dimming a collection

Dim lcol as collection

Then try setting that collection to any DAO object's properties collection:

	set lcol = MyFld.Properties

Or 	
	set lcol = MyTDF.Properties

You will get a run time error: "Type Mismatch"

Sigh.

AFAICT, all of the other object collections in the database container - the
tabledefs, fields, forms etc are all true collections, i.e. you can dim a
collection and save a pointer to these object collections in your
collection.  Not so with the Properties collection of any object.

Sigh.

The properties collection has different properties and methods:

Append 
Count 
Delete
Refresh

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list