Microsoft KB Archive/299908

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

Article ID: 299908

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q299908



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



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



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.
 * 1) Select all the system fields and then drag them to the bottom of the field list.
 * 2) Save your table.
 * 3) On the Tools menu, click Options.
 * 4) On the View tab, make sure that System objects is not checked and then click OK.
 * 5) 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.

 Run Access and then open the database where the problem occurs. Click Objects, click Modules, and then click New.  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 &quot;Cannot find the table '&quot; & strTableName & &quot;' specified.&quot; Resume ReorderExit Else MsgBox &quot;An error has occurred: &quot; & vbCrLf & _ Err.Description & &quot; (&quot; & Err.Number & &quot;)&quot; Resume ReorderExit End If End Sub  On the Debug menu, click Compile , where   is the name of your VBA project. On the File menu, click Save. Save the module as basReorderFields . Type the following line in the Immediate window and then press ENTER:

ReorderFields &quot;TableName&quot;

&quot;TableName&quot; is the name of the table where the problem occurs.</li></ol>

<div class="status_section">

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

<div class="moreinformation_section">

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.
 * 1) Run Access 2000 and then create a new blank database.
 * 2) On the File menu, click Get External Data and then click Import.
 * 3) Move to the Access 97 Northwind.mdb sample database and then import the Categories table and the Employees table.
 * 4) On the Tools menu, click Options.
 * 5) On the View tab, make sure that System objects is not checked and then click OK.
 * 6) 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.
 * 1) Open the Categories table in Design view.

Notice that system fields appear although system objects are not shown.

Additional query words: pra

Keywords: kbbug kbnofix KB299908

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.