Linked table not updating

[PRMiller2]

Having trouble with something that has worked for me in the past. I have a database that is stored on our network. When users open the front-end Access application, the network database is copied to the user's local C drive. In order to view changes made by others, the user can click a Synchronize button. This button uploads any changes they have made to the network database, then copies the network database back to the local machine.

I am using the same general code I have used successfully in the past. The latest database is being copied to the user's local computer. The menu form remains open while they do this, with an hourglass. Once the copy job is complete, I requery the subform to see refreshed data.

The problem is that data in the open form is not showing the necessary changes. Even more troubling, I leave the object browser available during debug and click on a linked table where I expect to see a change. The data in that linked table is old. I then open another copy of Access, open the local database (source of the linked table), and see UPDATED data... NOT the data I see from the linked copy.

Thoughts on what could be causing this?

Private Sub cmdSynchronize_Click()
On Error GoTo Err_Handler

    DoCmd.Hourglass True

    'Code here runs an INSERT from a local transaction table to the network database
    
    DoCmd.Hourglass True
    If RefreshLocalDatabase = 0 Then
        MsgBox "Unable to refresh local database.", vbOKOnly + vbExclamation, CurrentDb.Properties("AppTitle")
    End If
        
    DoCmd.Hourglass True
    Me.sfmRejects.Form.Refresh
    
Exit_Handler:
    DoCmd.Hourglass False
    Exit Sub
    
Err_Handler:
    Call LogError(Err.Number, Err.Description, "frmMain.cmdSynchronize_Click()")
    Resume Exit_Handler

End Sub

Public Function RefreshLocalDatabase()
On Error GoTo Err_Handler

    Dim fso As Object 'Scripting.FileSystemObject
    Dim strDest As String
    Dim strFileDate As String
    Dim strFileName As String
    Dim strSource As String

    Set fso = CreateObject("Scripting.FileSystemObject")
    
  '  strSource = "J:\Production Network Path\GoLive_DB.accdb"
    strSource = "C:\Documents and Settings\prmiller\My Documents\_Go Live Reporting\_Dev\GoLive_DB--DEVNETWORK.accdb"
    strDest = "C:\GoLive\GoLive_DB.accdb"
    
    If fso.FileExists(strSource) Then
        fso.copyfile strSource, strDest
        DoEvents
        RefreshLocalDatabase = 1
    Else
        RefreshLocalDatabase = 0
    End If

Exit_Handler:
    Set fso = Nothing
    Exit Function
    
Err_Handler:
    Call LogError(Err.Number, Err.Description, "basGlobal.RefreshLocalDatabase()")
    RefreshLocalDatabase = 0
    Resume Exit_Handler
    
End Function

 

[MazeWorX]

I dont really see any issues unless you strSource didnt exist ... did you check the name for spelling?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[PRMiller2]

Hi maze, thanks for the reply. Yes, I stepped through the code in debug mode, nothing errors out. It does execute correctly, as the latest copy of the database arrives in the local folder.

The weird issue is that, if I check a linked table from the application, the data is stale. If I open a copy of the local database to which the application is linked, the data is fresh.

It's as if the application has taken a snapshot of the data and refuses to update it's view. Oddly enough, I can update linked tables via SQL statements from the app.

 

[MazeWorX]

have you tried to refresh the table links? you might want to look at this code

http://www.eraserve.com/tutorials/MS_ACCESS_VBA_Re_Link_Tables.asp

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[PRMiller2]

Sorry for the very late reply. Maze that works, thank you! I don't know what I was thinking... I should always be refreshing my table links!