FileCopy procedure creating corruption

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

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
 
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
 
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 said:
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.
Click to expand...

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/
 
Back
Top