Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Question Unanswered: Check if record exists

    Hi

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

    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 or not import the rest of the data.

    Any idea what I could do to make sure that there are no duplicates for the IDNr column? Would appreciate some help

    Code:
    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

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    How about a unique index (no duplicates) on that field in the table and then handle the error?
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Nov 2009
    Posts
    2
    Hi

    The IDNr column is currently set to Not Allow Duplicates. The problem is that if I import multiple records with the comma delimited text file, and there is one duplicate in the text file it will not import anything else.

  4. #4
    Join Date
    Mar 2007
    Posts
    277
    How about importing everything to a temp table and then append to the real table form the temp table. I would think you would then get an error from Access that you could handle with an error handler.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •