How To Configure ADO to Ignore Duplicates During a Bulk Insert into an Access Database (293823)

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.

Table1

ID	Name
1	Joe
2	Mary
3	John
4	Sally
5	Jim

Table2

ID	Name
1	Joe
2	Mary

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:

Connect to the Access database using the Microsoft Jet 4.0 OLE DB Provider.
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:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
	"Data Source=C:\Test\Insert.mdb;Jet OLEDB:Global Partial Bulk Ops=1"

This setting applies only to bulk operations that are executed on the current ADO connection.