Page 1 of 4 1234 LastLast
Results 1 to 15 of 56
  1. #1
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682

    *** The Golden VB Script that will make your life much easier

    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)
    Last edited by pkstormy; 08-31-2010 at 07:01 PM. Reason: Added stars

  2. #2
    taylorhouse is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    2

    Windows Script Host Error

    HI I have just tried your script and it copies the database fine.

    But does not run i get the following error

    Script:S:\New NCR Database\Start NCR External Db.vbs
    LIne:35
    Char:1
    Error:Type mismatch: 'objShellrun'
    Code:800A000D
    Source:Microsoft VBScript runtime error

    Do you have any idea what is wrong?

    Thanks
    Darren

  3. #3
    taylorhouse is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    2
    Hi I found out what the problem is.

    I cut and paste the text from the post and there is a typo at the end

    objShellrun "MSAccess.exe " & newName

    instead of

    objShell.Run "MSAccess.exe " & newName

    The sample zip file is ok

    Many Thanks for the script

    Darren

  4. #4
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Great catch!

    The post is locked though so I cannot fix it.

    I recall now that objshell.run was a problem on some websites and would display as objshell**** instead of objshell.run

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by pkstormy View Post
    I recall now that objshell.run was a problem on some websites and would display as objshell**** instead of objshell.run
    I edited your post for you. Did I get it right?

  6. #6
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Actually Allan, it now shows the **** again.

  7. #7
    viper is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    26
    This is a great script; however I was having problems running the script. I kept getting an error, the script would work great up until it tired to run the MSAccess.exe.

    Here's the error I was getting.
    The command line you used to start Microsoft Office Access contains an option that Microsoft Office Access doesn’t recognize. Exit and restart Microsoft Office Access using valid command line options.


    For any one who ran into the same problem as I did, I had to add a ".open" to the end of the code that would run Access and open the newName of the file. It now works just fine for me, but I wanted to share in case any one else was running into this problem.

    Code:
    objShell.Run "MSAccess.exe " & newName.open

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Thanks Viper.

  9. #9
    viper is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    26
    My apologies for my premature post about solving my problem earlier. It did work the first few times I opened the .vbs but now all I get is a runtime error listed below that points to the line that I refer to in my previous post. I've looked up possible reasons why and some state on other forums that you have to define the object but i'm not sure where or how to do this. If any one has some ideas I'd be grateful.

    Thanks

    Error: Object required: 'newName'
    Code: 800A01A8
    p.s. heres a copy of my code as it reads currently

    Code:
    Set WshNetwork = CreateObject("WScript.Network")
    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing
    LUName = "c:\Documents and Settings\bmetcalf\My Documents\New Folder\Fire.mde"
    oldname = LUName
    newName = Replace(LUName, ".mde", "") & GetUser & ".mde"
    retval = 0
    Dim objFSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(oldname, newName, True)
    Dim objShell
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run "MSAccess.exe " & newName.open
    Set objShell = Nothing

  10. #10
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    A couple of things....
    Set WshNetwork = CreateObject("WScript.Network")
    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing
    LUName = "c:\Documents and Settings\bmetcalf\My Documents\New Folder\Fire.mde"


    *** AVOID Spaces ANYWHERE in LUName!!! This will NOT work!!! ***


    oldname = LUName
    newName = Replace(LUName, ".mde", "") & GetUser & ".mde"
    Make sure the above is .mde for mde files or .mdb for mdb files or .accdb for accdb files, etc...
    retval = 0
    Dim objFSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(oldname, newName, True)
    Dim objShell
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run "MSAccess.exe " & newName.open
    should be...
    objShell.Run "MSAccess.exe " & newName (although as viper stated above, newName.open does work - thanks viper.)
    Set objShell = Nothing
    Last edited by pkstormy; 11-27-2010 at 04:22 PM.

  11. #11
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    pkstormy, are you still monitoring this thread? If so I need some help getting it to work. I keep getting error 800A0046 permission denied from Line 27

    Code:
     
    retval = objFSO.CopyFile(oldname, newname, True)
    I am on a LAN and I am not an administrator. I think it might have something to do with the location of the FE DB. See below:

    Code:
    LUName = "\\elan87vm\g:\MyDatabaseFolder\test\LCDBv12rc5.accdb"
    Will this vbscript work with an address like the above?

    Thanks, Sean

  12. #12
    viper is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    26
    Quote Originally Posted by usmcgrunt View Post
    I am on a LAN and I am not an administrator. I think it might have something to do with the location of the FE DB. See below:

    Code:
    LUName = "\\elan87vm\g:\MyDatabaseFolder\test\LCDBv12rc5.accdb"
    Will this vbscript work with an address like the above?

    Thanks, Sean
    The "g:" drive letter is usually unique to the individual computer your using, when trying to access a file threw the server there shouldn't be any drive letters. Use My Network Places to navigate threw your network to the location of your database then look at the path name at the top of the window. When you write your script it needs to use the exact same path. Hope this helps

  13. #13
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    Viper, thanks for the reply. I did what you recommended but I am still getting a permission denied error - 800A0046 from line 27 same as before.

    My new LUName is:

    Code:
    \\Elan86vm\Grades~1\MyDatabaseFolder\test\LCDBv12rc5.accdb
    I had to shorten the actual name of the main folder that the DB resides in because it has spaces. The actual folder name is 'Grades and Student database'. Should 'Grades~1' be working?

    I am trying to get the IT Dept to change the name but I am in the military and my IT guy is a Sergeant with basic training in network administration so that might be a stretch.

    Thanks, Sean

  14. #14
    viper is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    26
    I looked up your error code at that one deals with permissions with specific files. Do you know/find out if you have "Read & Write" permissions to the folder you are using to store the database? Because essentially with this script you are constantly modifying the database and a Read Only permission would not make this work.

    http://www.computerperformance.co.uk/Logon/code/code_800A0046.htm

    One suggestion that I can give is try this script out on your local pc first. Store the database on your local hard drive and set the path in your script to match. Once you get it to work without any servers or permissions to get in the way you can then focuses on other problems.

    In regards to 'Grades~1', I have never used this expression so I'm not sure if that would work or not.

  15. #15
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    Viper, Thanks for the link. So, the vbscript works on my computer so I am almost positive that it has something to do with the fact that the folder on the shared drive has spaces in the name. The test on my computer resulted in the same errors but when I created a folder with no spaces in the name all works as it should.

    Thanks for all your help. The rest of the work is on my end.

    Sean

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 08-11-2010, 09:20 PM
  2. launching migration script
    By MrGrinch12 in forum Programming
    Replies: 0
    Last Post: 06-23-2010, 08:28 PM
  3. VBA Script to run select query
    By pushpm in forum Programming
    Replies: 2
    Last Post: 05-06-2009, 08:36 AM
  4. Login Script
    By theITguy in forum Access
    Replies: 2
    Last Post: 03-06-2009, 03:37 AM
  5. Open Accesss DB by batch script
    By micada in forum Access
    Replies: 0
    Last Post: 06-10-2008, 02:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums