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

• Microsoft Access 2000

SYMPTOMS

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.

CAUSE

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

RESOLUTION

Open each query in Design view, and then reenter the needed properties. Try to avoid using the ADOX Catalog object with a Table object in Microsoft Access databases.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

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.

Additional query words: pra

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug