Open second Access database from within first.

Posted on 2012-08-21
Last Modified: 2014-09-22
We use Access 2010 to run a database containing customer orders created by our sales staff. Some of the customer information is also used in a second database for a different purpose.
I would like to provide a command button, or similar, on the customer order form which would launch the second database and open the appropriate form, pre-populated with the customer details from the first form.

I appreciate that the easiest solution would be to merge the two databases, but there are a number of reasons why they are best kept separate.

I've managed the first step.  A command button runs an append query which imports the relevant information to the second database.  Is it possible or advisable to extend the VB code behind the command button to launch the second application and open the form?

Any advice would be much appreciated.
Question by:charlie_301
    LVL 56

    Assisted Solution

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<Is it possible or advisable to extend the VB code behind the command button to launch the second application and open the form?>>

     As long as you realize that the current database will close; you can't have two databases open at the same time within the UI.

     You would  be better off to pull that form into the current DB rather then switching.

    LVL 84

    Accepted Solution

    To expand on what Jim wrote:

    You can pull the form into the "Orders" database, and link the tables from the "Customer" database in the Orders db. This will maintain the data in the Customer database, but also give you the ability to use the data in that table from within your Orders db.
    LVL 11

    Assisted Solution

    Well ... you can open several db's in the UI, you just can't access the second db's UI objects {barring the use of References to another database in your VBA project}.  In other words, you can have linked tables that point to 100 different database files if you want, but your Access UI instance can only view visualization objects (Forms, Reports) from the database file opened (as in File | Open) by that instance.

    With that said, you can launch another instance of the Access UI and open the database file that contains the form you want to manipulate, then subsequently manipulate that form fairly easily.

    Sub foobar()
        With New Access.Application
            '.UserControl = True  'Uncomment this line if you want the Access Instance to remain open after the code runs
            .Visible = True
            .OpenCurrentDatabase "C:\somePath\someFile.mdb"
            .DoCmd.OpenForm "someForm", , , , acFormAdd
            With .Forms("someForm")
                'Populate the controls with data from the first Access UI instance
                .Controls("someControl") = Me.someControl
                'Etc ...
                'Save the record
                If .Dirty Then .Dirty = False
            End With
            'Close the form
            .DoCmd.Close acForm, "someForm", acSaveNo
            'Quit the instance (if that is what you want to do)
            .DoCmd.Quit acQuitSaveNone
        End With
    End Sub

    Open in new window

    However, if you just want to append data to a table in a remote database, you can do that by executing a SQL statement ...

    Sub Foobar2()
        Dim strSQL As String
        strSQL = "INSERT INTO [C:\somePath\someFile.mdb].[someTable] (textField1, textField2, etc...)" & _
                 " VALUES ('" & Me.someTextField1 & "','" & Me.someTextField2 & "')"
        CurrentDb.Execute strSQL, dbFailOnError
    End Sub

    Open in new window

    {disclaimer: code is of the 'AIR CODE' variety, and while it should work -- aside from the substitions of names where appropriate --- its primary purpose is to show a technique on how to accomplish a task}

    Author Comment

    thanks all for input

    Author Comment

    I've requested that this question be deleted for the following reason:

    old question
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    "old question"

    Do you mean it's no longer a question for you and you've moved on?   was anything here helpful?

    I ask only as a way of pointing out that you will only get the full benefit from EE if you,  just as much as the experts, work at a question.

    Everyone gets busy or sometimes a problem just goes away, but you asked a question and got three responses.   Coming back a month later and asking for a delete with "old question", doesn't help anyone.

    LVL 84
    I'm objecting to this, since you were presented several methods to do what you were asked. In specific, was a valid solution, and and were also valid.

    And you obviously took this action to remove the automated question lock, since you immediately asked this new question:

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now