FileCopy procedure creating corruption
in [Forms]
|
From: Paul on 8 Dec 2009 01:59 I'm hoping that someone could take a look at the code below to see if they can identify what may be causing corruption in some of my mdb files. I'm running an Access 2003 application in a multiuser environment, where the back end is on a network server and the front ends are on the users' local C drives. I'm still developing the application and I'm releasing new updates to the front end once or twice a week to the beta testers. In order to simplify the periodic updates, an AutoExec macro compares version numbers in the front and back ends, and if they're different, it opens another mdb file and closes itself. The second mdb file then copies the new version of the front end from the master copy on the network drive onto the user's C drive. It works fine about 75% of the time, but the other25% of the time the new front end is corrupted - either a table or query is missing, or the VB code doesn't operate properly. I'm trying to figure out what's causing the corruption, and the only thing I could imagine was that one or more of the steps in the process occurred before a previous operation, such as a file copy, had a chance to compete. In an attempt to overcome that potential problem, I've inserted a Sleep function at several places in the code. Is there anything in my code below that could be causing corruption in the newly-copied front end mdb file? Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) If vVersion <> vVersion_WH Then 'compare versions in the front and back end mdb files Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") 'copy the update file to C in case it's not already here fso.CopyFile "M:\APPS\Cost-Control\SecurityDB\update_front_end.mdb", "C:\apps\update_front_end.mdb" Set fso = Nothing End If Sleep 2000 'give the copy a chance to complete before launching the new front end app Shell "msaccess.exe ""C:\apps\update_front_end.mdb""", vbMaximizedFocus 'open the update file Set fso = Nothing Application.Quit acQuitSaveNone 'close the (old) front end file _____________________________ The intermediate file, update_front_end.mdb, runs an AutoExec macro that copies the new front_end.mdb from the network server, launches the new front end file, and then closes itself. Here's the code I'm using to do that: Sleep 2000 'This gives VBA extra time (2 seconds) to close the old front end before trying to copy over it. Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") fso.CopyFile "M:\APPS\Cost-Control\SecurityDB\front_end.mdb", "C:\apps\efront_end.mdb" Set fso = Nothing Sleep 3000 'This gives VBA extra time to finish copying the file before launching it. Shell "msaccess.exe ""C:\apps\front_end.mdb""", vbMaximizedFocus Application.Quit acQuitSaveNone _____________________________ Is there anything in the foregoing code that could cause the copied file to be corrupted? Thanks in advance, Paul
From: Crystal (strive4peace) on 8 Dec 2009 02:22 Hi Paul, sounds like you could benefit from this: Auto FE Updater, by Tony Toews -- free http://www.autofeupdater.com/ Warm Regards, Crystal remote programming and training http://MSAccessGurus.com free video tutorials http://www.YouTube.com/user/LearnAccessByCrystal Access Basics http://www.AccessMVP.com/strive4peace free 100-page book that covers essentials in Access (also has links to chapters for learning VBA, sample databases, and Whistles and Bells) * (: have an awesome day :) * Paul wrote: > I'm hoping that someone could take a look at the code below to see if they > can identify what may be causing corruption in some of my mdb files. > > I'm running an Access 2003 application in a multiuser environment, where the > back end is on a network server and the front ends are on the users' local C > drives. I'm still developing the application and I'm releasing new updates > to the front end once or twice a week to the beta testers. > > In order to simplify the periodic updates, an AutoExec macro compares > version numbers in the front and back ends, and if they're different, it > opens another mdb file and closes itself. The second mdb file then copies > the new version of the front end from the master copy on the network drive > onto the user's C drive. > > It works fine about 75% of the time, but the other25% of the time the new > front end is corrupted - either a table or query is missing, or the VB code > doesn't operate properly. I'm trying to figure out what's causing the > corruption, and the only thing I could imagine was that one or more of the > steps in the process occurred before a previous operation, such as a file > copy, had a chance to compete. In an attempt to overcome that potential > problem, I've inserted a Sleep function at several places in the code. > > Is there anything in my code below that could be causing corruption in the > newly-copied front end mdb file? > > Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) > > If vVersion <> vVersion_WH Then 'compare versions in the front and back > end mdb files > Dim fso As Object > Set fso = CreateObject("Scripting.FileSystemObject") 'copy the update > file to C in case it's not already here > fso.CopyFile "M:\APPS\Cost-Control\SecurityDB\update_front_end.mdb", > "C:\apps\update_front_end.mdb" > Set fso = Nothing > End If > Sleep 2000 'give the copy a chance to complete before launching the new > front end app > Shell "msaccess.exe ""C:\apps\update_front_end.mdb""", vbMaximizedFocus > 'open the update file > Set fso = Nothing > Application.Quit acQuitSaveNone 'close the (old) front end file > _____________________________ > > The intermediate file, update_front_end.mdb, runs an AutoExec macro that > copies the new front_end.mdb from the network server, launches the new front > end file, and then closes itself. Here's the code I'm using to do that: > > Sleep 2000 'This gives VBA extra time (2 seconds) to close the old > front end before trying to copy over it. > Dim fso As Object > Set fso = CreateObject("Scripting.FileSystemObject") > fso.CopyFile "M:\APPS\Cost-Control\SecurityDB\front_end.mdb", > "C:\apps\efront_end.mdb" > Set fso = Nothing > > Sleep 3000 'This gives VBA extra time to finish copying the file before > launching it. > > Shell "msaccess.exe ""C:\apps\front_end.mdb""", vbMaximizedFocus > Application.Quit acQuitSaveNone > _____________________________ > > Is there anything in the foregoing code that could cause the copied file to > be corrupted? > > Thanks in advance, > > Paul > >
From: Paul on 8 Dec 2009 20:13 Thanks for sending me that link, Crystal. I've been directed to that site before, but I didn't try out Tony's solution because I thougth I came up with a simpler way of doing it. Now that I'm finding my "simpler" way isn't working consistently, I guess I should take a close look at the Auto FE Updater. Again, thanks for the help. Paul
From: Crystal (strive4peace) on 8 Dec 2009 23:47 you're welcome, Paul ;) happy to help Tony has been enhancing his Updater and his site ... good time to look at using it <smile> Auto FE Updater, by Tony Toews -- free http://www.autofeupdater.com/ Warm Regards, Crystal remote programming and training http://MSAccessGurus.com free video tutorials http://www.YouTube.com/user/LearnAccessByCrystal Access Basics http://www.AccessMVP.com/strive4peace free 100-page book that covers essentials in Access (also has links to chapters for learning VBA, sample databases, and Whistles and Bells) * (: have an awesome day :) * Paul wrote: > Thanks for sending me that link, Crystal. > > I've been directed to that site before, but I didn't try out Tony's solution > because I thougth I came up with a simpler way of doing it. Now that I'm > finding my "simpler" way isn't working consistently, I guess I should take a > close look at the Auto FE Updater. > > Again, thanks for the help. > > Paul > >
From: Tony Toews [MVP] on 10 Dec 2009 01:23
"Paul" <BegoneSpam(a)forever.com> wrote: >I've been directed to that site before, but I didn't try out Tony's solution >because I thougth I came up with a simpler way of doing it. Now that I'm >finding my "simpler" way isn't working consistently, I guess I should take a >close look at the Auto FE Updater. Get it going but check back in a day or so. I'm waiting feedback from a user and am just about to release an update that will make distributing the initial setup to a new user much, much easier. You'll be able to email a link to a shortcut which they can just click on. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ Granite Fleet Manager http://www.granitefleet.com/ |