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

RESOLUTION

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.