ACC2000: Unexpected Behavior Referencing Properties and Methods of an Object

ID: Q223212

The information in this article applies to:

Microsoft Access 2000

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

SYMPTOMS

When you run ActiveX Data Objects (ADO) code that references properties or methods of an object, you receive either unexpected results or the following error message:

Run-time error '3265':

ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application.

CAUSE

The object has not yet been added as a member of the collection.

RESOLUTION

Avoid referencing properties and methods of objects that have not yet been added to their collections.

For example, the "Sample of Unexpected Results" example in the "More Information" section of this article contains the following:


'Count the number of properties of the MyID field BEFORE adding
'MyNewTable to the tables collection.
lngCount = tbl.Columns("MyID").Properties.Count

'Display the number of properties found.
Debug.Print "Number of properties BEFORE: " & lngCount

'Add the new table to the collection.
cat.Tables.Append tbl

'Count the number of properties after adding MyNewTable to collection.
lngCount = tbl.Columns("MyID").Properties.Count

'Display the number of properties found.
Debug.Print " Number of Properties AFTER: " & lngCount

In this example, avoid unexpected results by not referencing the Count property prior to adding the table to the collection. In other words, remove the code:


'Count the number of properties of the MyID field BEFORE adding
'MyNewTable to the tables collection.
lngCount = tbl.Columns("MyID").Properties.Count

'Display the number of properties found.
Debug.Print "Number of properties BEFORE: " & lngCount

The "Sample of Unexpected Error" example in the "More Information" section of this article contains the following:


'Display the name of the first property for MyID.
Debug.Print tbl.Columns("MyID").Properties.Item(0).Name

'Add the new table to the Tables collection.
cat.Tables.Append tbl

Avoid the error message by not referencing the Name property prior to adding the table to the collection. In other words, remove the code:


'Display the name of the first property for MyID.
Debug.Print tbl.Columns("MyID").Properties.Item(0).Name

MORE INFORMATION

If you programmatically reference an object's properties or methods prior to saving that object as a member of a collection, you receive either a run-time error, or the values returned are not accurate. During execution of this procedure, if you reference properties and methods prior to appending the object to its appropriate collection, and then you reference them again after appending the object to its collection, the initial values returned by these properties and methods will be returned again.

Setting a Parent property equal to the appended object or refreshing the collection itself does not resolve the issue.

Steps to Reproduce Behavior

The following two sample code snippets illustrate this behavior. The first sample shows the unexpected results that can occur. The second sample shows the error message that can occur.

Sample of Unexpected Results

Open a blank new database.

In the Database window, click Modules under Objects, and then click New.

On the Tools menu, click References.

In the Available References box, make sure that the Microsoft ActiveX Data Objects 2.1 Library and the Microsoft ADO Ext. 2.1 for DDL and Security are selected (checked).

Click OK to close the References - <dbname> dialog box.

Type the following procedure:


Sub Unexpected_Results()
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Sample procedure to show the unexpected results that can occur when
    'referencing properties of an unsaved object.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim tbl As New ADOX.Table
    Dim cat As New ADOX.Catalog
    Dim lngCount As Long

    'Establish a connection to the blank new database currently opened.
    Set cat.ActiveConnection = CurrentProject.Connection
    
    'Specify a new table name, and its field and data-type.
    tbl.Name = "MyNewTable"
    tbl.Columns.Append "MyID", adInteger
    
    'Count the number of properties of the MyID field BEFORE adding
    'MyNewTable to the tables collection.
    lngCount = tbl.Columns("MyID").Properties.Count
    
    'Display the number of properties found.
    Debug.Print "Number of properties BEFORE: " & lngCount

    'Add the new table to the collection.
    cat.Tables.Append tbl
    
    'Count the number of properties after adding MyNewTable to collection.
    lngCount = tbl.Columns("MyID").Properties.Count

    'Display the number of properties found.
    Debug.Print " Number of Properties AFTER: " & lngCount
End Sub

On the Debug menu, click Compile <dbname>.

On the View menu, click Immediate Window.

In the Immediate Window, type the following, and then press ENTER:


Unexpected_Results

Note the results that are returned:
Number of properties BEFORE: 0
Number of Properties AFTER: 0
NOTE: Adding the following two lines of code just before the final Debug.Print line does not resolve the problem:


tbl.ParentCatalog.Tables.Refresh
tbl.Columns("MyID").Properties.Refresh

Sample of Unexpected Error

Open a blank new database.

In the Database window, click Modules under Objects, and then click New.

On the Tools menu, click References.

In the Available References box, click to select the Microsoft ADO Ext. 2.1 for DDL and Security check box.

Click OK to close the References - <dbname> dialog box.

Type the following procedure:


Sub Unexpected_Error()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Sample procedure to show the unexpected error that occurs when
    'referencing properties or methods of an unsaved object.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim tbl As New ADOX.Table
    Dim cat As New ADOX.Catalog
    
    'Establish a connection to the blank new database currently opened.
    Set cat.ActiveConnection = CurrentProject.Connection
    
    'Specify a new table name, and its field and data-type.
    tbl.Name = "MyNewTable"
    tbl.Columns.Append "MyID", adInteger
    
    'Display the name of the first property for MyID.
    Debug.Print tbl.Columns("MyID").Properties.Item(0).Name
    
    'Add the new table to the Tables collection.
    cat.Tables.Append tbl
End Sub

On the Debug menu, click Compile <dbname>.

On the View menu, click Immediate Window.

In the Immediate Window, type the following, and then press ENTER:


Unexpected_Error

Note that you receive the error message described in the "Symptoms" section of this article.

Additional query words: pra prb

Keywords : kbdta AccCon
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb