Microsoft KB Archive/293823

= How To Configure ADO to Ignore Duplicates During a Bulk Insert into an Access Database =

Article ID: 293823

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft OLE DB Provider for Jet 4.0

-



This article was previously published under Q293823



SUMMARY
When you insert a number of records into a table in a Microsoft Access database, you may want any records that duplicate existing records to be ignored with no error or warning message, and you may want only unique new values to be inserted. Data Access Object (DAO) behaves this way by default; however, Microsoft ActiveX Data Objects (ADO) raises an error and halts when it encounters the first duplicate. This article describes how you can use the Jet OLE DB Provider and the Global Partial Bulk Ops dynamic property to change the default ADO behavior so that ADO inserts only the new values and ignores the duplicates.



MORE INFORMATION
Suppose that you have two Access database tables, Table1 and Table2.

Table2
Suppose that you want to use ADO to copy the values from Table1 into Table2. You want the records that exist only in Table1 to be added to Table2, and you want the duplicate values to be ignored without an error or warning message. In other words, you want to synchronize Table2 with Table1. Suppose that you use the following SQL statement: INSERT INTO Table2 SELECT * FROM Table1 To make this possible in ADO, you must follow these steps:
 * 1) Connect to the Access database using the Microsoft Jet 4.0 OLE DB Provider.
 * 2) Change the default behavior for bulk inserts. To do this, add the dynamic property Jet OLEDB:Global Partial Bulk Ops=1 to the connection string.

The resultant ADO connection string resembles the following string: &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=C:\Test\Insert.mdb;Jet OLEDB:Global Partial Bulk Ops=1&quot; This setting applies only to bulk operations that are executed on the current ADO connection.

Keywords: kbhowto kbjet kbmdacnosweep KB293823

-

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

© Microsoft Corporation. All rights reserved.