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.
Suppose that you have two Access database tables, Table1 and Table2.
Table1
Collapse this tableExpand this table
| ID | Name |
|---|
| 1 | Joe |
| 2 | Mary |
| 3 | John |
| 4 | Sally |
| 5 | Jim |
Table2
Collapse this tableExpand this table
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.