Preventing duplicate data?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Preventing duplicate data?

  1. #1
    Join Date
    Jun 2006
    Posts
    168

    Preventing duplicate data?

    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?
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  2. #2
    Join Date
    Dec 2005
    Posts
    92
    1st show the code on how you're importing the data into Access using vb code.

  3. #3
    Join Date
    Jun 2006
    Posts
    168
    Code:
     
    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
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  4. #4
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    I would probably query the Access database for each row before doing the Insert. Pseudo-code:
    Code:
    Read a line from CSV file
    Query database for CSV values
    If not found,
        Insert row
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  5. #5
    Join Date
    Jun 2006
    Posts
    168
    Quote Originally Posted by Phil Weber
    I would probably query the Access database for each row before doing the Insert. Pseudo-code:
    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
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  6. #6
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    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?
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

Similar Threads

  1. Data binding vs n-tier
    By Kathleen Dollard in forum .NET
    Replies: 14
    Last Post: 05-03-2002, 12:10 AM
  2. Turbo Data: test data generator (www.turbodata.ca)
    By Peter Prager (Canam Software) in forum vb.announcements
    Replies: 0
    Last Post: 08-15-2001, 10:48 AM
  3. Data Junction Announces XML Junction 7.51
    By Tim Frost in forum xml.announcements
    Replies: 0
    Last Post: 04-02-2001, 10:53 AM
  4. Unbound Data Report Recordset
    By Dale in forum VB Classic
    Replies: 1
    Last Post: 06-21-2000, 06:44 PM
  5. DataGrid - Enter data in a cell at run time
    By William Gaddam in forum VB Classic
    Replies: 1
    Last Post: 05-02-2000, 09:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center