From: Paul Clement on
On Sun, 31 Jan 2010 12:08:10 -0800 (PST), Faraz Azhar <itzfaraz(a)gmail.com> wrote:

� Hello.

� I have a database with quite some data in it. The DB is MS Access db.
� it has a primary key which is alphanumeric key.

� What im trying to accomplish is that I have a lot of data that I want
� to add to that database. Now most of my data my already exist in the
� database. Obviously if i add the data which has the same key as the
� data which already exists, it will give error that duplicate values
� are being created.

� I need suggestions on how to do this the fastest way: Add 2000 or 3000
� records to a ADO database, furthermore add records only if they dont
� previously exist in the database.

� For this i have two techniques:

� 1) First i may search the entire database for that unique key. If key
� exists then I wont add new record. If key doesnt exist then ill
� continue and add the record.

� 2) or i would just keep on adding records. ADO will give error that
� duplicate values are being created. If error arises (ie. record
� already exists in the database) then ill skip that newly added item.
� If error doesnt arise that means record was unique in the database.

� Which of the above two ways would you guys recommend ? Is there any
� other way round which is faster than this?

It would probably help if we knew what the data source was that you are importing but I would go
with #2. Just use On Error Resume Next and check the Err object (for the type of error) after
performing the INSERT.


Paul
~~~~
Microsoft MVP (Visual Basic)
From: Cor Ligthert[MVP] on
Good that I checked first your reply, that was my idea as well, although I
would not even check the error, why would you do that?


"Paul Clement" <UseAdddressAtEndofMessage(a)swspectrum.com> wrote in message
news:7fqdm55m3emtosddljpdei6tjgi7oui2gj(a)4ax.com...
> On Sun, 31 Jan 2010 12:08:10 -0800 (PST), Faraz Azhar <itzfaraz(a)gmail.com>
> wrote:
>
> � Hello.
> �
> � I have a database with quite some data in it. The DB is MS Access db.
> � it has a primary key which is alphanumeric key.
> �
> � What im trying to accomplish is that I have a lot of data that I want
> � to add to that database. Now most of my data my already exist in the
> � database. Obviously if i add the data which has the same key as the
> � data which already exists, it will give error that duplicate values
> � are being created.
> �
> � I need suggestions on how to do this the fastest way: Add 2000 or 3000
> � records to a ADO database, furthermore add records only if they dont
> � previously exist in the database.
> �
> � For this i have two techniques:
> �
> � 1) First i may search the entire database for that unique key. If key
> � exists then I wont add new record. If key doesnt exist then ill
> � continue and add the record.
> �
> � 2) or i would just keep on adding records. ADO will give error that
> � duplicate values are being created. If error arises (ie. record
> � already exists in the database) then ill skip that newly added item.
> � If error doesnt arise that means record was unique in the database.
> �
> � Which of the above two ways would you guys recommend ? Is there any
> � other way round which is faster than this?
>
> It would probably help if we knew what the data source was that you are
> importing but I would go
> with #2. Just use On Error Resume Next and check the Err object (for the
> type of error) after
> performing the INSERT.
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)

From: Paul Clement on
On Mon, 1 Feb 2010 20:03:44 +0100, "Cor Ligthert[MVP]" <Notmyfirstname(a)planet.nl> wrote:

� Good that I checked first your reply, that was my idea as well, although I
� would not even check the error, why would you do that?


Cor,

You check the error to make certain that it results from an attempt to add a row with a duplicate
primary key. There could be other constraints that generate an error and you may want to log those
as import failures so that they can be resolved for a subsequent import.


Paul
~~~~
Microsoft MVP (Visual Basic)
From: Tony Toews [MVP] on
Faraz Azhar <itzfaraz(a)gmail.com> wrote:

>Thanks everyone. I thought a query of some kind could be developed to
>add mass data into a database without producing duplicates. Anyway
>I'll try option #2...

Note that it's possible adding a record and then ignore the duplicates
could lead to excessive bloating of the Access database file. Access,
actually Jet/ADO/whatever may very well allocate a page for the
record, the realize an index is duplicate, mark that page as empty and
then continue.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/