Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    Unanswered: vb script clone for runtime users

    hello, i have a multi user database that has been split into a FE and BE. users are finding the FE a bit slow and thought the following vb script would fix this problem.

    http://www.dbforums.com/6274786-post19.html

    my modified code as below:

    Code:
    Set WshNetwork = CreateObject("WScript.Network")
    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing
    
    LUName = "G:\Databases\_AAA Development\development_qprm.mdb"
    'Note: or LUName = "UNC Name"
    oldname = LUName
    
    newName = Replace(LUName, ".mdb", "_") & GetUser & ".mdb"
    
    retval = 0
    Dim objFSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    retval = objFSO.CopyFile(oldname, newName, true)
    
    Set objFSO = Nothing
    
    Dim appAcc
    Set appAcc = CreateObject("Access.Application")
    
    appAcc.OpenCurrentDatabase newName
    
    appAcc.Visible = True
    
    set appAcc = Nothing
    the code works fine for users with full access but those users who only have runtime aren't able to open it. they get the error 'ActiveX component can't create object: 'Access.Application'

    any help would be greatly appreciated.
    thankyou

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The bottom 5 lines of the script looks like different coding versus what I posted in the code bank. Is this coding you needed to do to utilize it for runtime users?

    Dim objShell
    Set objShell = CreateObject("Wscript.Shell")

    objShell***n "MSAccess.exe " & newName (where *** = dot ru)

    Set objShell = Nothing

    Have you tried users opening the mdb/mde (those running the runtime) without using the script? The ActiveX component sounds like a problem with the ActiveX component used in the MSAccess application not being registered on the computers who are using the runtime versus a problem with the script. This is typically registered when the office package is installed.

    I haven't tested the script though on computers with just the runtime. It seems to me though there wouldn't be a reason the script would be problematic since all it does is copy/paste the mdb/mde file and then open the pasted copy (with the user's LoginID).

    I might have the runtime users 'manually' try copying and then pasting the mdb/mde and then opening the pasted copy. If it works when they open the non-pasted copy but doesn't work on the pasted copy, you may need to find a work-around if you want to utilize the script. If you only have a couple of users on the runtime and the script (ie. copying/pasting) is the problem, it may be worth it to just have separate mdb/mde files for those users.

    It's also possible the GetUser isn't grabbing the correct LoginID of that user. I might put: msgbox GetUser under the:

    Set WshNetwork = CreateObject("WScript.Network")
    GetUser = WshNetwork.UserName
    msgbox GetUser

    just to see what username it's grabbing. I've seen issues when some user's login to a specific box all under the same LoginID (ie. GetUser). If no GetUser is returned, that would be problematic for the rest of the script.
    Last edited by pkstormy; 03-23-10 at 23:40.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2010
    Posts
    7
    i had to change the last 5 lines as i was getting an error message saying 'The command line you used to start Microsoft Access contains an option that Microsoft Access doesn't recognize.' and then i get another message saying 'couldn't find file 'System.mdw'. This file is required for startup.

    after i changed the code it worked fine for full access users.

    for runtime users the code is copying the file ok, it just has an issue opening a database. i even tried opening the main database rather than the copied file but i still get the same result.

    any ideas as to any other code i could use to open a database?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm confused - So it's the 'copied' file of the mdb/mde that isn't working? Otherwise opening the source mdb/mde works fine if the script is not used?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    And a system.mdw file is also in the picture?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I never liked dealing with system.mdw file issues and the runtime version of MSAccess (along with other issues). We abandoned the runtime on user's computer and just had them buy Office.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Mar 2010
    Posts
    7
    sorry for the confusion.

    runtime users can open the database (copied or original) fine if script isn't used. when they open the .vbs file the database copies ok but errors when script tries to open a database (copied or original).

    the system.mdw error only shows up when i use your last 5 lines of code, hence why i changed it.

  8. #8
    Join Date
    Mar 2010
    Posts
    7
    i would love for everybody to have full access but unfortunately this is not an option.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Did you try the msgbox GetUser?

    Does that return the correct username?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by schweie View Post
    sorry for the confusion.

    runtime users can open the database (copied or original) fine if script isn't used. when they open the .vbs file the database copies ok but errors when script tries to open a database (copied or original).

    the system.mdw error only shows up when i use your last 5 lines of code, hence why i changed it.
    I don't understand this part:

    "when they open the .vbs file the database copies ok but errors when script tries to open a database (copied or original)."

    The script doesn't open the 'original', only the cloned copy.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ok. I think I see. the script doesn't work with the system.mdw....correct? (or any created via the script). But are you using the mdw parameter to open the mdb then?
    Last edited by pkstormy; 03-24-10 at 00:36.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    That might make sense....hmmm....

    Since opening an mdb with an mdw is dependant upon opening it via the parameter pointing to the correct system.mdw file, I might find a way in the script to utilize the same system.mdw file as a parameter in this code:

    Dim appAcc
    Set appAcc = CreateObject("Access.Application")

    appAcc.OpenCurrentDatabase newName

    Perhaps something like...

    appAcc.OpenCurrentDatabase newName /G:\MyMDWLocation\system.mdw

    or something like that. You'll have to play with the syntax. You may even be able to add something such as:

    newName = newName & "/G"\MyMDWLocation\system.mdw" or something like that. (Note: this may have also worked with the original code.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Let me know if the above works. I'm 'guessing' the copied mdb/mde file perhaps isn't utilizing the system.mdw file for permissions. If you used MSAccess security (ie. created a system.mdw file), a reference to that mdw file needs to be in the shortcut (or script coding) as a parameter.
    Last edited by pkstormy; 03-24-10 at 00:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Mar 2010
    Posts
    7
    i only get the system.mdw error with the initial code you had. my altered code works fine for full access users.

    everything works fine for runtime users until it has to open the database. so really it's only the createobject line that it gets stuck on. so i'm assuming runtime users don't support this. but i can't find an alternative.
    Code:
    Set appAcc = CreateObject("Access.Application")

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When the so-called "runtime" version of Access is installed on a computer, Access is not registered as an OLE server, then you cannot create an instance of Access using VbScript, which is what CreateObject("Access.Application") tries to do.
    Have a nice day!

Tags for this Thread

Posting Permissions

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