Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & "SERVER=localhost;" & " DATABASE=time_and_attendance;" & "UID=root;PWD=admin; OPTION=3"
While Not EOF(inFile)
Line Input #inFile, InLine
If InStr(InLine, "Event:Access") > 0 Then
data() = Split(InLine)
fields = UBound(data())
 
'**********************
'At this point you have fresh data in your array and what you 
'WANT to do is verify if the current card, in Card(data(5)), has 
'already been used today. 
 
'What you need here is a condition to see if the card has already 
'been used. Actually the IF statement you use later is almost there, i.e.
 
Set rs = New ADODB.Recordset
squery = "": squery = "Select * from tblattendance where Date='" & data(0) & "' and CardNumber='" & data(5) & "'"
Call ExecuteCommand
'If rs.EOF Then ' DATA HAS BEEN USED
If NOT rs.EOF Then  'Data has NOT been used so insert it.
'**********************
 
conn.Execute "INSERT INTO tblattendance (Date, TimeIn, Event, CardNumber) " & _
"VALUES ('" & data(0) & "', " & _
"'" & Left(data(1), 8) & "', " & _
"'" & Mid(data(2), 7) & "', " & _
"'" & Card(data(5)) & "')"
 
'**********************
'Now you have inserted the data into your database since it was 
'not there to begin with.
 
'However, if the data was already in the table you need to update 
'the TimeOut with the current time. So we scrap the If statement 
'(since we used it above) and use the else clause.
 
Else
'**********************
 
'Set rs = New ADODB.Recordset
'squery = "": squery = "Select * from tblattendance where Date='" & 'data(0) & "' and CardNumber='" & data(5) & "'"
'Call ExecuteCommand
'If rs.EOF Then ' DATA HAS BEEN USED
 
'conn.Execute "UPDATE tblattendance set TimeOut = '" & Left(data(1), 8) & "'"
'**********************
'The new UPDATE statement
conn.Execute "UPDATE tblattendance set TimeOut = '" & Left(data(1), 8) & "'" & _
WHERE Date='" & 'data(0) & "' and CardNumber='" & data(5) & "'"
'**********************
End If
rs.Close
Set rs = Nothing
End If

    Open sFile For Input As #inFile
'    Set db = DBEngine.OpenDatabase(App.Path & "\db1.mdb")
    While Not EOF(inFile)
        Line Input #inFile, InLine
        If InStr(InLine, "Event:Access") > 0 Then
        data() = Split(InLine)
        fields = UBound(data())
 
        Set rs = New ADODB.Recordset
        squery = "": squery = "Select * from tblattendance where Date=#" & data(0) & "# AND CardNumber='" & Card(data(5)) & "'"
'        Call ExecuteCommand
        rs.Open squery, conn
        If rs.EOF Then
 
            conn.Execute "INSERT INTO tblattendance ([Date], [TimeIn], CardNumber) " & _
                       "VALUES (#" & data(0) & "#, " & _
                              "'" & Left(data(1), 8) & "', " & _
                               "'" & Card(data(5)) & "')"
 
            'Set rs = New ADODB.Recordset
            'squery = "": squery = "Select * from tblattendance where Date='" & data(0) & "' and CardNumber='" & data(5) & "'"
            'Call ExecuteCommand
            'If rs.EOF Then ' DATA HAS BEEN USED
        Else
            conn.Execute "UPDATE tblattendance set TimeOut = '" & Left(data(1), 8) & "' where Date=#" & data(0) & "# AND CardNumber='" & Card(data(5)) & "'"
        End If
        rs.Close
        Set rs = Nothing
        End If
    Wend
    MsgBox "Record successfully saved       ", vbInformation + vbOKOnly, "Record Saved"
    Unload Me
    Me.Show
    Close #inFile
    Exit Sub