Using a macro to open another database.

[Peter Jason]

Access10 Win7 SP1

I have 3 databases each one for a specific
purpose.

I want to put a button on a form to open another
database via a macro.

The macros have a "close database" function but no
one to open one.

How can this be done? Can I have 3 databases on
the screen at once?

Peter

[Phil]

Try a control button "OpenDb"

The method of giving the path is only an example, it should not be "hard
wired in". The other database paths should really come from a table

Private Sub OpenDb_Click()

Dim objAcc As Object
Dim StrPath As String

StrPath = "E:\Phil Data\Access\Mdb 2010\Database3.accdb"

Set objAcc = CreateObject("Access.Application")

objAcc.OpenCurrentDatabase StrPath ' Opens another database

objAcc.UserControl = True

Application.Quit Quit ' Closes this database

End Sub

Probably you can have all 3 databases open, it depends on what they are
sharing. Data should be no problem, but in the unlikely event that they are
sharing referenced databases, there will be a problem.

You must have a big screen. Why do you want 3 databases open at the same
time?

Phil

[Peter Jason]

Actually I'm thinking of just having 3 ledgers in
the one database instead. This might be simpler.

[Phil]

>>Probably you can have all 3 databases open, it depends on what they are
>>sharing. Data should be no problem, but in the unlikely event that they are
>>sharing referenced databases, there will be a problem.
>>
>>You must have a big screen. Why do you want 3 databases open at the same
>>time?
>>
>>Phil
>
> Actually I'm thinking of just having 3 ledgers in
> the one database instead. This might be simpler.
>

Is this a relational database? Are the "Ledgers" (do you mean tables?)
related?

All sounds very odd. What are you trying to achieve? Have you been using
Lotus Approach before?

Phil

[David Hare-Scott]

Peter Jason wrote:
> Access10 Win7 SP1
>
> I have 3 databases each one for a specific
> purpose.
>
> I want to put a button on a form to open another
> database via a macro.
>

Use code instead.

> The macros have a "close database" function but no
> one to open one.
>
> How can this be done? Can I have 3 databases on
> the screen at once?
>
> Peter

I am assuming here that you really do mean to open three Access databases
not three tables or three something else.

You can have only one database open in each instance of Access. You can run
three instances of Access given sufficient RAM, each with a different
database open. You can link three backend databases to the same front end.
If you are not familiar with the concept of frontend and backend then you
should be.

But before going any further please explain what you are trying to do and
why you need three databases open at once. I have never seen the need nor
do I know of any case where it was required. I begin to suspect that you do
not have a normalised table design.

David

[Peter Jason]

Thanks, but I'm tending to having just the one
ledger. Mine is composed of 3 tables
"LedgerTxns", "LedgerAccnts" and "LedgerAccntType"
(this last being BalanceSheet & ProfitLoss).

If I simply add "Personal" and "Investments" to
the tbl"LedgerAccntType" and then add to
tbl"LedgerAccnts" add the special accounts as
required and then tick the necessary option boxes,
this should work.

Then I should access the desired ledger by
selecting its name in the "LedgerAccntType" combo
box. Just one database would be simpler.

Peter

[Peter Jason]

I made my own business relational database way
back in 1999 using Access97 and then upgrading
this all the way to Access10.
My affairs are more complicated now and I need a
way to separate business/personal/investment
transactions for analysis and for the accountants.

[Phil]

Ah! That makes more sense now.
I use something similar where I keep membership details of various clubs
-Yacht Club, Bridge Club, Rotary Club etc. I use the same front end database
with the necessary forms & reports & code and a corresponding number of back
end databases to hold the records. (3 in the case of the Clubs previously
mentioned) Using a combo box, I select the appropraite back end Club (I have
a table that holds the path of the back end databases) and link the
appropriate back end tables. On changing to another Club, I remove the link
to the tables and attach the new club's tables. Back to where we started!!

Phil