Question Check if record exists

[kasteel]

Hi

I am importing alot of data from a text file into an access database with the code below. (Comma delimited)

The problem is that I do not want to import duplicate records. I have the "IDNr" column in my access database set to "not allow duplicates" and need my code to check if there is already a record in the database before trying to import the data. Otherwise it will just give me an error and not import the rest of the data.

Any idea what I could do? Would appreciate some help

Thanks

Public Sub UPdateDB()

        Dim ImportDBFileExists As String = "C:\test\outputfile1.txt"
        If System.IO.File.Exists(ImportDBFileExists) = True Then
            'Database Import
            Dim cnn As New ADODB.Connection
            Dim Rec As New ADODB.Recordset
            Dim sqlString As String
            cnn.Open( _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\test\test.mdb;" & _
            "Jet OLEDB:Engine Type=4;")
            sqlString = "INSERT INTO [Sheet1] (SwipeCard, FirstName, LastName, Faculty, Degree, IDNr, EndDate, [e-mail]) SELECT SwipeCard, FirstName, LastName, Faculty, Degree, IDNr, EndDate, [e-mail] FROM [Text;DATABASE=C:\test;].[outputfile1.txt]"
            cnn.Execute(sqlString)          
        Else         
            Return
        End If

    End Sub

 

[Tom]

You can create a Stored Procedure for your insert statment and within that SP first check to see if the record exists. However the bottom line is that no matter how you do it, its an extra hit to the database to first check for the records existence and a second to insert the actual record. This is time consuming with very large imports. Although sometimes it is needed and unavoidable, I prefer to let the key constraints worry about duplicate records and then handle the problem records by either skipping the record or encasing the entire import into a transaction to be rolled back.

 

[JuggaloBrotha]

You can create a Stored Procedure for your insert statment and within that SP first check to see if the record exists. However the bottom line is that no matter how you do it, its an extra hit to the database to first check for the records existence and a second to insert the actual record. This is time consuming with very large imports. Although sometimes it is needed and unavoidable, I prefer to let the key constraints worry about duplicate records and then handle the problem records by either skipping the record or encasing the entire import into a transaction to be rolled back.

Access doesn't have stored procedures (well it claims to, but it's "stored procedures" aren't anywhere near what you'd expect)

What I would do is first do a SELECT statement with the criteria in the WHERE and see if any rows come back, if so, either ignore or do an update, otherwise do your insert.

 

[kasteel]

Hi

Thanks for the replies.

Are you able to show me an example? I cannot seem to get it working because of the multiple lines in the text file. How can I skip a line if the record exists?

Thanks