Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 293823 - Last Review: June 29, 2004 - Revision: 2.2
How To Configure ADO to Ignore Duplicates During a Bulk Insert into an Access Database
This article was previously published under Q293823
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.
APPLIES TO
- Microsoft Data Access Components 2.1
- Microsoft Data Access Components 2.5
- Microsoft OLE DB Provider for Jet 4.0
| kbhowto kbjet kbmdacnosweep KB293823 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please