I did create a 2nd front-end that our 3 heaviest users access, and that's significantly cut down on the frequency of these errors. It used to happen about 1-3 times a week, and since I created that 2nd front end, it only happens about 1 or 2 times a month.
Katie, you prove our point!
You need to start deploying your front-end to the local user’s PCs as suggested by not only myself but also by Twinnyfo or use the advice that NeoPa has given.
As for the suggestion to only compile on the server… unless the installation you are using is actually running from the server (i.e. in the cloud)... you are compling at the local PC level! I often compile my front-ends on either the PC at my desk or on my PC at home (both stand-alone installations of Office). The most important thing is that the software the front-ends are compiled on are at the same revision level, so long as they are, I’ve never had any issues with where the front-end is located during compile. Nor have I had an issue with the files opening on the user’s local PC… so long as they are using the same revision level of the software.
Ok... now... I'm going to give you a VERY simple batch-file that will make distribution of your front end VERY doable... There is no error checking; however, I've used this template dozens of times without issues.
Now, normally, I don’t offer full code unless I've seen some work; however, in this case with so many users needing your DB and the fact that batch-files are nolonger used alot by the typical user, I will give you my version of the SIMPLE batch file that I use for small front ends:
Open your Notepad and copy the following:
- @ECHO OFF
-
rem Check for local file - create directory and copy file if not present.
-
if not exist %userprofile%\z_db_applicationname\z_productiondatabase.mde mkdir %userprofile%\z_db_applicationname
-
-
Rem updates local copy with server file with overwrite and no prompt
-
xcopy "\\UNCSERVER\uncfolder\uncsubfolder\z_ productiondatabase .mde" "%userprofile%\z_db_applicationname\ z_ productiondatabase .mde " /y /q
-
-
rem start the application… give a user prompt if the shell sticks…
-
echo "CLICK ANY KEY TO START THE ACCESS PROGRAM"
-
START /I "MSAccess.exe" "%userprofile%\z_db_applicationname\ z_ productiondatabase .mde "
Now this is a template file so save this file with the name “Z_FrontEnd_Update.TXT”
Now… make a copy of this text file!
Opps... one more step:
Create a new subfolder in the folder where your production database is kept. MOVE your production database to this folder…
Now back to the text file we just made:
Now in the copy of the text file you just created:
“\\UNCSERVER\uncfolder\uncsubfolder” = You need to change this to the path to the location of the subfolder you just copied the production database to… I use UNC paths instead of drive letters… that way if the network drive is mapped to “H” on one PC and “X” on a different PC then it doesn’t matter as the batch file is looking for the “UNCSEVER” name… much like a website. If you can not determine the UNC path yourself... get your I.T. Magician to help.
“z_db_applicationname” = This is a subfolder. I use separate subfolders to group my database work and I use the same name on the local PC so that it is easier to find in the code... I HIGHLY ADVISE that you do not use spaces, dashes, etc in your path and file names… USE only alpha-numerics and the the underscore! If you fail to do so, then you WILL make things like this batch file difficult to use.
“z_productiondatabase.mde” = Change this to your database name… I HIGHLY ADVISE that you do not use spaces, dashes, etc in your path and file names… USE only alpha-numerics and the the underscore! If you fail to do so, then you WILL make things like this batch file difficult to use.
Now save your changes… but use something like “Z_Launch_ApplicationName.
CMD” and place this file in the folder where your production database WAS located.... that’s correct… was…. You do not want your users opening the front-end on the network anymore… so move it! Let the batch file do the opening from now on. ALSO Note… the extension must now be “CMD” not “TXT”
Now… send a short cut to the CMD file to your users. Personally, I send an Email in Outlook with a link to the batch file in the message and instructions as to how they can create their own shortcut for the batch file on their desktop.
From now on… every time they click on the shortcut/batch-file, it will run, copy the current front-end to the local PC and start Access using the local copy. It doesn’t matter at this point if you have 1 user or 10000 users; your users will never ever use the network front-end again unless they open it directly. AND BETTER YET… you can make a change, upload the changed front-end, and the next time the user clicks on the Z_Launch_ApplicationName.CMD file to open the database… they get a copy of the update.
You can also do something like this from VBA code in your front-end that checks for versions and so forth. I offer this solution as it doesn’t require VBA… just windows DOS.
There is just one issue... linked tables... make sure they are also using the UNC convention for file path. With the UNC, so long as they don't move or the server name doesn't change... you should be fine; however, if the server name is changed etc... you will have to relink. I have code in my front-ends that does this check and trys to relink... but that is a different thread! :)
-z