I hope this is the right forum for such a question, if not I apologize in advance.
I have an application that is being used by six people. When the newest version of it was built, I altered the desktop shortcut on everyone's PC to point to the newest version. Four of these shortcuts still work, but two are behaving somewhat erratically.
In the case of these two, clicking on the shortcut sometimes opens the older version of the application. I have recreated the shortcuts and they work for a while, before reverting to the older link. Going to the application via Explorer works fine.
While I am getting more comfortable with VBA, this area is outside anything I know. Could someone advise me on what affects whether shortcuts work or not? Do they automatically 'refresh'? If so, when?
In case it's relevant, everyone here is using the same versions of both XP and Access and everyone has access to the same drives.
How did you "alter" the shortcuts? With code or manually. Are you sure the users are not messing with the shortcuts. Shortcuts should not revert back unless somebody is changing them.
What is the exact text string you have in the Target field?
When I said 'alter' I meant I manually deleted them and recreated them. Sorry for the confusion there.
I'm fairly confident the users aren't messing them up, as both are rather wary of computers in general.
The text string is the path to the application (I coped and pasted it from the explorer window). I have found that the same thing can happen if the users open Access and pick from the list of recently opened items, so I doubt the path has too big a part to play.
I was clutching at straws, to be honest, when I suggested the shortcuts might be 'refreshing' on their own. The problem doesn't happen on a daily basis, or even every other day, but it's one of those things that bugs me purely because it makes no sense to me.
Odd that you are not using the same \\path\filename.mdb each time you update your application. That way the shortcuts do not have to me modified for all you have to do is over write [replace] the old mdb file with the new version of the mdb file.
I did think of doing that, but there were a few versions of the mdb floating around when I got here and since so many changes were made in the first month or two, I opted to move to the next version number.
I did think of doing that, but there were a few versions of the mdb floating around when I got here and since so many changes were made in the first month or two, I opted to move to the next version number.
Then you will always have the potential risk of a user opening and using an outdated version of the program. The user might not realize that they have keyed or edited a days worth of work into an old version until it is too late. Who would be responsible for verifying that the correct data is being input into the current version? I highly recommend that you delete all of the previous version files to avoid the confusion. The application is worthless unless you can guarantee the integrity of the data. I have never heard of having a completely different file based on the version of the application. What a nightmare to maintain. I also have to guess that the db is not split.
I have no idea why your shortcuts may be behaving as you say. However, have you thought about automating your Database updates?
I have 3 databases which are used by my department, each one has a Front End stored on the users PC and a back end which sits on the Network and holds the data.
I have a system set up so that whenever a user opens their copy, some code checks the version of their database against a master version. If there copy is out of date, some more code overwrites their current version with the new version from the network.
This is done automatically and means that no user can work on an old version.
OK, there are several parts to the update process.
1) You need to pick a folder on your network which will hold the master (or latest version) copy of the front end database and which each of your users can access.
2) In this folder, you need two .mdb files. One will be the latest version of your front end database, the second .mdb will hold one table only which will hold the master version number. I call this database DataDB.mdb, in here I have one table called tblVersionServer and this has one field called VersionNumber.
3) In your front end database, you need a table (local, not linked) called tblVersionClient, whcih will have a field called VersionNumber. You also need to link to the tblVersionServer form the DataDB.mdb file, mentioned in 2).
For the coding of this, in your front end database, add a new module (I call mine UpdateApplication but it doesn't really matter) and add the following to it:
Code:
Option Compare Database
Declare Function apiCopyFile Lib "KERNEL32" Alias "CopyFileA" _
(ByVal lpExistingFileName As String, _
ByVal lpNewFileName As String, _
ByVal bFailIfExists As Long) As Long
Now, the way the process works is to use a Splash Form, so you need to create a form which you will need to set as the form to opened on the start up of your database. You can design any type of form you like, on mine for example I have a text box which displays the current database version.
Anyway, on your splash form, you need to set up two events, the first is the OnLoad event and the code is:
This basically gets the value of the local front end version and the master version on the server.
The second event is the OnTimer event (you need to set your Timer Interval to 2000 in your forms properties) and the code is:
Code:
Me.TimerInterval = 0
If strVerClient = strVerServer Then
Me.Visible = False
DoCmd.OpenForm "frmInitialMenu" [COLOR="SeaGreen"]'You need to change the value of frmInitialMenu to the name of the form which normally opens when your database is opened.[/COLOR]
Exit Sub
Else
Dim strSourceFile As String
Dim strDestFile As String
Dim strAccessExePath As String
Dim lngResult As Long
'Create the source's path and file name.
strSourceFile = "X:\Database\Database.mbd" [COLOR="seagreen"]'You need to change this to the path of your master front end file on the server.[/COLOR]
strDestFile = CurrentProject.FullName
'Determine path of current Access executable
strAccessExePath = SysCmd(acSysCmdAccessDir) & "MSAccess.exe "
If Dir(strSourceFile) = "" Then 'Something is wrong and the file is not there.
MsgBox ("See System Administrator")
Else 'copy the new version of app over the existing one.
lngResult = apiCopyFile(strSourceFile, strDestFile, False)
End If
'Modify strDestFile slightly so that it can be used with the Shell function
strDestFile = """" & strDestFile & """"
MsgBox "Application Updated. Please wait while the application restarts.", _
vbInformation, "Update Successful"
'Load new version, then close old one.
Shell strAccessExePath & strDestFile & "", vbMaximizedFocus
DoCmd.Quit
End If
To manage this process, if you need to make changes to the front end database, make the changes, then update the version number (I started at 1.0, then 1.1 etc.) in both the tblVersionServer and the tblVersionClient (make sure they are both the same!)
What happens is, if the version of the users database matches the master version in the DataDB.mdb file, then it opens whichever form you specify and exits this process.
If it doesn't match, the ne version gets copied from the server and overwrites the existing copy on the users PC. A message box will tell the user that the application has been updated, they click OK and the database opens up. This time obviously, the version numbers will match and they are ready to go on the latest version.
I've been trying your version check, and running into a problem. I'm using the switchboard as the main form. I've put your your code into the onload and timer events.
But when I try and open the database via the FE on my desktop, Access opens, but nothing else happens. I don't see the switchboard, or anything.
OK, I've got the database to open. I had forgotten to link to the datadb table.
I've created the splash form, and added the code. When I open the database, it opens the form, waits a sec and then goes to my switchboard. Only problem, it's not updating the FE at all. I've doubled checked the client/server versionnumbers, and they are both the same on the master FE. But on the client FE, I could have an older version, and it doesn't update.
Is there more I need to do, then what you have above?
Unfortunately, I've been asked to work on another area of the application for the time being. As soon as I can get back to the shortcut problem, I shall certainly give your method a try.
I didn't want you thinking I was ignoring your answer, after you'd been good enough to post it.
