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


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q247486
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

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

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, 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.

STATUS

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.

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.
Note that the properties that you defined previously on the General tab are no longer there.
Modification Type:MajorLast Reviewed:11/25/2002
Keywords:kbbug KB247486
©2002 Microsoft Corporation. All rights reserved.