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

David G. Smith
David G. Smith used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial