Microsoft Knowledge Base Email Alertz

(293823) - 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...

Search KbAlertz

Advanced Search

Receive Microsoft Knowledge Base articles by E-Mail?

Every night we scan the Microsoft Knowledge Base. If technologies you're interested in are updated, we'll send you an e-mail. You only get one e-mail a day, and only when new articles are added.

Click here to create a
FREE account
Already have an account?
[Click here to Login]











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

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

Collapse this tableExpand this table
IDName
1Joe
2Mary
3John
4Sally
5Jim

Table2

Collapse this tableExpand this table
IDName
1Joe
2Mary

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:
"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
Keywords: 
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