Linked Text Data persists stale / does not update

[lameid]

I have two linked tables. A text source table and a physical Access table for destination.

I have a form with an on timer event looping the data in the text file via a recordset and putting it in the physical table, again via recordset. This works great on the first pass. Then I update the text file (test case is Notepad++ which is forgiving about file locks) and then the data never makes it to the destination table on successive executions. My on screen indicator says the update has run again. If I close the form and reopen it, the table updates as expected. I suspect I am missing something nuanced about the way linked text files are refreshed but I have tried requerying the recordset and refreshing the tabledefs collection. I am coming up empty on ideas. Any thoughts?

Access 2010, Windows 7.

Private Sub Form_Timer()
    
    'For each record,
    'Copy all records in "from" table to "to" table
    
    Dim db As DAO.Database
    Dim rs_Map As DAO.Recordset
    
    Dim rs_From As DAO.Recordset
    Dim rs_To As DAO.Recordset
    Dim fld As Field
    Dim IndexFields() As String
    Dim i As Long
    Dim lngUpper As Long
    
    Dim strFind As String
    Dim strField
    
    'Debug.Print "Table synchronize run at " & Now
    
    Set db = CurrentDb
    txtLastRun = Now
    
    Set rs_Map = Me.RecordsetClone 'The form's recordsource is a local physical table that has the table mapping... only one record in this first test case
        
    While Not rs_Map.EOF
        
        Set rs_From = db.OpenRecordset(rs_Map!Update_From)
        rs_From.Requery 'Data was stale on successive iterations... so requery?...
        Set rs_To = db.OpenRecordset(rs_Map!Update_To)
        IndexFields = Split(rs_Map!Field_list_for_Unique_ID, ",")
                    
        While Not rs_From.EOF
            lngUpper = UBound(IndexFields)
            strFind = ""
            For i = 0 To lngUpper
                strField = Trim(IndexFields(i))
                If IsNumeric(rs_From(strField)) And strField <> "Field1" And strField <> "Field2" And strField <> "Field3" Then 'Field names changed
                    strFind = strFind & strField & " = " & rs_From(strField).Value
                Else
                    If IsDate(rs_From(strField)) Then
                        strFind = strFind & strField & " = #" & rs_From(strField).Value & "#"
                    Else
                        strFind = strFind & strField & " = """ & rs_From(strField).Value & """"
                    End If
                End If
                
                If i < lngUpper Then
                    strFind = strFind & " AND "
                End If
            Next i
            rs_To.FindFirst (strFind)
            
            If rs_To.NoMatch Then
                rs_To.AddNew
            Else
                rs_To.Edit
            End If
            
            For Each fld In rs_From.Fields
                rs_To.Fields(fld.Name).Value = fld.Value
            Next fld
            
            rs_To.Update
            
            rs_From.MoveNext
        
        Wend
        rs_Map.MoveNext
    
    Wend

    If chkRun Then
        Me.TimerInterval = c_RunPeriodic 'Sync may be set for shorter duration like after checking run or when form first opened, so set it to 59 seconds so it "runs every minute".
    Else
        Me.TimerInterval = 0 'Just in case can get in here with sync off, turn it off
    End If
    db.Close
    Set db = Nothing
Exit Sub

Form_Timer_Err:
    'Likely this application will be minimized, so make some noise
    Beep
    Beep
    Beep
    MsgBox "Error " & Err.Number & " " & Err.Description
End Sub

 

[Blorf]

Just a quick observation - There is no requery command in the code. Maybe try me.requery before Exit Sub.

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That DonÆt.

http://www.thelegacyreturns.com/

 

[lameid]

I am viewing the data directly in the backend and hitting F5 to refresh it in testing and the form's recordsource is not the table being updated.

This did spur me to change to opening the table directly instead of using recordsetclone... apparently it must start at a different record position each time which is weird. A movefirst should also fix it then. Something else weird is going on now with duplicates but that should be easy to spot.

 

[lameid]

Oops... nothing wrong with code I posted except recordsetclone. There was an issue with my setup data (test data has an optional field in the composite field so the whole null / zero length string criteria issue caused it to not find the data when it exists).