Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 20th, 2006, 08:51 AM
Authorized User
 
Join Date: Feb 2005
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default Opening another instance of Access with VBA

Hello. I was asked a question that I have never thought of before.

How can an Access application open another one? Please do not misunderstand me here. I am not talking about using the OpenDatabase method. It would be more like closing the current Access app and open another one.

My customer has two separate databases and they are 'kind of' related. So from one form there would be a command button launching the other one.

From the other one I could verify if the app is open then close it.

Would it be similar to opening Word or Excel with a specified document?

Set objAcc = CreateObject("Access.Something")
With objAcc
    .Application.Visible = True
End With

or with

Shell "Access.exe", vbHide

I am not sure where to start with this. Any idea anyone please?

Daniel


Daniel
__________________
Daniel
 
Old November 20th, 2006, 12:52 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I was messing around with this and really couldn't find a solution. What I would suggest is creating a new front end that uses data from both databases, or alternatively, opening a connection to the other database into yours, using forms imported from the other database. Opening and closing instances can be confusing for the user.



mmcdonal
 
Old November 20th, 2006, 03:50 PM
Authorized User
 
Join Date: Nov 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here is some code that we use to open a form in another database that is called from a click event from a form in our database.

Private Sub Btn_MOTBL_RemailReq_Click()
'This allows the Survey Program to Open a Form in the Project Database
'to deal with Remail Requests

'(Dim) Declare variables; dq, strExe, strMdb and strRun as String Data Type
Dim dq As String
Dim strExe As String
Dim strMdb As String
Dim strRun As String

dq = """"

'Define var strExe; Miscosoft Access
strExe = "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"

'Define var strMdb; Project database location
strMdb = "\\groups\data\CTRHS\CHCR\Programming\Data\SurveyI nterface\EFLRemailRequests.mdb"

'Define the entire statement to implement at Shell
strRun = dq & strExe & dq & " " & dq & strMdb & dq

'Run the code to open the project database
Shell (strRun)

End Sub




 
Old November 20th, 2006, 09:34 PM
Authorized User
 
Join Date: Feb 2005
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you mmcdonal and specially John Parker. I tried your sample and it works after as I intended after I added a return value and an extra parameter for the Shell command.

This is what I used:

Private Sub cmdNorthwind_Click()
 Dim dq As String
 Dim strExe As String
 Dim strMdb As String
 Dim strRun As String
 Dim dblReturnVal As Double

 dq = """"

 'Define var strExe; Miscosoft Access
 strExe = "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"

 'Define var strMdb; Project database location
 strMdb = "D:\Daniel\Databases\Northwind.mdb"

 'Define the entire statement to implement at Shell
 strRun = dq & strExe & dq & " " & dq & strMdb & dq

 'Run the code to open the project database
 dblReturnVal = Shell(strRun, vbMaximizedFocus)

End Sub

Now, I guess this is harder, can I close the previous Access application window. This would be wonderful (not to mention an innovation some of us could use)!

Thanks again John.


Daniel
 
Old November 20th, 2006, 09:54 PM
Authorized User
 
Join Date: Nov 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Daniel,

We don't have anything hightech to close, we just select the "x" at the top right.

Glad I could help but this is code that someone else wrote and we have been using for some time. Just thought it would be applicable in your situation. I'm just learning Access vba myself. I have lots to learn!


Cheers!!

John

 
Old November 20th, 2006, 11:22 PM
Authorized User
 
Join Date: Feb 2005
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well guys I am glad to let you know that I found the perfect answer to my question. From an open Access database I click on a button and it opens Northwind and closes the calling database noooo worries!

Here is the code I use. You will see there is no magic here.

Public Sub AccessExample()
    Dim objAccess As Access.Application

    'Check that this is the path on YOUR PC for
    'Northwind.mdb, which ships with Access.
    Const conPATH = "D:\Daniel\Databases\Northwind.mdb"

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

    'Open the sample Northwind database that ships with
    'Access and display the application window.
    objAccess.OpenCurrentDatabase conPATH
    objAccess.Visible = True

    'Open the Employees form.
    objAccess.DoCmd.OpenForm "Employees"

    ' Maximize other Access window and quit current application.
    objAccess.DoCmd.RunCommand acCmdAppMaximize
    Application.Quit
End Sub

After I thought of it I can savely close the calling application once the other is opened.

Hope this helps anyone interested and thanks again for your help John.


Daniel





Similar Threads
Thread Thread Starter Forum Replies Last Post
Opening Switchboard with VBA rhysduk Access VBA 8 February 5th, 2014 12:31 AM
VBA to find Device Instance Number and tag sswcharlie Excel VBA 0 May 29th, 2007 04:21 PM
opening VBA project in VB6 joseph1 VB How-To 0 July 8th, 2006 08:25 AM
Send data without opening a new window instance kulrom ASP.NET 1.0 and 1.1 Basics 8 December 15th, 2005 12:01 PM
Opening Switchboard with VBA rhysduk Access VBA 0 September 9th, 2004 02:53 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.