ACC2000: Using the ADOX Catalog Object on a Table Object Erases Field Properties of Each Query That Uses That Table

After you use a Table object with the ADOX Catalog object in a Visual Basic for Applications procedure, you notice that the field properties of one or more queries in your database are gone.

Using the ADOX Catalog object on a Table object erases the field properties of each query that uses that table.

To resolve this problem, obtain Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a).

To obtain SR-1/SR-1a, click the article number below to view the article in the Microsoft Knowledge Base: To temporarily work around this problem, open each query in Design view, and then re-enter the needed properties. Try to avoid using the ADOX Catalog object with a Table object in Microsoft Access databases.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was corrected in Microsoft Office 2000 SR-1/SR-1a.

Steps to Reproduce Behavior

1. In the Database window, click Tables under Objects, click New, click Design View, and then click OK.
2. Add a field named Field1 with a data type of Text.
3. Close the table, and when prompted, save it as Table1.
4. When prompted to create a primary key, click Yes to allow Microsoft Access to add the primary key.
5. In the Database window, click Queries under Objects, click New, click Design View, and then click OK.
6. In the query design grid, add the Table1 table.
7. Add the two fields (the AutoNumber field and the Field1 field) to the query design grid.
8. Right-click the Field1 field in the query design grid, and then click Properties.
9. On the General tab, type some text for the Description, Format, InputMask, and Caption properties.
   
   NOTE: It is not important what you type for these properties as long as the entries are valid.
10. Save the query as Query1, and then close it.
11. In the Database window, click Modules under Objects, and then click New.
12. Type the following code into the module:

    Sub DropFieldProperties()
    
      Dim cat As ADOX.Catalog
      Dim conn As ADODB.Connection
      Dim idxADOX as ADOX.Index
    
      Set cat = New ADOX.Catalog
      cat.ActiveConnection = Application.CurrentProject.Connection
    
      'The following is the particular line responsible 
      'for erasing the properties.
      Set idxADOX = cat.Tables("Table1").Indexes(0)
    
    End Sub
    					

13. In the Immediate window, type the following line, and then press ENTER:
    DropFieldProperties
14. Open the Query1 query in Design view.
15. Right-click the Field1 field, and then click Properties.

Note that the properties that you defined previously on the General tab are no longer there.