Open Another Database in a New Window

[redcali55]

Hello,
I'm trying to create a form that works as a Switchboard to open a number of databases from one place. I want users to be able to click a button and open an existing database in a new window. I tried the following code and the database flashes open and then disappears (fyi - I'm using Access 2007). Any ideas?

Private Sub Command0_Click()
 
    Dim appAccess As Access.Application
    Dim strDB As String
 
    strDB = Me.PMTCTPath 'references text box with file path on form
 
     Set appAccess = _
        CreateObject("Access.Application")
    ' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strDB
 
End Sub

[ADezii]

1. The problem is that appAccess is declared as a Procedural Level Variable, and is destroyed immediately when the Procedure ends. Change the Declaration to either a Private Declaration in a Form Module. Declare strDB for other reasons, in the same manner, as in:
   

   Private appAccess As Access.Application
   Private strDB As String

2. Or as a Public Declaration in a Standard Code Module:
   

   Public appAccess As Access.Application
   Public strDB As String

3. Make the Instance of appAccess Visible, as in Code Line #5:
   

   strDB = Me.PMTCTPath    'references text box with file path on form
    
   Set appAccess = CreateObject("Access.Application")
    
   appAccess.Visible = True
    
   'Open database in Microsoft Access window.
   appAccess.OpenCurrentDatabase strDB
    
   'Open a specific Form to View
   appAccess.DoCmd.OpenForm "<some old Form>"

4. Any questions, feel free to ask.

[NeoPa]

...
4. Any questions, feel free to ask.

I'll nick in there and take you up on that ;)

Q1. If the application variable goes out of scope, will the application necessarily be closed?

Q2. If the application is made visible first, will that allow the process to continue indefinitely (until closed by the operator)?

[ADezii]

I'll nick in there and take you up on that ;)

Q1. If the application variable goes out of scope, will the application necessarily be closed?

Q2. If the application is made visible first, will that allow the process to continue indefinitely (until closed by the operator)?

Q1. If the application variable goes out of scope, will the application necessarily be closed?

It does not appear so, but you will not be able to reference the Properties or Methods of the appAccess Objects, as well as to react to any of its Events. You will not be able to Close the DB programmatically.The following will generate an Object Required Error in this current context:

MsgBox appAccess.CurrentObjectName

Q2. If the application is made visible first, will that allow the process to continue indefinitely (until closed by the operator)?

This would also appear to be True.

[Stewart Ross]

Hi. Although the OP is posting about opening Access from Access and my experience is in opening Excel from Access I can confirm ADezii's answer to NeoPa's questions. There are some ramifications of all this which apply to this thread.

In the case of Q1, the non-visible automation instance will remain open indefinitely but not be accessible via code or the user. The running application is listed by Windows Task Manager and can be shut down from there, but otherwise it remains dormant but running until Windows is shut down.

In the case of Q2, the visible instance of the automation server responds to user commands just as if it had been started normally, so yes it can be shut down by the user, but if the link between the automation server and the instance of Access that called it is broken - through error, or a variable going out of scope before shutting the automation instance down, for example - then there is no further process interaction between the visible Access (or Excel in my case) instance and the process that was running in the original Access database.

Returning to the theme of this thread for a moment, the fact that the other databases remain independently operating is a potential disadvantage which may need to be borne in mind - the user can indeed close the application, and if that automation server instance is then referred to once again from the calling instance an error will arise as the application object will no longer be valid.

When the automation server is visible but running under control of automation code from elsewhere it is very easy (far too easy) to forget that that the running instance is operating under 'remote control' as it were. I have on many occasions caused the automation code to crash by accidentally interrupting an operation, forgetting that (in my case) the Excel worksheet in front of me is not available to do other things. That is the main advantage of running in 'invisible' mode - there is no temptation to interfere, and as long as the code has no errors the process will definitely run to completion.

Having the application visible introduces uncertainty into whether or not the automation code will run to completion, by the nature of the asynchronous and unpredictable actions the user may take. It's fascinating to watch automation code at work, however; in my case worksheets fill up as if by magic. I set all of mine to invisible now, however, not just to avoid my own interference - it also avoids the window focus shifting from the current application to the automation server every time a new instance is opened (a real nuisance if you are trying to work concurrently on other things).

-Stewart

[FishVal]

