We help IT Professionals succeed at work.

What method is best to open another database from an Access switchboard?

I have an Access database (BE / FE) that I have utilized switchboards.  I want to open another database in a new window.  I presume I can either use the "Run Macro" or
Run Code" command.  I am most comfortable creating a macro and calling it from the switchboard.

Can someone help me with the method to create a macro that opens a separate database in a new window?
Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
You can use the RunCode command to a Function like this ( example)

Public Function mOpenDbInWindow

Dim obj As Application
Set obj = CreateObject("Access.Application")
obj.Visible = True
obj.OpenCurrentDatabase ("FullPathAndDbNameHere")
obj.UserControl = True
Set obj = Nothing


'  other code

Set obj = Nothing  '

End  Function

mx
Or simplified version:

Public Function OpenDb()

Dim strPath As String

strPath = "C:\YourPath\Yourfile.accdb"

Application.FollowHyperlink strPath, , True

End Function

Then in Macro, using RunCode, enter the Function name OpenDb().

Hope this helps,
Daniel
Well the Query contains a selection from a Table or more Tables.

So the Delete statement will be almost the same, but instead of SELECT command you need to use the DELETE command.
Basicaly creating a DELETE query.

See below thread:
http://www.fmsinc.com/microsoftaccess/query/snytax/delete-query.html#DELETE%20Query%20SQL%20Syntax

Hope this helps,
Daniel
Please ignore the last post, it was meant for different thread.
For some reason ended up in this thread (?)

If possible delete.

Author

Commented:
Ok, I tried to complete the solution submitted by Daniel "danishani" but i get an "Action Failed "Error Number 2950".

I am working in an Access 2007 database and trying to open a .mdb file.  Is this a problem?

My code is below.
Public Function OpenDb()

Dim strPath As String

strPath = "\\brc-cor-fp01\mas90\1 Quality\Training\Training Records.mdb"

Application.FollowHyperlink strPath, , True

End Function

Open in new window

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"I am working in an Access 2007 "
Is the db you are trying to open in a Trusted Location ?

mx
I have tested it, and it worked for me.

Make sure you have compiled the Function OpenDb() and saved it in a seperate Module in your database.

Hope this helps,
Daniel
Another thing, make sure you have enabled Macro's, and run in Trusted Location.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
In fact see this link on that exact error:

http://support.microsoft.com/kb/931407

mx

Author

Commented:
Yes:
\\brc-cor-fp01\mas90\

is trusted  (with subfolders)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well, error 2950 is a relatively new error number (A2007) and specifically deals with Trusted Locations.  Did you look at the KB above ?

mx

Author

Commented:
Yes I looked at the KB.  I have ensured that both locations of each database is in a trusted location.  Both databases have Macros enabled.  Still does not work for some reason.

The host DB is on a terminal server (Remote Desktop) and is calling to open a database elsewhere on the server.  I read where this may be more difficult.  Could this be the issue?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"Could this be the issue?"
My guess: Yes ... if you already have TL's set up.

I don't know the solution ...

mx

Author

Commented:
Update:
The code works like a charm when ran from Visual Basic.  But it gives me the error when run from the switchboard.  

I decided to make a blank form and add a button that runs a macro (runcode).  No luck!  But this time it says the database can't find the function.  Then it gives me the error.
Make sure the Module is saved with a different name then the Function.
Did you saved it in a seperate Module?


Author

Commented:
Daniel, that was it!!!!  Thank you so much.  It works.  Above you mentioned to make sure it was saved in a separate module - I did that but I gave it the same name.  Now that I named the Module different from the Function, it works perfectly.  
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
danishani:
Good one - hyperlink !

mx