Open second Access database from within first.

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.
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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.
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
<<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.

datAdrenalineConnect With a Mentor Commented:
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}
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

charlie_301Author Commented:
thanks all for input
charlie_301Author Commented:
I've requested that this question be deleted for the following reason:

old question
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
"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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.