Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 49

Thread: The Golden VB Script that will make your life much easier

  1. #31
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    643
    Here's an example of what a cloned folder for the QuitlinePhaseII.mde file might look like (ie. F:\Data\Databases\Quitline\DB)


    The vb script is a button clicked on in the Main Menu (see above post or Main Menu below where they can click the Quitline (Phase II) button to open the app) or users can run the Quitline7MonthMadison.vbs script in F:\Data\Databases\Quitline folder.

    Users are taught not to get into the F:\Data\Databases\Quitline\DB folder (where all the cloned files are - this could be buried even further in more folders if need be) and users are told not to open any of the files in that folder. (If possible and ideally, this folder would be hidden.)

    You'll notice that I can easily tell which users are currently using the Quitline program by looking for an *.ldb file associated with their loginID in the name of the file. When the user closes the file, the *.ldb file will go away and a new one generated the next time they open the program via the script. Looking at this folder tells me exactly who's currently in the application and when they last got into it. I can also tell if they got into the latest version by looking at the source (called: QuitlinePhaseII.mde) and compare the date/size on that file with the date/size of the file with the user's loginID within it. It doesn't hurt to leave these files within the ...\DB folder. They will get overwritten whenever the user opens the vb script next. If a user's *.ldb file happens to get corrupted for any reason (which will not lock out or affect any other users), they simply re-open the application using the *.vbs script or button and it will self-fix the corrupted *.ldb file.

    There are pro's and con's to leaving the cloned files in the folder. I personally like the pro's of leaving them in the folder so I can see when the user last opened the app. If need be though, a routine could be designed to run to delete all the cloned file (but leaving the source file.)

    Also notice that there is never a need to make the users close out of the application to copy new source (mde) code (since this is never opened but instead, used as a source to clone off of). The mdb file can be worked on in the development folder while users are in the application. An mde file is then created and copied to the \DB folder (again, while users are still in the application). I will then send out an email to all users telling them that new source code has been copied and (at their convenience) to close out of the app and simply re-open it again to see the latest changes (using the button on the Databases Main Menu or running the *.vbs script.) There is also no limitation on the number of users who can open the application since every user is essentially in their own cloned copy of the source code.
    Attached Thumbnails Attached Thumbnails clonedFolderForQuitlineXPPhaseIImdefile.png   vbscriptForUsersToRun.jpg   CTRIMainMenu.jpg  
    Last edited by pkstormy; 07-27-2011 at 08:24 PM.

  2. #32
    benjammin is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Nov 2010
    Posts
    60
    This is awesome, helps avoid all sorts of pitfalls i was up against.

  3. #33
    Dunro is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Toronto, Canada
    Posts
    38
    Thank you for this code!

    I was actually using a Batch file to do something similar, but I'd rather stick to one language with this project.

    I did make a few tweaks to the original code - firstly to allow for spaces in file names, and second to delete the cloned file once it was closed.

    Code:
    objShell.run "MSAccess.exe " & newName
    was replaced with:

    Code:
    temp = objShell.run("MSAccess.exe " & chr(34) & newName & chr(34),,True)
    ' Added chr(34) quotes to allow for spaces in file name
    ' Run the cloned file, pause the script until after Access is closed
    
    ' Delete the cloned file
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    If (objFSO.FileExists(newName)) Then objFSO.DeleteFile(newName)
    set objFSO = nothing

  4. #34
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    643
    Thanks for this line of code Dunro!

    temp = objShell.run("MSAccess.exe " & chr(34) & newName & chr(34),,True)

    (and to delete the existing cloned file - nice code!)

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    If (objFSO.FileExists(newName)) Then objFSO.DeleteFile(newName)
    set objFSO = nothing

    Yes! - The above chr(34) should handle problems with spaces in the folder/file name (and "should" also work with using the UNC name - untested yet.) Again - Thanks for supplying this!

    Using other odd characters in the folder/file name such as: !@#$%^&*() has also not been tested and I would discourage any folder/file names like:

    F:\Some DB Folder#1\DB Folder$\Some dbName!!.mde

    and instead keep it simple:

    F:\Some_DB_Folder\DB\SomeDBName.mde

    One comment on using this:
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    If (objFSO.FileExists(newName)) Then objFSO.DeleteFile(newName)
    set objFSO = nothing

    I haven't tested the above code on what would happen should there be a hanging *.ldb file when the user exits the db and re-runs the script (ie. should the Access mde/accde stop working suddenly for the user and they kill the MSAccess task or they lose their connection to the mde). It should work ok though. Again, Thanks for supplying this code!

    Also keep in mind you could possibly get the error "MSAccess cannot find the file called: MyAppNamePaKohn.mde" if adding this code in the script to delete the cloned file right after it is created.

  5. #35
    ScottyPottie is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    1

    I got FileNames with Spaces to Work

    Thanks for the update

  6. #36
    realtammie is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    3
    Hi pkstormy,
    I have just found your thread and it is great however i am unsure on one thing regarding deleting the cloned file you state

    "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)"

    I will have around 16 users accessing cloned files and I would like to be able to delete the cloned file when they close the mdb when they are finished. could you explain how i could get this to work please, it would be much appreciated.

    Kind Regards

    Paul

  7. #37
    realtammie is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    3
    OK, ignore my previous post this is my first time in this forum and I did not read all of the posts. Dunro's post actually answers my question

    Regards

    Paul

  8. #38
    realtammie is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    3

    clone files to seperate folder

    Hi pkstormy, me again how do i go about doing this

    "Note also that I have it so it creates the cloned files in a separate folder by itself so as to not get mixed in with any other files."

    Kind Regards

    Paul

  9. #39
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    643
    Just change the LUName to the folder/db name that you want the clone to copy to. Note that the source mde/accde must also be in this same folder. Then you have your mdb/accdb in YOUR \Developers\ProgramName folder (that only you have access to). To copy new code at any time, you then make changes in your mdb/accdb file (which no other users should be in to interrupt any of your coding by locking the mdb/accdb file). When done making your changes, simply debug/compile, compact/repair (I do this as routine) and create a new compiled mde/accde file. Then copy that mde/accde file to the LUName folder. That's it. The last thing I do out of courtesy is send all the users an email that they can close and re-open the program (using the vbscript) and they will see the new changes (and I list the changes in the email). If I made any critical changes, I can look at the LUName folder and see who is currently using the Access file (I look for an lde or laccde file extension and the date it was created and email them to close immediately. Note: if the date modified for the file is an older date, the Access file most likely hung on the user a while back and they just never re-opened. This is also a great way to track lockups for users and the date/time you had copied new code (just compare it to the source mde/accde date).) If you make changes regularly like I do (I maintain 15 different programs), this makes it a blessing to not have to go around and make each user close out of the Access file so I can make coding changes or deal with lockup issues (I just tell them to re-run the vbscript which will re-create the mde/accde and remove the lock file.) I also have a "Databases Main Menu" which has buttons to run all the vbscripts for each program so users don't have to remember where they are. Some will want shortcuts to the vbscript directly on their desktop (they usually do). The nice thing is that if I ever want to move the db, I only have to change the LUName in the vbscript file (and delete all the 'copy of vbscript' files in the folder that users mistankenly created when making a shortcut of it to their desktop).

    This may just be my situation but I think the vbscript can really be beneficial for any MSAccess multi-user environment.

    Quote Originally Posted by realtammie View Post
    Hi pkstormy, me again how do i go about doing this

    "Note also that I have it so it creates the cloned files in a separate folder by itself so as to not get mixed in with any other files."

    Kind Regards

    Paul

  10. #40
    mask207 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    1
    Hi everybody, this "Golden Script" is what I'm looking for. I have followed the instruction and this is my code

    Set WshNetwork = CreateObject("WScript.Network")
    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing
    LUName = "c:\ghadmin.accdb"
    oldname = LUName
    newName = Replace(LUName, ".accdb", "") & GetUser & ".accdb"
    retval = 0
    Dim objFSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(oldname, newName, True)
    Set objFSO = Nothing
    Dim objShell
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run "msaccess.exe " & newName
    Set objShell = Nothing

    I have a accdb file named "ghadmin" in C drive. However, I got error "The command line you used to start Microsoft Access contains an option that Microsoft Access doesn't recognize"
    and then a blank access window pop with error "Microsoft Access can't find the database file 'c:\ghadmin.mdb.' "

    It's strange to see access trying to open ghadmin.mdb when I have ghadmin.accdb in vbs code. Can someone help me to point out the problem ? I'm using Access 2010 64bit.

  11. #41
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    643
    The error you're describing is one you may get when it can't find MSAccess in whatever folder it resides. You may try clicking the "Start' button, then "Run" and just type in MSAccess in the run box to see if will launch MSAccess. Or make sure MSAccess is setup as the default for opening any *.accdb files. You can also try this for objShell.run command:
    objShell.Run "C:\Program Files\MSAccess folder\Msaccess.exe " & newName. When MSAccess is installed in a different folder (versus the default folder) or isn't set as the default program to open a *.accdb file will sometimes give you the error you described. I found that with Windows 7 and the different folders Office sometimes gets installed in can be problematic, especially if there are different versions of Office installed.

  12. #42
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    643
    Paul - I really wouldn't recommend trying to delete the cloned file in the script. I would instead create another vb script which runs as a nightly task (in task scheduler) which would delete cloned files (making sure not to delete the source accde/mde file). I personally don't try to delete the cloned file and find them useful to see who's currently using the accde/mde or when they last opened it. The deleting of the cloned file coding is not code I created and was posted by someone else. I typically avoid any auto-deleting type code as well as auto-compacting/repairing or other code like this. Sorry but I can't be much help on doing this.

  13. #43
    Dunro is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Location
    Toronto, Canada
    Posts
    38
    Quote Originally Posted by pkstormy View Post
    Paul - I really wouldn't recommend trying to delete the cloned file in the script. I would instead create another vb script which runs as a nightly task (in task scheduler) which would delete cloned files (making sure not to delete the source accde/mde file).
    Hello,

    I used this code a bit more than a year ago on one of my projects and it's still working pretty well. (Thank you to everyone else who contributed.)

    I completely agree that deleting the cloned file in the script is not the best option, and that it'd be better to use a scheduled task. There were a few reasons why I chose to include this in the same script:
    1. My "real" front end (and all back end data) is backed up elsewhere, so I'm not worried about files getting deleted.
    2. It made it easy for me to guess who was still using the database when I needed to ask them to log out*
    3. Any scheduled task still depends on me being logged into my machine to run (unless I get IT to support our database for us).

    There can be instances where Access refuses to open the front end again after a crash (even though the front end is a brand new copy). Fortunately, this has only happened once or twice since we've used it.

    * To further solve problem #2, I also added a means to boot out all users (the script periodically checks if a text file exists in a given location, if not, gives users 5 minutes to exit). Helpful for when someone forgets to shut down their computer one night and then goes off sick.

  14. #44
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    643
    Quote Originally Posted by Dunro View Post
    Hello,

    I used this code a bit more than a year ago on one of my projects and it's still working pretty well. (Thank you to everyone else who contributed.)

    I completely agree that deleting the cloned file in the script is not the best option, and that it'd be better to use a scheduled task. There were a few reasons why I chose to include this in the same script:
    1. My "real" front end (and all back end data) is backed up elsewhere, so I'm not worried about files getting deleted.
    2. It made it easy for me to guess who was still using the database when I needed to ask them to log out*
    3. Any scheduled task still depends on me being logged into my machine to run (unless I get IT to support our database for us).

    There can be instances where Access refuses to open the front end again after a crash (even though the front end is a brand new copy). Fortunately, this has only happened once or twice since we've used it.

    * To further solve problem #2, I also added a means to boot out all users (the script periodically checks if a text file exists in a given location, if not, gives users 5 minutes to exit). Helpful for when someone forgets to shut down their computer one night and then goes off sick.
    Hi Dunro

    I will also do a search for an *.lccde file (or *lde) file to see if someone has the file open. There's a neat example of code in the repository for finding out who is in the *lde file. I hate forcefully closing users out since there are some users who need to keep the db open all night while others throughout the day.

    i've seen many different setups/modifications to the script and each one works best for that company. For example, our network guy found it best to modify each of my vb scripts to instead clone the frontend to a hidden c:/databases folder he automatically creates on each of the users computer. This of course gives a much better performance of the frontend and I instead look at my "users" table in each db to see when the user last opened that specific db (I write the date/time the user opened the db to a field in my "tblSysUser" table).

    The objective of the script is to simply prevent multiple users in the same mde/accde file and if someone corrupts their front end due to a lockup, it then doesn't force me to find every user and make them close out so I can then repair the file. This setup works ideal in my situation in a hospital environment where I also must copy new code changes to 1/2 dozen different front ends within a day and there might be a dozen or so users using one or more db's at a single time which can't close out of the db because they are with a patient. For me, it saves me the time of making everyone close out so I can copy a new mde/accde file. Instead of hunting down users to make them close out, i simply copy the new mde/accde file (at any time) and then send out an email to everyone that they can close and reopen the db (when they are done with their patient) to see the new changes. It's a win, win situation for both the user and developer. Each setup for a company varies and although I use this script everywhere, it's modified slightly to fit that environment.

    Also note that the script should overwrite a corrupt *.accdb/accde file which would also have a *.lccde/lccdb file showing in the folder. I know I had problems with this happening once where it produced an error when it tried to copy over a locked up *.accde file but that ending up being due to the way the network/os had been setup where it didn't like an accde file being copied over with an existing *.lccde file in the folder. If an *.accde file keeps getting locked for a specific user where the hanging *.lccde file doesn't allow another *.accde file to copy over it, there could also be a permissions problem with the user and the cloned folder. Usually opening and closing the *.accde file removes the locked file but if it doesn't, this is often permission based to the folder.

    One thing to keep in mind when using this script, is that you are essentially bypassing MSAccess's method of keeping track of who is/is not using the MSAccess file for multiple users. Since MSAccess doesn't seem to do this very well with over 3 users and the 'tracking *.lccde" file can often get corrupt especially if a user suddenly loses connection which then prevents other users from opening that MSAccess file until all users close out of the MSAccess file so the tracking *.lccde file can delete itself and start fresh (repair itself so to speak). The script simply makes it so that everyone is in their own *.accde file thus having their own *.lccde file as well which isn't "tracking/affecting" other users in the app.

    When the script is not used and a specific user does not have read/write permissions to the folder, they tend to lock up the *.lccde file whenever they open the *.accde file, hence causing problems for other new users wanting to open the app (and everyone must close out so the *.lccde file can delete itself and start new).
    Last edited by pkstormy; 11-11-2012 at 06:24 PM.

  15. #45
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    643
    No clones on the network drive - a better method!!

    **************************************************
    Here's a method using the vb script where you can set it up to clone the source code (ie. mde/accde file) to a c:\Databases folder (preferably hidden to prevent users from finding it). Our network guy automatically creates the c:\Databases folder on everyone's computer in his computer setup routine. This really works well. There's no 'mass' amount of clones residing on the F: network drive\Databases folder. The user get's a fast interface since the accde file is directly on their c: drive and they get a fresh copy every time they run the script from the network drive. As long as there are no bottle-necks in the network file copy process, a 50 mb db should copy and open within 2-3 seconds (best to always compile your MSAccess files to a mde or accde).

    Note the only difference is the line where "newname = ...." which clones to the C:\Databases folder versus to the network (F: in our case) drive.

    This method has really worked well for us in that there are less opened cloned accde files on the network drive so our network drive/resources are not heavily tasked when 50 users open the db! And Users get a fast, fast frontend! If you use SQL Server linked tables, you've essentially eliminated any slowdowns the network drive would cause since users have direct access to the SQL Server tables from their frontend db on their c: drive.

    I wouldn't recommend trying to copy to the user's desktop though. Just put a shortcut to the vb script on their desktop or have 1 MSAccess DB Main Menu which has buttons to open each vb script file if you have multiple projects (save it to the network drive and put a shortcut to the DB Main Menu db somewhere on the network drive where everyone can access it - this is the easiest for a dba/developer to maintain versus individual scripts on everyone's desktop).

    Set WSNet = CreateObject("WScript.Network")
    GetUser = WSNet.UserName
    Set WSNet = Nothing
    LUName = "F:\Data\Databases\DualUse\DB\DualUse2010.accd e"
    oldname = LUName
    newName = Replace("C:\Databases\DualUse2010.accde", ".accde", "") & GetUser & ".accde" 'Note: for accde. change to mde if needed
    retval = 0
    Dim objFSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Copy file - this code below can be used to copy other files as well.
    retval = objFSO.CopyFile(oldname, newName, True)
    Set objFSO = Nothing
    Dim objShell
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run "MSAccess.exe " & newName
    Set objShell = Nothing
    Last edited by pkstormy; 03-02-2013 at 07:51 PM.

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

Similar Threads

  1. Replies: 8
    Last Post: 08-11-2010, 08:20 PM
  2. launching migration script
    By MrGrinch12 in forum Programming
    Replies: 0
    Last Post: 06-23-2010, 07:28 PM
  3. VBA Script to run select query
    By pushpm in forum Programming
    Replies: 2
    Last Post: 05-06-2009, 07:36 AM
  4. Login Script
    By theITguy in forum Access
    Replies: 2
    Last Post: 03-06-2009, 01:37 AM
  5. Open Accesss DB by batch script
    By micada in forum Access
    Replies: 0
    Last Post: 06-10-2008, 01: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
  •  
Tech Forums: Microsoft Office Forums