Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Second instnce of Access (Office 2k and windows 2K)

    Is it possible to run a second instance of access from code. Problem: I am downloading information from a shared network drive and appending this information to a linked table in another database. The shared drive connection is extremely slow and the download and append process takes about four to five minutes. It is set up to do this as someone logs on to the database. I would like to try to move this download and append process to a third database that is activated behind the scenes and not noticed by the user. I would like to accomplish this with vba code as the user logs on. Is this at all possible? Summary: Users log on to DB1. At the login, the DB1 goes out to a shared drive and appends data from DB2 to a duplicate table in DB1. Due to the update time involved I would like DB1 to activate DB3 and download and append info from DB2. All being invisible to the user???????????

    Thanks
    Kevin

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Second instnce of Access (Office 2k and windows 2K)

    Thank You Hans, I appreciate your response. As a quick question, is there a toggle in the command line that will allow this to run without the user seeing anything happening? (i.e. second instance does not receive focus?)

    Thanks
    Kevin

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Second instnce of Access (Office 2k and windows 2K)

    Edited later to correct error in code.

    You can use Automation to run another instance of Access.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Dim objAccess As Access.Application

    On Error GoTo ErrHandler

    Set objAccess = CreateObject("Access.Application")
    objAccess.OpenCurrentDatabase("serversharefolderdb 3.mdb")
    ...

    ExitHandler:
    On Error Resume Next
    objAccess.Quit acQuitSaveNone
    Set objAccess = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

    <img src=/w3timages/blueline.gif width=33% height=2>

    Having an error handler and releasing objects is very important if you create instances of an application in code.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Second instnce of Access (Office 2k and windows 2K)

    Kevin,

    If you create another instance of Access this way, it will be invisible and hence won't receive the focus, unless you make it visible by explicitly setting the Visible property of the objAccess object to True. You control it entirely through code.

    Note: there was an error in my original code (in the line with objAccess.Quit; I have corrected it. Sorry about that.)

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Second instnce of Access (Office 2k and windows 2K)

    Hans,
    Thanks, I picked up on the error when my compile failed. I do have a question though. It seems that the DB I am trying to open does not want to open. I get an exclamation error msgbox. I can actually see the .IDB file breifly open and then close right away. Any Ideas?

    Thanks
    Kevin
    Kevin

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Second instnce of Access (Office 2k and windows 2K)

    Kevin,

    What does your code do with the database between opening it and closing it again?

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Second instnce of Access (Office 2k and windows 2K)

    Hi Hans,
    I am not doing anything. I just took your example to see how it was going to inter act. My plan is to use an autoexecute macro to open a form with a timer and routinely download data from the shared drive.

    Thanks
    Kevin
    Kevin

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Second instnce of Access (Office 2k and windows 2K)

    If you don't have automatically executing code yet, the database will close immediately after opening - VBA just executes your routine, which opens, then closes Access. You must either put code in your routine between OpenCurrentDatabase and Quit, or use an AutoExec macro.

    Make sure you open the form with acDialog as WindowMode argument, otherwise the rest of the code won't wait. And make VERY sure that the form will be closed by your code!

Posting Permissions

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