VBA to switch from one access database to another

[DRoss902]

I have spent days combing forums like this one and anything else I could find, for comprehensible code to move between two separate Access databases open in separate windows, when the name of the other window is known. Spent a lot of time staring crossed eyed at api, hwnd, and other scripting stuff. None of which I could decipher well enough to get it to work.
In the end, I seem to have found a simple, elegant solution by cobbling together bits of simple vba code. I present it here for others who may need it.

In database one:

Private Sub Button_Click()
Dim objAccess As Object


Set objAccess = GetObject("C:\DirectoryPath\Filename.accdb")
objAccess.DoCmd.OpenForm "ThisForm"
DoCmd.RunCommand acCmdAppMinimize
End Sub

In Database two, in the On Open of "ThisForm":

Private Sub Form_Open(Cancel As Integer)
Forms!ThisForm.SetFocus
Forms!ThisForm!AnyControl.SetFocus
End Sub

Of course the last bit can then be followed by any code you want, to do what needs doing in the second database. In my case the code in the first database is part of a bigger chunk which pushes data from database one into database two (via three append queries), and I then proceed to create a new record in a sub form of "ThisForm" based on those new records.
I now also have a tool I can use to allow my end users to move back and forth between these two related, but separate databases, either of which they may need to consult or update at any given moment, without having to go to the windows task bar. And also very important, I don't end up with extra instances of my files open.
Victory is mine!

[Robeen]

the code in the first database is part of a bigger chunk which pushes data from database one into database two (via three append queries)

How does the data itself go from one database to the other?
Can you give me an outline of how that works?
I've never had to do something like this - but I might NEED to one day - and then I'm sure this will come in handy!

Thanks in advance!!

[DRoss902]

How does the data itself go from one database to the other?
Can you give me an outline of how that works?
I've never had to do something like this - but I might NEED to one day - and then I'm sure this will come in handy!

Thanks in advance!!

Hi Robeen,

Data can be 'pushed' using an Append Query. Look that up and you'll find lots of howtos. One word of warning is to be sure the field names are identical in both databases.

[Robeen]

Your most recent post did two things:
1. Paraphrased what you said in your previous post [Data can be 'pushed' using an Append Query] - so no new info there.
2. Told me to 'Look that up . . .' [in other words 'find it yourself'?]


I know you don't HAVE to share what you already figured out but I'm still scratching my head about why you replied if you didn't intend to add anything new to your previous post other than telling me I could find it myself, when you originally said:

None of which I could decipher well enough to get it to work.

So I'm still a little .

Any chance you could give me a little more of what you figured out about how you move data from one database to another?
I think this would be really helpful to many [like me] who don't know how to do this.

Thanks!!!

[DRoss902]

Hello again Robeen,

I did not mean to be unhelpful. I am pretty new to programming and Access in general myself, so I didn't want to be trying to explain things I might get wrong. But since you really want more from me, below is the whole piece of code I was discussing in my first post, along with a bit more on how to create the append queries that are part of it. I make no claims that this will 100% work for you, as I basically fumble and stumble my own way to success, through a lot of googling and trial and error! It is just intended to give you the concepts I have employed, most of which I learned about through forums like this one. (A belated thank you to all those who unknowingly helped me during my many months of lurking!) It is also possible that others would do the same things in completely different ways...
In any case, I hope you will find this more helpful.

Private Sub CreateNewFile_Click()

'Check to see if there is already a corresponding record in the other database. This makes use of the relationship between the two databases, by counting the related records in a linked table in the other database

If Forms![ContactInfoForm]![CtRefs] > "0" Then
MsgBox "There is already a record for this person. Please go to Database 2 if you wish to view it.", vbOKOnly, "File Exists"
ElseIf Forms![ContactInfoForm]![CtRefs] = "0" Then

'Confirm that the person really wishes to create a record in the other database

Response = MsgBox("Do you wish to create a new record for this person?", vbYesNo + vbQuestion + vbDefaultButton2)
If Response = vbNo Then
Exit Sub
End If
If Response = vbYes Then

'Open a small form that keeps track of the ID# of the record whose data we wish to move, to ensure that relationships are set up correctly (the need for this was learned through trial and error)

DoCmd.OpenForm "CurrentRecordForm", , , , , acHidden

'Call the three append queries. An append query starts off as a normal query containing the data fields you wish to transfer. Open the query in design mode and in the design tab there is a button marked Append. This opens a wizard that allows you to choose the database to whcih you want to transfer files. You then choose the table as well, but you cannot specify the field names, which is why they need to be the same in both databases.

DoCmd.OpenQuery "AppendFirstTableQuery"
DoCmd.OpenQuery "AppendSecondTableQuery"
DoCmd.OpenQuery "AppendThirdTableQuery"
DoCmd.Close acForm, "CurrentRecordForm"

'This next bit is the part I had a lot of trouble with - getting over to the other database to see the new record.

Dim objAccess As Object
Set objAccess = GetObject("C:\DirectoryPath\Filename.accdb")
objAccess.DoCmd.OpenForm "Thisform"
DoCmd.RunCommand acCmdAppMinimize
End If
End If
End Sub

'As noted in my first post, using a Setfocus in the On Open of Thisform, then brings the other database window to the front.

[Robeen]

Thanks!!
This is very helpful.

I hope to try it one of these days so that I have a working copy of it should I ever need it!

Much appreciated.