ACC2000: Properties Are Missing from Queries After You Access TableDef Object with ADOX Catalog Object

After you run ActiveX Extensibility Objects (ADOX) code referring to a Table object in a Microsoft Access database, you notice that the field properties in one or more queries in the database are missing.

This problem may occur when the following conditions are true:

• The Name AutoCorrect feature is turned on (this feature is turned on by default).
• In Visual Basic for Applications, you access a Table Object with the ADOX Catalog object.

This causes the fields of all the queries that contain the table that you referred to with the TableDef object to lose custom property settings, such as the Format, the Input Mask, the Description, and the Caption property settings.

IMPORTANT: If you have Access 2000 running on Microsoft Windows 2000, Microsoft Office 2000 Service Release 1 (SR-1) will not correct this problem.

To resolve this problem on Windows 2000, obtain Windows 2000 Service Pack 1 (Win 2000 SP1). For information on downloading SP1 or obtaining the Windows 2000 SP1 CD, please visit the following Microsoft Web site: To resolve this problem on non-Windows 2000-based computers, obtain Microsoft Office 2000 Service Release 1 (SR-1). 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, before you run the ADOX code that refers to a table with the Catalog object, turn off Name AutoCorrect. To do so, follow these steps:

1. On the Tools menu, click Options.
2. In the Options dialog box, click the General tab.
3. Ensure that the Track name AutoCorrect Info check box is not selected, and then click OK.

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 the Behavior

1. Create a new Access database.
2. On the Tools menu, click Options.
3. In the Options dialog box, click the General tab.
4. Ensure that the Track name AutoCorrect Info check box is selected, and then click OK.
5. In the Database window, click Tables, and then create a table named Table1 in Design view.
6. Create one field named Field1. Set the data type of the field to Text.
7. Close the table and click Yes when Access asks if you want to create a primary key. This lets Access add the ID AutoNumber field.
8. Create a query named Query1.
9. Add the Field1 field and the ID field to the query design grid.
10. Click the top of the Field1 column so that the entire column is selected. Right-click the column, and then click Properties on the shortcut menu. The properties for the Field1 field appear.
11. On the General tab, type any valid text for the following properties:
    • Description
    • Format
    • Input Mask
    • Caption
12. Save and close the query.
13. In the Database window, click Modules, and then click New. In the Visual Basic Editor, on the Tools menu, click References, and then click to select the check box for the following library (you may need to scroll down to see it):
    Microsoft ADO Ext. 2.1 for DDL and Security
    Click OK.
14. In the new module, type or paste the following code:

    Sub xQueryFieldProps()
    
      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 line has the Catalog object refering to Table1.
      Set idxADOX = Cat.Tables("Table1").Indexes(0)
    
    End Sub
    					

15. In the Immediate window, type the following code:

    xQueryFieldProps
    					

16. On the File menu, click Close and Return to Microsoft Access.
17. In the Database window, click Queries, and then open the Query1 query in Design view.
18. In the query design grid, click the top of the Field1 column so that the entire column is selected. Right-click the column, and then click Properties on the shortcut menu.
19. On the General tab, note that the properties are now missing.

For additional information about a similar issue involving DAO code affecting queries in an Access database, click the article number below to view the article in the Microsoft Knowledge Base: