After a week fully occupied with other things I have finally established a procedure which satisfies my requirements. I experimented with writing a module to be triggered by exiting Access but then realised that I needed the synced files (which may have been updated on another computer) to be set as the active files for my next session. My solution goes back to my early computing days when Windows was for wimps and real men used DOS, namely write a batch file and call it from the shortcut instead of opening Access directly!
I also decided that I wanted some way of restoring the previous files in case anything went wrong with the automated process, so after a bit of fiddling, here is my batch file:
Code:
@echo off
REM Copy active files to backup directory
E:
CD E:\AvizzeData\Backup
Copy C:\ANdata\DorohoiDB4LIVE_be.mdb /y
Copy E:\AvizzeData\ANavizDB.mdb /y
REM Copy synced files to active directories
C:
CD C:\ANdata
Copy DBsync\DorohoiDB4LIVE_be.mdb /y
E:
CD E:\AvizzeData
Copy C:\ANdata\DBsync\ANavizDB.mdb /y
REM Run Access database front end
ANavizDB.mdb
REM Copy active files to sync file directory
C:
CD C:\ANdata\DBsync
Copy C:\ANdata\DorohoiDB4LIVE_be.mdb /y
Copy E:\AvizzeData\ANavizDB.mdb /y
pause
The final 'pause' allows me to check that the process has run correctly - replace this with a 'Exit' once you are satisfied.
SugarSync is configured to backup only the DBsync directory, and so is not confused by the intermediate saves within Access.
Obviously you will need to substitute your own filenames and paths, and you have to modify any shortcut icons to call the batch file instead of opening Access first. A computer-specific batch file and shortcut are required if you have different configurations on different computers.
Athar - I'm sorry if you were waiting for some sophisticated Access module but I found that this was the simplest and most controllable way of achieving what I wanted.
Susy - I do have front end and backend split and in different directories. IIRC, the password is only for Access internal protection and the batch process should work unless your _be file is also write-protected.
Good luck!