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

[Nimion]

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.

        Dim database_rows As Integer = 0
        Dim current_row As Integer = 0
        Dim noDup As Integer = 0
        Try
 
            Dim CN As New OleDb.OleDbConnection
 
            CN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database\Jobs.accdb;Jet OLEDB:Database Password=123temp;"
            CN.Open()
 
            Dim jb As String = "Select * From " & "JobBasics"
            Dim da As New OleDb.OleDbDataAdapter(jb, CN)
            Dim ds As New DataSet()
            da.FillSchema(ds, SchemaType.Source, "JobBasics")
            da.Fill(ds, "JobBasics")
            database_rows = ds.Tables("JobBasics").Rows.Count
 
            'Check the database if the record already exsists
            While (current_row < database_rows)
                If (g_v.JobBasics.jb_title = ds.Tables("JobBasics").Rows(current_row).Item("jb_title")) Then
                    MsgBox("This job already exsists, please use the search function.", MsgBoxStyle.Exclamation, "Duplicate Entry!")
                    CN.Close()
                    Exit Sub
                Else
                    current_row = current_row + 1
                End If
            End While
 
 
            'Add the Job Info to the database if the loop finds nothing
            Dim cmdBuilder As New OleDb.OleDbCommandBuilder(da)
            da.InsertCommand = cmdBuilder.GetInsertCommand
 
            With ds.Tables("JobBasics")
 
                Dim dr As DataRow = .NewRow
                dr("jb_title") = g_v.JobBasics.jb_title
                dr("jb_id") = g_v.JobBasics.jb_id
                dr("jb_td") = g_v.JobBasics.jb_td
                dr("jb_ad") = g_v.JobBasics.jb_ad
                dr("jb_criteria") = g_v.JobBasics.jb_criteria
                dr("jb_description") = g_v.JobBasics.jb_description
                dr("jb_comments") = g_v.JobBasics.jb_comments
                dr("jb_lastdate") = g_v.JobBasics.jb_lastdate
                dr("jb_usr_id") = g_v.key.global_key(4)
 
                .Rows.Add(dr)
            End With
            da.Update(ds, "JobBasics")
            CN.Close()
 
        Catch ex As Exception
            MsgBox(ex.Message)
 
        End Try

Anyone have some suggestions?

[debasisdas]

The best / fastest way to chek for duplicates is to use COUNT().

If it returns more than 1 there is duplicate entry.

[Killer42]

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.

[Nimion]

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! :)