ACC2000: Upsizing Error and No Data Is Upsized with Table That Contains Replication IDs (253757)

SYMPTOMS

When you upsize an Access table that contains Replication ID fields, you may receive the following error message:

Microsoft Access was unable to append all the data to the table.

The contents of fields in x record(s) were deleted, and 0 record(s) were lost due to key violations.

*If data was deleted, the data you pasted or imported doesn't match the field data types or the FieldSize property in the destination table.

*If records were lost, either the records you pasted contain primary key values that already exist in the destination table, or they violate referential integrity rules for a relationship defined between tables.

Do you wish to proceed anyway?

If you proceed, you see one of the following results when the upsizing process is finished:

If you do not have a unique index on the field that contains Null values, the table is upsized as well as the data.
If the field has a unique index but ignores Null values, the number of records deleted will be the count of the null records, but none of the data is upsized.

RESOLUTION

To work around this problem, temporarily drop the index from the Replication ID field that contains the Null values before you upsize the table. After the table is upsized, you can add the index back to the SQL Server table with Enterprise Manager or in an Access Project. To see an example of how to do this, follow these steps:

Open the original Access database (.mdb).
Open the problem table in Design view.
On the View menu, click Indexes.
Delete the index that is on the Replication ID field that contains the Null values.
Upsize the database. The table should be upsized with all the data.
Open the target SQL Server database, either in SQL Server Enterprise Manager or in an Access project.
Open the table in Design view.
Right-click anywhere in the table design area, and then click Properties.
In the Properties dialog box, click Indexes/Keys.
Click New. On the first blank row under Column Name, select the column on which you want an index. In the Index Name box, accept the default index name or type a different name.
Change any of the other default settings for the index if you need to.
Close the Properties dialog box.
Save and close the table.

MORE INFORMATION

Steps to Reproduce the Behavior

Create a new Access database, and name it UpsizeTest.mdb.

In the new database, create a table with the following specifications, and then save it as tblTest:

   Table Fields: tblTest
   ----------------------------
   Field Name: Col1
   Data Type: Number
   Field Size: Replication ID
   Indexed: Yes (No Duplicates)

   Field Name: Col2
   Data Type: Number
   Field Size: Replication ID
   Indexed: Yes (No Duplicates)

   Field Name: Col3
   Date Type: Text
   Field Size: 20
   Indexed: Yes (Duplicates OK)

   Table Properties: tblTest
   -------------------------
   PrimaryKey: Col1
   Index1: Col2

Save the table, and then view it in Datasheet view.

In the Col1 field, type the following Replication IDs (be sure to include the braces {}):

   {17DC72A1-F0E1-4F59-AF0D-F3D413269A32}
   {302AD122-01E0-49EF-B2A6-D28681E34B97}
   {30ABD2AB-7494-4CCB-86CF-8FD524E51EF5}
   {36D901D5-4EA6-4D4B-A670-CE87E98BC1E0}
   {9CFAC60E-FC56-4F98-AD0C-5B63E3E0D41F}
   {ED0950B4-D8F7-4B90-8936-AE29CA011688}
   {FD82B066-4758-477B-A112-FBB22AC2585E}

In the Col2 field, type the following Replication IDs. Leave the field blank for the last three records:

   {17DC72A1-F0E1-4F59-AF0D-F3D413269A32}
   {302AD122-01E0-49EF-B2A6-D28681E34B97}
   {36D901D5-4EA6-4D4B-A670-CE87E98BC1E0}
   {9CFAC60E-FC56-4F98-AD0C-5B63E3E0D41F}

In the Col3 field, type the following names:

   Sam
   Nick
   John
   Terry
   Mike
   Joe
   Don

Save and close the table.
On the Tools menu, point to Database Utilities, and then click Upsizing Wizard.
Select to use an existing SQL Server database, and then click Next.
Select the appropriate DSN to connect to your SQL Server, and then click OK.
On the page that asks you which tables to upsize, double-click tblTest so that it appears on the right.
Click Next twice.
On the page that asks you which application changes you want to make, click Link SQL Server tables to existing application.
Click Finish. Note that you receive the error message mentioned in the "Symptoms" section of this article.
Click Yes to proceed with upsizing.
Close the resulting Upsizing Report. In the list of tables, you see that the original table is now called tblTest_local, and a new attached table to the upsized table has the original name with a globe icon, as expected.
Open the attached table, tblTest, and note that no data has been upsized.

NOTE: In this example, the data would be upsized if either there were no index on Col2 or if Col2 contained no Null values. However, because the Replication ID fields are upsized as a UniqueIdentifier data type, the upsized data will appear as #Deleted in the linked table unless you have applied the Microsoft Jet 4.0 SP 4 or later update to your system.

For additional information about this problem, click the article number below to view the article in the Microsoft Knowledge Base:

257487 ACC2000: Access Displays #DELETED When You Insert Data with the Same Value into a Non-Unique Key Column of a Remote Table