Opening a new DB with VBA

Question

I am trying to open another db using VBA from my current db then close the one I am in. When I use the code listed here it opens Access but closes it immediately. I am sure it is simply something I am overlooking but the past hour I have wracked my brain. Any help would be greatly appreciated.

Private Sub Command115_Click()
Dim objAccess As Access.Application

Const conPATH = "C:\Users\user\Desktop\Database1.accdb"

'Create an instance of the Access application object.
Set objAccess = CreateObject("Access.Application")

'Open the database
objAccess.Visible = True
objAccess.OpenCurrentDatabase conPATH

'Open the form.
objAccess.DoCmd.OpenForm "Main-Form"

' Maximize other Access window
objAccess.DoCmd.RunCommand acCmdAppMaximize
End Sub

Thanks in advance for any help in this matter

Try adding your Access object to a public collection in a module? — dennythecoder
– dennythecoder, Commented Jun 27, 2013 at 12:10
I placed the code in a module as a Public Function then called it with the same result. Is that what you meant for me to do or did I misunderstand? — Chuck
– Chuck, Commented Jun 27, 2013 at 12:21

ashareef · Accepted Answer · 2013-06-27 12:43:34Z

3

If you use the shell then when you close your first database, the second will remain open.

Sub test()
    Dim sh As Variant
    sh = Shell("""C:\...\MSACCESS.EXE"" ""C:\...\FileName.accdb""")
End Sub

My guess the reason your code didn't work is because, the second access you start is an object that exists within the first one. The moment the first one closes and cleanup starts for its objects/variables. It closes the second one.

answered Jun 27, 2013 at 12:43

ashareef

1,84613 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ashareef Over a year ago

This is a link for command line switches for access. You may find them useful when you open accdb/mdbs. office.microsoft.com/en-ca/access-help/…

Chuck Over a year ago

Perfect! Just added vbMaximized focus and a close for this one. Thanks so much!

user2638336 · Accepted Answer · 2014-05-06 17:20:21Z

0

Say your first app is access1 and it is trying to open access2 and then close access1 app and makes access2 as active app. One thing you could do is, Once your access1 app loads try to open access2 app programatically and make it visible and then use Application.closeCurrentdatabase to close access1 database and then in the unload event of access1 apps form (if there is any form in access1 app) call Application.Quit.

This works.

answered May 6, 2014 at 17:20

user2638336

254 bronze badges

Collectives™ on Stack Overflow

Opening a new DB with VBA

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related