-
checking for duplicates
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