INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Linked Text Data persists stale / does not update

Linked Text Data persists stale / does not update

(OP)
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.

CODE

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 

RE: Linked Text Data persists stale / does not update

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/

RE: Linked Text Data persists stale / does not update

(OP)
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.

RE: Linked Text Data persists stale / does not update

(OP)
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).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close