Keep getting the "locked by another user" error or can't copy new code because users have your app open or slow performance of MSAccess file?
Or you wish you could have 100+ users in your app without any problems? (or at least more than 4) and easily tell who's currently using the app.
Try this method out. This script resolves:
1. "Locked by another user" or "The database has been placed in a state by user admin on a machine which prevents it from being opened" errors - PERMANENTLY!
2. The ability to have an unlimited number of users opening the front-end.
3. The ability to copy code at ANY time, even while users are using your application!
4. Avoids having to regularly compact/repair the frontend.
5. Not having to redo user's desktop shortcut when a frontend is moved to another location.
6. Seeing who's CURRENTLY using the frontend, when a user LAST opened the frontend, and see what "version" a user last opened.
7. Never having to put code in to automatically "logoff" (or quit/close) the user out of the frontend.
8. Automatically repairing corrupt frontends.
9. Safely "hiding" your "source" frontend file and users never open it but are instead, working off of an exact clone of it.
When run, the script will then take the front-end, clone it with the user's loginID (ie. copy/paste), and launch the cloned file - simple as that. (it automatically grabs the user's loginID!)
Since the cloning script always clones off the source mdb/mde, the user will always have the latest code changes (plus, you can safely hide the source mdb/mde file so the user never sees it!) You can again, copy new code at any time to the source mdb/mde, EVEN while the users are in the cloned mdb/mde files (since they are never in your "source" frontend file but instead, an exact clone.) All you simply "should" do is send them an email telling them you've copied new source code and that they ONLY have to close out and get right back in to see the new changes!! (using the vb shortcut.) (out of courtesy, you might want to relate your coding changes in the email to them.)
There's only 1 line of code you need to change in the vb script (LUName = "F:\MyDBFolder\MyMDBorMDEName.mde) and it's ready to use! And if you ever want to move your frontend to another location, you only have to change the location for LUName in the script and don't need to worry about all the shortcuts scattered on different user's desktops.
You can also add a shortcut on the user's desktop to this script (or just email a shortcut to the vb script) and then have them click on that shortcut whenever they want to get into the mdb/mde. When the script is run, it literally takes less than a second to clone the source mdb with the user's LoginID and open the cloned mdb/mde file (note: providing there aren't network problems which slow down copying/pasting of a file.) Otherwise, the user won't even notice any delay.
Then, simply look in the folder where the script clones to (ie. LUName) and look for a MyMDBorMDEFileName(USERNAME).ldb file and you'll know if that user is using the application (if you want to see who's currently using it.) You'll also see when the user last opened the application by simply looking at the date created on the MyMDBorMDEFileName(USERNAME).mdb (or *.mde) file. And you never have to worry about the mdb or mde being locked by another user! And you can have as many users as you want using your application - providing they all use the vb script to open it (since it only takes 1 user to lock a frontend file.)
Again, IMPORTANT - use this vb script for FRONT-END's only!
Do NOT use this script on an mdb which has the data tables in the frontend (unless they're only temp tables created in code/queries.) (ie. you must have linked MSAccess tables or SQL Server/MySQL tables in the frontend) - it's for frontend's only as you obviously don't want to clone the mdb which has the actual backend data tables.) Otherwise if you use it on an mdb which doesn't have linked data tables, it will clone the actual data tables as well and you'll end up with separate data tables.
INSTRUCTIONS: You edit the vbs script (in NOTEPAD) and simply change the 1 line for LUName = which is the location of your mde/mdb file (keep the location/file name in quotes (") as the example illustrates.). It's recommended to use the UNC name for the location but this is not a neccessity. If you have foldernames with spaces, use the ~1 for the 8 character foldername (just like you would do for a shortcut). Make sure when you save the script, you change the "Save As Type" to *.* and save it with the *.vbs extension. You can save the script in any folder. I've documented everything in the script so read the comments in the script itself.
Have each user simply click on the vb script whenever they need to get into your application instead of another shortcut to the mde/mdb front-end. 1 vb script which all of the user's can run. Running the vb script also takes less than a second for it to clone and launch the new mde/mdb front-end.
Dan Wang and myself created this script and I have never had any problems with it (except where users logged into the network with the same loginID). Note: you also want to make sure users can execute a vb script (the default). Otherwise, you edit IE (Internet Explorer) to change permissions for users to execute vb scripts.
The script uses the wscript.exe file as normal to execute any vb script.
Again, Here are more details on the advantages of using this script:
1. You'll NEVER have to worry about users not being able to get into a front-end mdb/mde file because a user locked or corrupted the front-end!
2. No need to ever use any "auto logoff or auto logout" code as it DOESN'T MATTER if the user corrupts the cloned mde/mdb file they are in. Just have them get back into the front-end via the vb script and the user get's a "fresh" cloned copy which will fix a corrupt mdb/mde file.
3. Your "source" front-end mde/mdb file will stay SAFE and READY for new code or backup at any time! It's never used! (except to clone off of.)
4. You can copy new code over the "source" mde/mdb code without making users close out of it. (Again, then just simply send an email to the user to close out and get back in (via the vb script again) at their convenience to see the new code.)
5. You can see who is currently in the mde/mdb (i.e. you'll see a file such as MyMDEApplicationPaulK.ldb in that folder). No need to get a 3rd party application to read *.ldb files to see who's using the application or use ANY other utility!! Just look in the LUName folder for *.ldb files with the user's login name to see who's currently in the app.
6. You can also tell when that user LAST got into the mde/mdb file by looking at the creation date of the mde/mdb file with the users loginID!
7. If you have a lot of make-table queries, this is perfect as each user is in their own front-end so there's no issues with tables being locked or needing to run scheduled compact/repair routines. The next time the user get's into the mde/mdb via the script again, they will have a lean (ie. no data in temp tables), non-bloated application which doesn't need to be compacted and repaired.
8. Users can create a shortcut to the vb script on their desktop and just click on the short-cut (when executed, it will clone the mde/mdb file, add the users windows loginID, and automatically launch the cloned mde/mdb file with the users loginID.) You can simply email a shortcut to the vb script to have users run.
9. You can tell if the user is working with the latest code changes! If MyAppName.mde is 6500 KB and MyAppName(USERNAME).mde is 6300 KB, you know they are not working with the latest code changes. Just have them close out and click on the vb script again.
10. You'll have less problems with code that you would with multiple users in the same front-end! (since again, each user is essentially in their OWN front-end.)
11. The script doesn't necessarily need to be used for just MSAccess files. You can make a few modifications to it and utilize this script to clone ANY other file and run them! (See documentation within the script!) For example, it's great for having a source *.doc file and then having multiple users running the script to each make changes in their own *.doc file (just make sure they then save that *.doc as another name so it doesn't overwrite their changes with the source *.doc the next time the script is run.)
Special thanks goes to Dan Wang for helping with the initial code.
I hope you find this script as useful as I have! I use it quite a bit for not just MSAcces applications, but other file types as well. If you find it useful, you can email me at: pkohn@charter.net.
'vb script created by pkstormy -- make sure to save as *.vbs extension so it can be executed.
'Use notepad to create this and just copy/paste the code below into a notepad file. Again, save as *.vbs extension.
'note: when saving, change *.txt to *.* files and again, save as *.vbs file (ex: MyAppName.vbs)
'Then just have the user's double-click on the MyAppName.vbs file to execute instead of your MSAccess file.
Set WshNetwork = CreateObject("WScript.Network")
'Now simply grab the user's windows loginID and save to variable GetUser to use later in pasting
GetUser = WshNetwork.UserName
Set WshNetwork = Nothing
'change below LUName to point to the location of your "source" MSAccess file, ideally use UNC name
LUName = "c:\HelpForOthers\PKSampleXP.mde"
oldname = LUName
'change below to "accdb" or "accde" or "mdb", whichever you're using. Below simply adds the GetUser to newName variable
'ex: PKSampleXP.mde becomes 'PKSampleXP<LoginID>.mde for newName variable
newName = Replace(LUName, ".mde", "") & GetUser & ".mde"
retval = 0
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
'below simply copies a "source" file (oldname to newName) and pastes it
retval = objFSO.CopyFile(oldname, newName, True)
Set objFSO = Nothing
Dim objShell
Set objShell = CreateObject("Wscript.Shell")
'now it just simply opens the cloned copy (ie. PKSampleXP<LoginID>.mde)
objShell.run "MSAccess.exe " & newName
Set objShell = Nothing
Added note courtesy of Brad Davison:
If you want to delete the cloned file, you can experiment using the code below at the end of the vb script:
This seems to work: objShell.run "cmd /K del " & newName & " & exit"
(again, experiment with the above code as I haven't thoroughly tested it myself)