Unanswered: Form not requerying backend data after file copy
I have an odd problem and so far no luck finding a solution on google or here with the search function.
I have an application split into a frontend and several backend data files. One of these linked backend data files is a copy of a master personnel file that resides on the network and is updated by administrative types. The users of this application can hit a Copy button from the personnel form to update their local copy of the personnel data before they go on travel to ensure they have the latest data.
The code there is simple enough. Here's a stripped down version...
'Command Button on Form
Private Sub cmdUpdateData_Click()
' Copy personnel data over the network
' Code in a module
Public Function updatePersonnelData(formName As String)
Dim fs as Object
' Close the form
DoCmd.Close acForm, formName, acSaveNo
Set fs = CreateObject("Scripting.FileSystemObject")
' Check for file existence (and network connection) and some error handling
If fs.FileExists(NETWORK_FILE_PATH) Then
If fs.FolderExists(LOCAL_FILE_DIR) = False Then
' Create the local directory structure
' Make a local copy of the master personnel data file
fs.CopyFile NETWORK_FILE_PATH, LOCAL_FILE_PATH, True
' Open the form again
Set fs = Nothing
Now here's the fun part. When the form opens again, the data is not updated. If I manually close the form and manually open the form again, the new data is there. If I comment out the DoCmd.OpenForm at the end, click the update button and manually open the form, the new data is there.
Is there something that Access does with the external data files which does not occur while code is executing? Is there a command to force it to happen so I can open the form again programmatically rather than being forced to just show a MsgBox kindly asking the user to open the form again themselves?
I have tried refreshing and requerying the form after it is opened and that does not help. I have tried Currentdb.TableDefs.Refresh before opening the form again and that does not work. I tried a little loop through all TableDefs in CurrentDb doing .RefreshLink on each one (with a connect string) before opening the form again and no luck.
For the moment I'm going with the MsgBox approach because I've already spent more time than it's worth for this project, but I'd still really love to figure out what is going on here for my own knowledge in the future. If you have any ideas, please let me know and I'll give them a shot.
My guess is it is a timing issue. The copy is probably run asynchronously and the form open occurs milliseconds are the copy has started.
I think the messing with the form's RecordSource will force it to Refresh the data. Another option is changing the form's RecordType. I think that will also force the form to get new data. And finally you might be able to put in a pause after to the copy to wait for the copy to complete.
You can use the below procedure to check if it is a timing thing. I would pause for 30 seconds and then open the form to see if the data is updated. Then you can mess with it to get a resonable time.
Public Sub Pause(sinSeconds As Long)
Dim sinStartTime As Single
Dim sinFinishTime As Single
Dim lngCount As Long
'Get the current number of seconds since the beginning of the day
sinStartTime = Timer
sinFinishTime = sinStartTime + sinSeconds
'Adjust for midnight
If sinFinishTime > 86400 Then sinFinishTime = sinFinishTime - 86400
Do Until Timer > sinFinishTime
lngCount = 0
Thanks for having a read. Sorry I did not get back here for a while but I've been on other projects.
I played around for a few minutes earlier and found the problem to be with Snapshot recordset types. I did not dig further into why but as soon as I set the form to Dynaset, which I don't need, it works fine.
So again, as a Snapshot form, closing the form via code, copying new data via filesystem copy code, and re-opening the form via code shows old data. Closing the form via code, copying new data via filesystem copy code, and making the user re-open the form manually shows them new data.
As a Dynaset form, I'm not running into this issue.
That still makes no sense to me but I can chalk it up to just another quirk I don't understand and not use Snapshots anymore.