Article ID: 247486
Article Last Modified on 11/25/2002
APPLIES TO
- Microsoft Access 2000 Standard Edition
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
- In the Database window, click Tables under Objects, click New, click Design View, and then click OK.
- Add a field named Field1 with a data type of Text.
- Close the table, and when prompted, save it as Table1.
- When prompted to create a primary key, click Yes to allow Microsoft Access to add the primary key.
- In the Database window, click Queries under Objects, click New, click Design View, and then click OK.
- In the query design grid, add the Table1 table.
- Add the two fields (the AutoNumber field and the Field1 field) to the query design grid.
- Right-click the Field1 field in the query design grid, and then click Properties.
- 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. - Save the query as Query1, and then close it.
- In the Database window, click Modules under Objects, and then click New.
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
- In the Immediate window, type the following line, and then press ENTER:
DropFieldProperties
- Open the Query1 query in Design view.
- 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.
Additional query words: pra
Keywords: kbbug kbpending KB247486