CODEWITHSUNDEEP
ms-accessvbafile-copying
StarkAE
StarkAE

Reputation: 3

How to have Access VBA overwrite a file on the desktop that is not the version on the server

I am new to Microsoft Access but have been learning as I go this past week. But now I am stuck. I am writing VBA code that checks if the user is using a current database version from their desktop compared to the database version on the server. I have established a table in the back end that holds the numerical value for each user's version and then a table that also stores the numerical value for the newest version on the server(changed by an admin when changes are made). The current code compares the values to determine if the copy that was opened is the newest version. If it is not it opens the version that is on the server and then changes the user's version in the back end table. But I want to change the copy on the desktop so the next time the user opens the copy it is the new version since the code will identify that the version was "updated".

I know that there is the My.Computer.FileSystem.CopyFile() that can be used to copy one file to a new location and write over the file that is there, but if I use this will it be able to write over the file that is open and running the code that is generating the .CopyFile() command? I already have the version on the server being accessed before the .CopyFile() command, but I just don't know if it will actually execute. I'm also not sure if I like the fact that the user will need to reenter their user name and password once the server version opens. Does anyone know of a way to overwrite the file on the desktop without the user even knowing?

Upvotes: 0

Views: 1059

Answers (1)

Blenikos
Blenikos

Reputation: 743

I have already done that in a previous database and with some modifications I think you can make it suitable to your needs.

I will start from the point that you have checked the version and found if it is the newest available or an old one.

If Not updated Then
    Select Case MsgBox("An update is available for your client!!!" _
    + " You would be able to use this client version but without new features and support!" _
    + " Do you want to update now?", vbYesNo Or vbInformation Or vbDefaultButton1, "Available Update")
        Case vbYes
            'Call MsgBox("Please contact your administrator to receive your new client version!!!", vbExclamation Or vbDefaultButton1, "Available Update")
            Shell "cmd /c ""<whatever path you want>\doupdate.cmd"", vbHide" 'calls a cmd script to do the dirty job of copy -> see below
            Application.CloseCurrentDatabase 'closes the current database
            Exit Sub
        Case vbNo
            <whatever you want on no>
    End Select
End If

You can remove the select on the above code and it will run everytime the variable updated is False without asking the user.

The cmd script code is the following:

@Echo OFF

SLEEP 3 
copy "\\server\groups\<whatever path>\file.accde" "%userprofile%\Desktop\file.accde" /Y
call "%userprofile%\Desktop\file.accde"
exit

The above script is surely not perfect because of a 3sec busy waiting on the begining, but I want to be sure that access has time to close. (The problem will be big enough if acccess hangs on close.

What you can also do is to add the above or the command My.Computer.FileSystem.CopyFile() on the close event of your database, however in that case the update will happen after the user has completed his work with the old version.

Note that in that case I wanted the script that updates to be separate from the database for other reasons. The main principle is the same even if you use vba for the update, you have to close the file before overwriting it.

Upvotes: 2

Related Questions