Hello gentlemen.

Excel and Access expose Application.UserControl property which determines whether an application instance would be terminated when object variable referring to has been set to nothing.

At the same time for Word you should use Document.UserControl property - Word.Application.UserControl is read only.

Regards,
Fish

[NeoPa]

Thanks to all of you for your clarifications :)

[ADezii]

Hi. Although the OP is posting about opening Access from Access and my experience is in opening Excel from Access I can confirm ADezii's answer to NeoPa's questions. There are some ramifications of all this which apply to this thread.

In the case of Q1, the non-visible automation instance will remain open indefinitely but not be accessible via code or the user. The running application is listed by Windows Task Manager and can be shut down from there, but otherwise it remains dormant but running until Windows is shut down.

In the case of Q2, the visible instance of the automation server responds to user commands just as if it had been started normally, so yes it can be shut down by the user, but if the link between the automation server and the instance of Access that called it is broken - through error, or a variable going out of scope before shutting the automation instance down, for example - then there is no further process interaction between the visible Access (or Excel in my case) instance and the process that was running in the original Access database.

Returning to the theme of this thread for a moment, the fact that the other databases remain independently operating is a potential disadvantage which may need to be borne in mind - the user can indeed close the application, and if that automation server instance is then referred to once again from the calling instance an error will arise as the application object will no longer be valid.

When the automation server is visible but running under control of automation code from elsewhere it is very easy (far too easy) to forget that that the running instance is operating under 'remote control' as it were. I have on many occasions caused the automation code to crash by accidentally interrupting an operation, forgetting that (in my case) the Excel worksheet in front of me is not available to do other things. That is the main advantage of running in 'invisible' mode - there is no temptation to interfere, and as long as the code has no errors the process will definitely run to completion.

Having the application visible introduces uncertainty into whether or not the automation code will run to completion, by the nature of the asynchronous and unpredictable actions the user may take. It's fascinating to watch automation code at work, however; in my case worksheets fill up as if by magic. I set all of mine to invisible now, however, not just to avoid my own interference - it also avoids the window focus shifting from the current application to the automation server every time a new instance is opened (a real nuisance if you are trying to work concurrently on other things).

-Stewart

Excellent points, Stewart. I think another very important aspect that could potentially come into play here, is the OPs Switchboard-like design in which a Switchboard could open multiple, Independent Instances, of several Databases. I would imagine that it would be a rather complex operation to maintain programmatic control of the individual Instances, let alone the confusion involved in such an operation. The Object Variable appAccess, if maintained at the Form Level, would now be meaningless since it would only point to the last Opened Instance. I imagine that you could assign multiple Object Variables to all the Instances, but again the problem of manageability arises. The best approach would probably be to manage them via a Collection with each Instance uniquely identified by its hWnd Property, but it would still be a mess.

Personally, instead of the Switchboard approach, I would have a single Command Button opening a File Dialog Box, filtered for *.mdbs, with Multiselect set to False. Once a File has been selected, the Database would be opened via Automation as indicated by the previous code. It would not be that simple to inadvertently open multiple Databases, and the code base would be substantially reduced. Any thoughts?

[NeoPa]

I have a thought ADezii :-
Only you would even GO there! :D

[redcali55]

For anyone else who came across this looking for a quick answer...I just set it up as a hyperlink using VBA:

Private Sub Command25_Click()
 
    Dim strInput As String
 
    strInput = FilePathCommonOth1 'This references a text box on my form that has a file path
 
   Application.FollowHyperlink strInput, , True 'Setting this to True opens the database in another window
 
End Sub

[ADezii]

For anyone else who came across this looking for a quick answer...I just set it up as a hyperlink using VBA:

Private Sub Command25_Click()

Dim strInput As String

strInput = FilePathCommonOth1 'This references a text box on my form that has a file path

Application.FollowHyperlink strInput, , True 'Setting this to True opens the database in another window

End Sub

Glad you figured it out for yourself. Personally, I never considered this option, since with your initial approach, I was under the impression that you wanted to use Automation to Open the DBs.

[NeoPa]

For anyone else who came across this looking for a quick answer...I just set it up as a hyperlink using VBA:
...

Thanks for posting your resolution. It's always interesting to see different approaches to the same problem.

Please try to remember the [ CODE ] tags in future though.