ACC2000: Fields May Appear to Be Missing in a Replicated Database

When you view a table in Datasheet view or in Design view in a replicated database, not all fields may appear. You may also notice that you can see some of the system fields in these views. If you view system objects in the database, the fields are now visible.

If you import a table that contains replication fields from an earlier version of Access to an Access 2000 database and then replicate the Access 2000 database, this problem occurs.

There are two possible workarounds for this problem. You must use the Design Master for both workarounds.

Workaround 1

You can manually reset the ordinal position of the fields as follows:

1. Run Access 2000 and then open your database.
2. On the Tools menu, click Options.
3. On the View tab, make sure that System objects is checked and then click OK.
4. Select your table in the Database window and then click Design.
5. Select all system fields and then drag the system fields to the top of the field list.
   
   Notice that other system fields may be visible and may be mixed with your existing fields. You must also move these other system fields.
6. Select all the system fields and then drag them to the bottom of the field list.
7. Save your table.
8. On the Tools menu, click Options.
9. On the View tab, make sure that System objects is not checked and then click OK.
10. View your table in Datasheet view.
    
    The fields that were previously hidden now appear.

Workaround 2

You can reset the position of these fields programmatically by using Microsoft Visual Basic for Applications (VBA) code. Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

1. Run Access and then open the database where the problem occurs.
2. Click Objects, click Modules, and then click New.
3. Add the following code to the module:

   Const CON_ERR_ITEM_NOTFOUND As Integer = 3265
   
   Function IsSystemField(fld As DAO.Field) As Boolean
       ' procedure: IsSystemField
       ' purpose:   Given a DAO.Field object, returns True if the field
       '            is a system field.
       If (fld.Attributes And dbSystemField) = dbSystemField Then
           IsSystemField = True
       End If
   End Function
   
   Sub ReorderFields(strTableName As String)
       ' procedure: ReorderFields
       ' purpose:   Resets the OrdinalPosition property for all system fields to
       '            appear after non-system fields.
       ' argument:  strTableName, the name of the table to reorder
       ' returns:   nothing
       On Error GoTo ReorderErrors
       
       Dim db As DAO.Database
       Dim td As DAO.TableDef
       Dim fld As DAO.Field
   
       ' Counter used to reset non-system fields.
       Dim iCounter As Integer
   
       ' Counter used to reset system fields.
       Dim iCounter2 As Integer
       
       ' Number of non-system fields
       Dim iNonSystemFields As Integer
       
       ' get DAO objects
       Set db = CurrentDb()
       Set td = db.TableDefs(strTableName)
       
       ' Initial pass through the fields to determine how
       ' many non-system fields there are.
       For iCounter = 0 To td.Fields.Count - 1
           Set fld = td.Fields(iCounter)
           If Not IsSystemField(fld) Then
               iNonSystemFields = iNonSystemFields + 1
           End If
       Next
       
       ' Reset the counter.
       iCounter = 0
       
       ' Reset ordinal position for system (replication) fields to the end
       ' and non-system fields to the beginning.
       For Each fld In td.Fields
           If Not IsSystemField(fld) Then
               fld.OrdinalPosition = iCounter
               iCounter = iCounter + 1
           Else
               fld.OrdinalPosition = (iNonSystemFields + iCounter2)
               iCounter2 = iCounter2 + 1
           End If
       Next
       
   ReorderExit:
       ' cleanup
       db.Close
       Set fld = Nothing
       Set td = Nothing
       Set db = Nothing
       Exit Sub
       
   ReorderErrors:
       If (Err = CON_ERR_ITEM_NOTFOUND) Then
           MsgBox "Cannot find the table '" & strTableName & "' specified."
           Resume ReorderExit
       Else
           MsgBox "An error has occurred: " & vbCrLf & _
                  Err.Description & " (" & Err.Number & ")"
           Resume ReorderExit
       End If
   End Sub
   					

4. On the Debug menu, click Compile ProjectName, where ProjectName is the name of your VBA project.
5. On the File menu, click Save. Save the module as basReorderFields.
6. Type the following line in the Immediate window and then press ENTER:
   
   ReorderFields "TableName"
   
   "TableName" is the name of the table where the problem occurs.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Steps to Reproduce the Problem

Note These steps require that you use both Microsoft Access 97 and Access 2000.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Run Access 97 and then open the Northwind.mdb sample database.
2. On the Tools menu, click Replication and then click Create Replica. When you are prompted to create a Replica, click No.
   
   This converts the Northwind sample database to a Design Master.
3. Run Access 2000 and then create a new blank database.
4. On the File menu, click Get External Data and then click Import.
5. Move to the Access 97 Northwind.mdb sample database and then import the Categories table and the Employees table.
6. On the Tools menu, click Options.
7. On the View tab, make sure that System objects is not checked and then click OK.
8. In Access 2000, click Replication on the Tools menu and then click Create Replica. When you are prompted to create a Replica, click No.
   
   This converts the new database to a Design Master.
9. Open the Categories table in Design view.
   
   Notice that system fields appear although system objects are not shown.