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

Topic

New Reply

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

Reply | Quote

Replies

Edited later to correct error in code.

You can use Automation to run another instance of Access.

Dim objAccess As Access.Application

On Error GoTo ErrHandler

Set objAccess = CreateObject(“Access.Application”)
objAccess.OpenCurrentDatabase(“serversharefolderdb3.mdb”)
…

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

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Kevin,

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

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

Kevin,

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Edited later to correct error in code.

You can use Automation to run another instance of Access.

Dim objAccess As Access.Application

On Error GoTo ErrHandler

Set objAccess = CreateObject(“Access.Application”)
objAccess.OpenCurrentDatabase(“serversharefolderdb3.mdb”)
…

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

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler

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

Reply | Quote