Preventing duplicate data?

[chupacabra]

Here is my problem, it's looking kind of complicated to me, but maybe it's because I've confused myself. so i need an outside opinion. (that's you guys)

Ok.

I have this data, which is from testing a device. this data, which can be rather extensive, is written into a comma delimited text file.

What I am doing, is taking said text files and importing them into access using my vb code, and then doing queries and all that good stuff on the data, to eventually manipulate it into an output excel document.

but, what I am doing right now in this, is trying to prevent duplicate data.

Is there a way I can do this BEFORE inserting the data into the database?
check the data to be imported vs. the existing data?

There should never be duplicate data (start times, part numbers, serial number and whatnot), UNLESS the user has retested a device and wants to keep both sets of data.

Right now I'm kind of unsure about HOW I want to test the data, or govern what constitutes duplicate data.

But is there a way to test before the insertion?

like treat the text file like a database, and do a normal sql query on it to see if the first entries match (make sure same file is not imported twice), or something along those lines?

I think I've just really mixed myself up, haha.
Any suggestions on how to go about this?

[softraj]

1st show the code on how you're importing the data into Access using vb code.

[chupacabra]

 
Public Sub ImportTextToAccessADO(dbfullpath As String, tableName As String, textFullPath As String, Fields() As String)
'=====================================================================================================
'Does the physical work of inputting the text file into the database using SQL via ADO.
'   Programmatically creates SQL string
'=====================================================================================================
On Error GoTo errHandler

    Dim cnn As New ADODB.Connection
    Dim sqlString As String
    Dim textFilePath As String, textFname As String
    
    Dim i As Integer 'counter variable
    Dim intFields As Integer
    
    textFilePath = textFullPath
    textFname = getFileName(textFilePath, True)
    
    cnn.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & dbfullpath & ";" & _
    "Jet OLEDB:Engine Type=4;"
    
    '''''Begin formulating SQL insertion string '''''''''''''''''''''''''''''''''''''''''''''''''''''
    sqlString = "INSERT INTO [" & tableName & "] (" & vbCrLf & vbTab

    intFields = UBound(Fields())
    For i = 0 To intFields Step 1
        sqlString = sqlString & "[" & Fields(i) & "]"
        If Not i = intFields Then
            sqlString = sqlString & ", " & vbCrLf & vbTab
        End If

    Next i
    sqlString = sqlString & vbCrLf & ")" & vbCrLf & " SELECT "
    
    sqlString = sqlString & "* "
    
'    For i = 0 To intFields Step 1
'        sqlString = sqlString & "[" & Fields(i) & "]"
'        If Not i = intFields Then
'            sqlString = sqlString & ", " & vbCrLf & vbTab
'        End If
'    Next i

    sqlString = sqlString & vbCrLf & "FROM [Text;DATABASE=" & textFilePath & "].[" & textFname & "];"
    
    'Debug.Print sqlString
    '''''End formulation of SQL string''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    
    cnn.Execute sqlString
    
    'MsgBox "Operation Completed Successfully!", vbOKOnly, App.Title
    
    Exit Sub

[Phil Weber]

I would probably query the Access database for each row before doing the Insert. Pseudo-code:

Read a line from CSV file
Query database for CSV values
If not found,
    Insert row

[chupacabra]

I would probably query the Access database for each row before doing the Insert. Pseudo-code:

Read a line from CSV file
Query database for CSV values
If not found,
    Insert row

hmm you don't think that would take too long? As I've said before, these files are pretty big...
The last one I imported was 90MB, and it was a "medium" sized file. The database ended up having 266,547 rows.

I was kinda thinking about that way...that was actually the initial way i had planned on importing a file, before ever even thinking of the duplicate data, though.

I guess I can do it that way, and then also query for any other things that would/could also constitute duplicate or re-written data.

Thanks

[Phil Weber]

I have no idea how long it will take. ;-) You'll have to test it. I wouldn't worry about optimizing it, though, until after I had tested it and confirmed that it was too slow. Even then, you should be able to perform the import as a background batch process; if it doesn't impact the user, who cares how slow it is?