By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Open Another Database in a New Window

P: 7
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?
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3.     Dim appAccess As Access.Application
  4.     Dim strDB As String
  5.  
  6.     strDB = Me.PMTCTPath 'references text box with file path on form
  7.  
  8.      Set appAccess = _
  9.         CreateObject("Access.Application")
  10.     ' Open database in Microsoft Access window.
  11.     appAccess.OpenCurrentDatabase strDB
  12.  
  13. End Sub
Sep 6 '08 #1
Share this Question
Share on Google+
11 Replies


ADezii
Expert 5K+
P: 8,619
  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:
    Expand|Select|Wrap|Line Numbers
    1. Private appAccess As Access.Application
    2. Private strDB As String
  2. Or as a Public Declaration in a Standard Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Public appAccess As Access.Application
    2. Public strDB As String
  3. Make the Instance of appAccess Visible, as in Code Line #5:
    Expand|Select|Wrap|Line Numbers
    1. strDB = Me.PMTCTPath    'references text box with file path on form
    2.  
    3. Set appAccess = CreateObject("Access.Application")
    4.  
    5. appAccess.Visible = True
    6.  
    7. 'Open database in Microsoft Access window.
    8. appAccess.OpenCurrentDatabase strDB
    9.  
    10. 'Open a specific Form to View
    11. appAccess.DoCmd.OpenForm "<some old Form>"
  4. Any questions, feel free to ask.
Sep 6 '08 #2

NeoPa
Expert Mod 15k+
P: 31,344
...
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)?
Sep 6 '08 #3

ADezii
Expert 5K+
P: 8,619
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:
Expand|Select|Wrap|Line Numbers
  1. MsgBox appAccess.CurrentObjectName
Q2. If the application is made visible first, will that allow the process to continue indefinitely (until closed by the operator)?
Expand|Select|Wrap|Line Numbers
  1. This would also appear to be True.
Sep 6 '08 #4

Expert Mod 2.5K+
P: 2,545
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
Sep 6 '08 #5

FishVal
Expert 2.5K+
P: 2,653
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
Sep 6 '08 #6

NeoPa
Expert Mod 15k+
P: 31,344
Thanks to all of you for your clarifications :)
Sep 6 '08 #7

ADezii
Expert 5K+
P: 8,619
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?
Sep 6 '08 #8

NeoPa
Expert Mod 15k+
P: 31,344
I have a thought ADezii :-
Only you would even GO there! :D
Sep 7 '08 #9

P: 7
For anyone else who came across this looking for a quick answer...I just set it up as a hyperlink using VBA:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command25_Click()
  2.  
  3.     Dim strInput As String
  4.  
  5.     strInput = FilePathCommonOth1 'This references a text box on my form that has a file path
  6.  
  7.    Application.FollowHyperlink strInput, , True 'Setting this to True opens the database in another window
  8.  
  9. End Sub
Sep 8 '08 #10

ADezii
Expert 5K+
P: 8,619
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.
Sep 8 '08 #11

NeoPa
Expert Mod 15k+
P: 31,344
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.
Sep 8 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.