469,575 Members | 1,668 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,575 developers. It's quick & easy.

There must be a faster way to dup check in Access than this...

Sorry if I posted in the wrong forum, but since I'm looking at VB code I have a 50/50 chance at being wrong. :)

I've been trying a variety of methods to speed up the checking for duplicates.

Right now I'm using a simple while statement and if statement to go through the database row by row checking the field I want, and then moving onto the next.

At the moment it works fine because the database is in its infancy, but I know down the line the database will have thousands of entries, and using this method will not be 'optimal' for when that time comes.

This is what I am doing to check the database for duplicate entries, and I KNOW there has to be a faster way. I cant imagine using this with a database with over 500,000 entries in it.

Expand|Select|Wrap|Line Numbers
  1.         Dim database_rows As Integer = 0
  2.         Dim current_row As Integer = 0
  3.         Dim noDup As Integer = 0
  4.         Try
  6.             Dim CN As New OleDb.OleDbConnection
  8.             CN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database\Jobs.accdb;Jet OLEDB:Database Password=123temp;"
  9.             CN.Open()
  11.             Dim jb As String = "Select * From " & "JobBasics"
  12.             Dim da As New OleDb.OleDbDataAdapter(jb, CN)
  13.             Dim ds As New DataSet()
  14.             da.FillSchema(ds, SchemaType.Source, "JobBasics")
  15.             da.Fill(ds, "JobBasics")
  16.             database_rows = ds.Tables("JobBasics").Rows.Count
  18.             'Check the database if the record already exsists
  19.             While (current_row < database_rows)
  20.                 If (g_v.JobBasics.jb_title = ds.Tables("JobBasics").Rows(current_row).Item("jb_title")) Then
  21.                     MsgBox("This job already exsists, please use the search function.", MsgBoxStyle.Exclamation, "Duplicate Entry!")
  22.                     CN.Close()
  23.                     Exit Sub
  24.                 Else
  25.                     current_row = current_row + 1
  26.                 End If
  27.             End While
  30.             'Add the Job Info to the database if the loop finds nothing
  31.             Dim cmdBuilder As New OleDb.OleDbCommandBuilder(da)
  32.             da.InsertCommand = cmdBuilder.GetInsertCommand
  34.             With ds.Tables("JobBasics")
  36.                 Dim dr As DataRow = .NewRow
  37.                 dr("jb_title") = g_v.JobBasics.jb_title
  38.                 dr("jb_id") = g_v.JobBasics.jb_id
  39.                 dr("jb_td") = g_v.JobBasics.jb_td
  40.                 dr("jb_ad") = g_v.JobBasics.jb_ad
  41.                 dr("jb_criteria") = g_v.JobBasics.jb_criteria
  42.                 dr("jb_description") = g_v.JobBasics.jb_description
  43.                 dr("jb_comments") = g_v.JobBasics.jb_comments
  44.                 dr("jb_lastdate") = g_v.JobBasics.jb_lastdate
  45.                 dr("jb_usr_id") = g_v.key.global_key(4)
  47.                 .Rows.Add(dr)
  48.             End With
  49.             da.Update(ds, "JobBasics")
  50.             CN.Close()
  52.         Catch ex As Exception
  53.             MsgBox(ex.Message)
  55.         End Try
Anyone have some suggestions?
Mar 16 '08 #1
3 1096
8,127 Expert 4TB
The best / fastest way to chek for duplicates is to use COUNT().

If it returns more than 1 there is duplicate entry.
Mar 17 '08 #2
8,435 Expert 8TB
I admit I haven't read your post in detail, only skimmed quickly.

But as for finding duplicates, keep in mind you may not need to. If the idea is to prevent creation of duplicates, then one simple method is to define a unique index on a field or combination of fields which will prevent it. In your code, you simply attempt to add your records, and trap the error that results if it is a duplicate.

Alternatively, rather than "manually" looping through the records you'd do better to use the power of the database by issuing a query to specifically find duplicates of the value(s) you're about to store.

A lot depends on the situation, obviously ("Know Thy Data" as usual). But in general, the former technique might be quicker if you expect duplicates to be rare, while the latter would be preferable if you expect to find duplicates most of the time.
Mar 17 '08 #3
Thank you both, I was about to resond with 'how do I use the count()?" but openig up my SQL Server I found I was missing my ' and ' around my where item. :)

The reason I cant use a predefined index which auto counts up is because I'm using a randomly generated key of numbers and letters as the ID to track the job, as everything that has to do with the job is centered around the ID I thought it easiest to use that as my 'primary key'.

But the count way works perfectly :) for some reason when I was using it before, I kept using the Rows.count instead of the item(#).tostring! Which was my problem a week ago.

Thanks for the help! :)
Mar 18 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by James dean | last post: by
2 posts views Thread by Arjen | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.