Microsoft KB Archive/927417

Knowledge Base

A table field appears incorrectly in Datasheet view when you add the field by using the Microsoft DAO Object Library and you set the OrdinalPosition property in Access

Article ID: 927417

Article Last Modified on 2/10/2007

---

APPLIES TO

• Microsoft Office Access 2003
• Microsoft Access 2002 Standard Edition
• Microsoft Access 2000 Standard Edition

---

SYMPTOMS

After you add a field to a table in Microsoft Office Access, the field appears as the second column in the table. This problem occurs if the following conditions are true:

• You add the field by using the Microsoft Data Access Objects (DAO) Object Library in Microsoft Office Access.
• You change the value of the OrdinalPosition property of the field to display the field as the first column in the table.
• You open the table in Datasheet view.

WORKAROUND

To work around this problem, follow these steps:

1. In the Access database, click Tables, right-click the table that has the problem, and then click Open.
2. On the File menu, click Save.
3. Right-click the table that you saved in step 2, and then click Open.

Notice that the new column is now the first column in the table.

MORE INFORMATION

For more information about the Microsoft DAO 3.6 Object Library, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/aa163992(office.10).aspx

Steps to reproduce the problem

1. Open Access.
2. On the File menu, click New.
3. Click Blank database, type db1.mdb in the File name box, and then click Create.
4. Click Tables, right-click Create table in Design view, and then click Open.
5. In the first Field Name box, type F1, and then click Text in the first Data Type list.
6. In the second Field Name box, type F2, and then click Text in the second Data Type list.
7. On the File menu, click Save.
8. In the Table Name box, type Table1, and then click OK.
9. In the dialog box that appears, click No, and then close Design view for the table that you created.
10. Click Modules, and then click New.

11. In the Module1 code window, paste the following code:

    Function test()
            'Object parameter decleration
            Dim db As DAO.Database
            Dim fld As DAO.Field
    
            Set db = CurrentDb 'put current database into database object
            Set fld = db.TableDefs("Table1").CreateField("AAA", 4) '"AAA" create field
            db.TableDefs("Table1").Fields.Append fld '"AAA" add field
            db.TableDefs("Table1").Fields("AAA").OrdinalPosition = 0 '"AAA" move field location
            db.Close 'Database object Close
            test = "END" 'execution is completed
    End Function

12. On the Tools menu, click References, select the Microsoft DAO 3.6 Object Library check box, and then click OK.
13. On the Run menu, click Run Sub/UserForm.
14. On the File menu, click Close and Return to Microsoft Office Access.
15. Click Tables, right-click Table1, and then click Open.

Notice that the new column is not set as the first column in the table.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Keywords: kbprb kbinfo kbbug kbexpertiseadvanced kbexpertiseinter kbtshoot kbsample KB927417

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.