Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: To StarTrekker on the vb script

    StarTrekker - see this post: http://www.dbforums.com/showpost.php...6&postcount=13 regarding the vb script info you requested. Please give it a test - you won't be disatisfied (gauranteed or I'll buy you a six-pack.)

    Any others which would like to see some neat ways to use vb scripts with MSAccess should take a look. There's a lot of neat things you can do with vb scripting in conjunction with MSAccess (ie. open a recordset and write data to a table via a vb script without getting into MSAccess). The original link with all the comments on what it can do can be found here: http://www.dbforums.com/showpost.php...6&postcount=19 or an trimmed down example without all the comments in the above link for StarTrekker (and other ideas on it's use.)
    Last edited by pkstormy; 04-15-08 at 03:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    That's the same one I have It's 15 lines of code

    I can recommend an upgrade:

    objShell.Run "MSAccess.exe " & newName
    This line is a problem if your username has a space in it.

    objShell.Run "MSAccess.exe " & Chr(34) & newName & Chr(34)
    This works better

    Yes! I used it today... I changed a site to use this method of rollout to get first hand experience with it. I have to say that I like it. It definitely has advantages. Updates are a piece of cake. Being able to easily see who's logged in is awesome! Automatic corruption issues would be handled by restarting too... it's a much better way to go, providing the network speed can cope with it.

    Avoiding "bloating" issues is also great, but that's kinda offset by the disk space that the multiple user versions take up.

    I found that I still have to store the .ICO file locally tho, which kinda sucked... I guess I'll have to keep looking for an answer for that one. I didn't get a lot of time to think about that though. I also found that now, users have to deal with a "do you want to open this file" issue, but I think I have that cracked... I just need to test it next time I am there.
    Last edited by StarTrekker; 04-15-08 at 05:55.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Apr 2003
    Location
    Austin, TX
    Posts
    21
    Our Access 2007 FE/BE system uses VB script to compare a master FE database to a User's FE on their C-drive and then swaps the newer master version for the older user version. Our system is currently only allowing access to single users vs multiple users as it was designed for. When more than one user tries opening the database they get the Run-time error 3045: "Could not use *BackEnd Database*; file already in use." I've checked all the file permission issues and that doesn't seem to be the problem. This error message began appearing about the same time our IT folks began changing all the desktop computers from Windows XP to Windows 7. There are about 15 of the 50 computers yet to be converted. With all that being said, the VB script provided by pkstormy at http://www.dbforums.com/6333066-post13.html seems interesting, but I cannot open the sample winzip file attached to the post. Is it possible to repost the zip file. Thanks in advance for any help.

  4. #4
    Join Date
    May 2009
    Posts
    104
    I'm not an expert with VB but i tried this out and so far it's great. But before i used a shortcut to run a macro that opens two forms instead of opening up to the main switchboard. I do this to restrict the end users to just those two form because of simplicity. I used this code in the short cut to run the macro when program was started.

    Shortcut code:
    Code:
    "D:\Microsoft Office\Office12\MSACCESS.EXE" "U:\QC\FinalInspection\FinalInspection.accdb"  /x "ProdJob"
    The code I derived form your example looks like this:
    Code:
    Set WshNetwork = CreateObject("WScript.Network")
    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing
    
    LUName = "C:\Users\kyleblackwell\Desktop\FinalInspection.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****n "MSAccess.exe " & newName
    
    Set objShell = Nothing
    How do I do this within VB. I tried a few things but they don't seem to work.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Are you sure about the way you compute newName? I don't understand the logic of this.

    If my user name is Sinndho, with your method, newName will be:
    "C:\Users\kyleblackwell\Desktop\Sinndho.accdb"
    Have a nice day!

  6. #6
    Join Date
    May 2009
    Posts
    104
    Sorry i posted the wrong script
    Code:
    Set WshNetwork = CreateObject("WScript.Network")
    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing
    
    LUName = "U:\QC\FinalInspection\FinalInspection.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****n "MSAccess.exe " & newName
    
    Set objShell = Nothing
    This works fine, copies the database and runs the copy, making it so i can make changes and have the update when the database is opened next time. but essentially i want to fire off an access macro when i run this.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use the /x macroname command line switch. See: Startup command-line options - Access - Office.com
    Have a nice day!

  8. #8
    Join Date
    May 2009
    Posts
    104
    The shortcut i use now uses
    Code:
    "D:\Microsoft Office\Office12\MSACCESS.EXE" "U:\QC\FinalInspection\FinalInspection.accdb"  /x "ProdJob"
    The VB script code I tried looks like:
    Code:
    Set WshNetwork = CreateObject("WScript.Network")
    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing
    
    LUName = "U:\QC\FinalInspection\FinalInspection.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****n "MSAccess.exe " & newName /x "ProdJob"
    
    Set objShell = Nothing
    But this doesn't work. If this is too complicated i'll start a new thread. I don't want to take over pkstormy's thread.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Code:
    ... "MSAccess.exe " & newName & " /x ProdJob"
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    May 2009
    Posts
    104
    That's it. Thank You

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •