ADO: Mass updating a database with unique key
in [Visual Basic]
|
From: Paul Clement on 1 Feb 2010 09:55 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 1 Feb 2010 14:03 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 1 Feb 2010 14:42 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 1 Feb 2010 15:22 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/
First
|
Prev
|
Pages: 1 2 Prev: VB Control Positioning in MS Word Next: Mass updating a database with unique key |