Hi,
I'm creating a vb program that will add the records in a text file( delimited by commas) to an existing MS Access table. My problem is if i have a record in my text file that is duplicate of a record in the table the program will return an error message from Access and stop the program. So even if after the duplicate record in the text file i have a record that is not a duplicate it wont be added. Does anyone know how i can fix this.
What i tried to do is
Loop through files in text file
intialize variable dup = ""
check table if matching record(select load_id into dup from tbloads where load_id = load)
if no record is found (dup="" )
add text file record to table
end if
Loop to next text file record

Code:
Option Explicit
 
Private Sub cmdAppend_Click()
Dim Load As String
Dim Loc_City As String
Dim Loc_State As String
Dim Dest_City As String
Dim Dest_State As String
Dim Desc As String
Dim Req As String
Dim Dte As String
Dim Message As String
Dim LoadFile As Integer
LoadFile = FreeFile

Dim Connection, dsn, sql

'create ADO connection and recordset object
Set Connection = CreateObject("ADODB.Connection")

'initialise dsn
dsn = "dsn=dsnLoads"

sql = "Select * From tblLoads"

'open the connection to the database
Connection.Open dsn

Open "C:/My Documents/project/load.txt" For Input As #LoadFile
Do Until EOF(LoadFile)
    Input #LoadFile, Load, Loc_City, Loc_State, Dest_City, Dest_State, Desc, Req, Dte
    Connection.Execute ("INSERT INTO tblLoads VALUES('" & Load & "','" & Loc_City & "','" & Loc_State & "','" & Dest_City & "','" & Dest_State & "','" & Desc & "','" & Req & "' ,'" & Dte & "')")
Loop
' check to see if there are any errors
If Err.Number = 0 Then
    MsgBox ("Your records have been entered.")
Else
    MsgBox ("Sorry your records could not be entered.")
End If
Connection.Close
Set Connection = Nothing
End Sub