[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

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.
0
charlie_301
Asked:
charlie_301
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.

Jim.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )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.
0
 
datAdrenalineCommented:
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)
        .CloseCurrentDatabase
        .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}
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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

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

Jim.
0
 
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, http://www.experts-exchange.com/Database/MS_Access/Q_27836764.html#a38319154 was a valid solution, and http://www.experts-exchange.com/Database/MS_Access/Q_27836764.html#a38317115 and http://www.experts-exchange.com/Database/MS_Access/Q_27836764.html#a38317304 were also valid.

And you obviously took this action to remove the automated question lock, since you immediately asked this new question:
https://secure.experts-exchange.com/Software/Server_Software/Email_Servers/Exchange/Q_28521724.html
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now