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

CAUSE

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.

RESOLUTION

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:

http://www.microsoft.com/windows2000/

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:

245025 OFF2000: How to Obtain and Install Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a)

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:

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

MORE INFORMATION

Steps to Reproduce the Behavior

Create a new Access database.
On the Tools menu, click Options.
In the Options dialog box, click the General tab.
Ensure that the Track name AutoCorrect Info check box is selected, and then click OK.
In the Database window, click Tables, and then create a table named Table1 in Design view.
Create one field named Field1. Set the data type of the field to Text.
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.
Create a query named Query1.
Add the Field1 field and the ID field to 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. The properties for the Field1 field appear.
On the General tab, type any valid text for the following properties:
- Description
- Format
- Input Mask
- Caption
Save and close the query.
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.

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

In the Immediate window, type the following code:
```
xQueryFieldProps
					
```
On the File menu, click Close and Return to Microsoft Access.
In the Database window, click Queries, and then open the Query1 query in Design view.
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.
On the General tab, note that the properties are now missing.