Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 56
  1. #16
    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

    Quote Originally Posted by viper View Post
    In regards to 'Grades~1', I have never used this expression so I'm not sure if that would work or not.
    That would only work if that is also the value Windows has in the 8+3 name. Having embedded spaces in the name should also work.

  2. #17
    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
    Quote Originally Posted by RuralGuy View Post
    Having embedded spaces in the name should also work.
    Allen, thanks for jumping in on this issue. The original DB path was as follows:

    G:\Lesson Card Database\LCDBv12rc5.mdb

    Everytime I run the 'Golden VB Script' I get the following series of error messages:

    Code:
    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.
    Then I click 'OK' and Microsoft Access starts to open and the following error message appears:

    Code:
    Microsoft Office Access can't find the database file 'G:\Lesson.mdb.'
    Make sure you entered the correct path and file name.
    So, it appears that the vbScript is missing everything after the first space in the pathname. It is not recognizing the embedded spaces.

    Now if I rename the path to:

    G:\LessonCardDatabase\LCDBv12rc5.mdb

    all works fine so I know it has something to do with the spaces in the folder name. What I don't know is can I solve this issue with code modifications.

    As always, just looking for a little spark for my fire; not water for my bucket.

    Sean

  3. #18
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Hi Sean,

    In the posting, there's an important note regarding using spaces in LUName. If you use spaces anywhere, the script won't work. Any type of VB Scripting doesn't like spaces in the path's name since it is DOS based. Try Allan's suggestion to see if that works.

  4. #19
    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
    Quote Originally Posted by pkstormy View Post
    In the posting, there's an important note regarding using spaces in LUName. If you use spaces anywhere, the script won't work. Any type of VB Scripting doesn't like spaces in the path's name since it is DOS based. Try Allan's suggestion to see if that works.
    pkstormy, thanks for the response. I did in fact see that message but I also remembered seeing the below message so I thought that there might be other ways to make this happen.

    Quote Originally Posted by pkstormy View Post
    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).
    Understand the vbscript limitation. I just need to get the IT guys to rename some folders on the network to accommodate this shortcoming.

    Thanks for taking the time to put together this code. It is just what I needed to distribute my database across our network.

    Sean

  5. #20
    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
    Good luck and have fun!

  6. #21
    Mr.E is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    2
    Question for you guys regarding this script:

    the script seems to be working fine in creating a clone (I can see it ine the folder) but I'm getting an error "The database is in an unrecognized format."

    If I then click on any MS Access link/icon I will get the same message, however, if I open MS Access and map to the file it will then open.

    Do you think this is the script or do you think there is something amiss with the install of MS Office/Access?

    I'm running XP Pro SP 3 and MS Access SP 2 ver:11.6566.8132.

    The code looks like this:

    Set WshNetwork = CreateObject("WScript.Network")

    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing

    LUName = "H:\TestEPDB\DB2.mde"
    oldname = LUName

    newName = Replace(LUName, ".mde", "") & GetUser & ".mde"
    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

    Thanks for your help,

    E

  7. #22
    Exwarrior187 is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    13

    Question

    ^^ Mr. E.

    Looks to me like it's trying to open your file with the ".mde" extension when it's probably ".mdb".

    Your code:

    LUName = "H:\TestEPDB\DB2.mde"
    oldname = LUName

  8. #23
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Quote Originally Posted by usmcgrunt View Post
    pkstormy, thanks for the response. I did in fact see that message but I also remembered seeing the below message so I thought that there might be other ways to make this happen.

    Sean
    Sean -

    Regarding:

    LUName = \\elan87vm\g:\MyDatabaseFolder\test\LCDBv12rc5.accdb


    I was reviewing these posts and I just noticed something else in your LUName which would cause the script to error. You have an accdb file versus an mde. If this is the case, you also need to change the following line in the script:

    newName = Replace(LUName, ".mde", "") & GetUser & ".mde"
    to
    newName = Replace(LUName, ".accdb", "") & GetUser & ".accdb"

  9. #24
    Mclaren is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2010
    Location
    Johannesburg
    Posts
    164
    Man, you have no idea how this simplifies updating front ends.

    You may want to update comments in the script for use with 2007 and 2010 versions of access.

    Works a treat, right out the box (well apart from changing the path details etc)

  10. #25
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Thanks - I appreciate the feedback!

  11. #26
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Is there an updated version of this with the above bugs/issues/suggestions added/changed?

  12. #27
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Quote Originally Posted by TheShabz View Post
    Is there an updated version of this with the above bugs/issues/suggestions added/changed?

    Do you mean this part that's in the comments:

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

    For example (for an accdb)
    newName = Replace(LUName, ".accdb", "") & GetUser & ".accdb" <- but don't clone backend accdb file!!

    For an accde file:
    newName = Replace(LUName, ".accde", "") & GetUser & ".accde"

    For an mdb file:
    newName = Replace(LUName, ".mdb", "") & GetUser & ".mdb" <- but don't clone backend mdb file!!

    and for an mde file:
    newName = Replace(LUName, ".mde", "") & GetUser & ".mde"

    Sorry about that - I should've been a bit more clearer on the comments on that part.

  13. #28
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    As a last note:

    Be careful using this....

    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)

    I have not done this and this can have undesiriable affects. I might suggest creating a vb script that perhaps runs at midnight to delete the cloned files if you find the need to delete these files. I myself leave the cloned files as they provide some benefits for me. ** 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.

  14. #29
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Here's another suggestion I recommend along with using the script:

    Since I maintain about a dozen different mde frontends, I create one 'Main Menu' mdb which has buttons in it to launch each of the vb scripts. This way the user only needs to open one mdb file (which doesn't really need to be cloned but can).

    An example of code for one of the buttons in the Main Menu is such:

    Private Sub cmdQuitlineEval_Click()
    Call Shell("wscript.exe F:\Data\Databases\QuitlineEval\QuitlineEval.vbs")
    End Sub

    (IMPORTANT NOTE: Since running wscript depends upon the Intranet setting in IE, you may need to edit your intranet setting and make sure your network drive is mapped as a trusted site. Otherwise the user will get a 'Security File' warning whereby they can click 'Run' and it will still run the vb script - just a nusance.)

    See attached picture:

    When I need to make a change to one of the programs, I make the changes in my development folder, create a new mde file, and then copy/paste that mde file to the LUName folder and I'm done. Out of curteousy, I'll then send out an email to users to close the app (at their convenience unless it's a criticial change) to simply close and re-open the app via the button on the Main Menu.

  15. #30
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    I also recommend a program called EXEScript. This program works very nicely and will convert a vbs script into an exe file. Price I think is around $40.

    The goal though is to prevent users from finding the MyApp<UserName>.mde file and opening that (since they are bypassing the cloning process run from the vb script and may not be getting the latest source code.) That's one reason I put the cloned files in a separate folder from where the vb script is located. Users should only open the vb script (or exe file if created via the ExeScript program) each time they want to open the program (or use the Main Menu program demonstrated in the previous post).

    Note also: The cloned folder must have read AND write permissions for all users, otherwise it will lock the *.ldb file (for just that user - but it won't affect other users as it would when multiple users share the same mde or accde file.) The beauty of this script is that you can copy code at any time without making users close out of the app and if a user's cloned copy get's corrupted for some reason, simply have them re-run the vb script and it will automatically correct itself with a fresh new cloned copy from the source. (nothing you need to do on your end.) This script has saved me many headaches and hours from having to track down multiple users to find the 1 specific user that has their mde file open and is locking everyone else out of the app. I hope it makes things just as easy for you. Feel free to send any comments my way.

Page 2 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, 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