I was wondering if it is possible to create a master switchboard and have buttons that will open other databases on the server?

At this company there are many databases created for single purposes.
Some have just a few objects to do one task, nothing more.
I wanted to combine them into larger databases and get rid of having so many that do a single function.
But a problem I have run into is that many of these single function databases use repetitive object naming.
Most have a Query1, Query2, Form1, Form2, Macro1, Macro2, etc.
Importing them into a single database would be a real headache.

I would like to have one switchboard that would have buttons to open different databases on the server as chosen by the user.

Any help is appreciated.
I have been out of Access programing for a LONG time.

Hi,

Yes, that is very possible but we'll need to understand the effect you're trying to create. If you just want a switchboard to "launch" the other databases, that would be easy. However, if you want a switchboard to "pretend" that the user is not really using a different database, that would take a lot more work.

Just my 2 cents...

I want to make it easier for them to open whatever instance they want to, without having to go to a certain folder and choose the DB they want.

I would like to give them a single switchboard they can use on their own machines and have it open whatever database they want.

It does not have to be fancy schmancy, nothing to hide.

Okay. So, create your switchboard and for each button to open the specific database, you can use any of the functions mentioned in this Wiki article: Opening Files From Access.

Just my 2 cents...

Sorry, I am not trying to be dense........

I create the switchboard, and then what do I do with the function?

I really dont know what to do with it.
I looked at the linked page, and assume you meant to use Shell or Shell Executable API.

I have never used either of them before.

Yes, so basically, let's say you added a button on your "switchboard" to open DatabaseA from "c:\myfolder\databasea.accdb"

In the Click event of that button, you would use something like:

ShellExecute "c:\myfolder\databasea.accdb"

Let me know if you need step-by-step instructions.

Just my 2 cents...

Here is what I did:

I created a switchboard
I put a button on the switchboard.
It called it Button7.
I went to the Event tab.
Chose [Event Procedure]
Ht the ... button and put this in there:


Private Sub Command7_Click()

ShellExecute "H:\AccessProgs2003\allrock2000.mdb"

End Sub



When i click the button, Access does not like ShellExecute.


What have I done wrong now?
It appears to be very simple and I cant figure it out.

Hi,


I recommend that you get into the habit of using more meaningful names than the defaults Access assigns to each control. For example, you could use "cmdOpenDatabaseA."



Did you copy and paste the ShellExecute API from the website into a Standard Module in your database? Also, make sure you don't name the module "ShellExecute;" try modShellExecute.

Just my 2 cents...

I cant go back to the unemployment line.
My family needs me and I am failing.

I have no idea how to do this.
I am confused beyond belief.


Here is what I currently have in the database and it will not work.
Click to view attachment


I put the code, verbatim, in the click event, it didn't work.


I put the code in a module, I have no idea what to do with it after that.
I cant add it to the form.
It isnt an option on the dropdown on the event tab.


Why is this so hard, when it shouldn't be?

Hi,

But you didn't show me what's inside "modShellExecute." Did you "copy and paste" the code from the website inside modShellExecute? If not, that's what you need to do. The previous steps you did were all fine. You just need to make sure Access can find the function ShellExecute, which you can copy from the website linked in that Wiki article.

Just my 2 cents...

i told it to give me a new module from the VB toolbar.

Inside, I put this:

Click to view attachment


Once again, I have no idea what is wrong or where to go.

On the wiki page, how do I use:

Shell """C:\....\MSACCESS.exe C:\Yourdb.mdb /Excl /x mcrStartup"""

or

Dim x
x = Shell("""C:\....\MSACCESS.exe C:\Yourdb.mdb /Excl /x mcrStartup""")



I have no clue what that means.

What is Excl?
Why would I want to open Excel?

What is x mcrStartup?

What do they do?

Hi,


No, inside the module, you were supposed to copy and paste the code from this website.

After that, all the code you created earlier in your form should work because Access can now find the function you were calling.

Just my 2 cents...

Hi,


I would try using the ShellExecute first instead of using this method.

Just my 2 cents...

I copied and pasted the whole thing into the module.

The button does nothing now.
No error.
No response.
No nothing.



What have I done wrong now?????????????????/

Hi,


You did leave all the code you had before in the form and the button still has the [Event Procedure] in it, correct?

Here is what I put into the module:

Option Compare Database - I DIDNT PUT THIS IN, ACCESS DID.

'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" _
(ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long

'***App Window Constants***
Public Const WIN_NORMAL = 1 'Open Normal
Public Const WIN_MAX = 3 'Open Maximized
Public Const WIN_MIN = 2 'Open Minimized

'***Error Codes***
Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

'***************Usage Examples***********************
'Open a folder: ?fHandleFile("C:\TEMP\",WIN_NORMAL)
'Call Email app: ?fHandleFile("mailto:dash10@hotmail.com",WIN_NORMAL)
'Open URL: ?fHandleFile("http://home.att.net/~dashish", WIN_NORMAL)
'Handle Unknown extensions (call Open With Dialog):
' ?fHandleFile("C:\TEMP\TestThis",Win_Normal)
'Start Access instance:
' ?fHandleFile("I:\mdbs\CodeNStuff.mdb", Win_NORMAL)
'****************************************************

Function fHandleFile(stFile As String, lShowHow As Long)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
'First try ShellExecute
lRet = apiShellExecute(hWndAccessApp, vbNullString, _
stFile, vbNullString, vbNullString, lShowHow)

If lRet > ERROR_SUCCESS Then
stRet = vbNullString
lRet = -1
Else
Select Case lRet
Case ERROR_NO_ASSOC:
'Try the OpenWith dialog
varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
& stFile, WIN_NORMAL)
lRet = (varTaskID <> 0)
Case ERROR_OUT_OF_MEM:
stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
Case ERROR_FILE_NOT_FOUND:
stRet = "Error: File not found. Couldn't Execute!"
Case ERROR_PATH_NOT_FOUND:
stRet = "Error: Path not found. Couldn't Execute!"
Case ERROR_BAD_FORMAT:
stRet = "Error: Bad File Format. Couldn't Execute!"
Case Else:
End Select
End If
fHandleFile = lRet & _
IIf(stRet = "", vbNullString, ", " & stRet)
End Function
'************ Code End **********











If I take this out and put the other code back in, the button has a response.


What am I not getting here?
We have spent over 15 posts and I still dont have this SIMPLE thing working.

At this point, I dont know what I have done.................

When I click on the button in design mode, it has event procedure and the following when I click the ...button



Private Sub Command7_Click()

ShellExecute "H:\AccessProgs2003\allrock2000.mdb"

End Sub

Private Sub cmdOpenDatabaseA_Click()

End Sub



I then have the module with all of this code:


Option Compare Database

'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" _
(ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long

'***App Window Constants***
Public Const WIN_NORMAL = 1 'Open Normal
Public Const WIN_MAX = 3 'Open Maximized
Public Const WIN_MIN = 2 'Open Minimized

'***Error Codes***
Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

'***************Usage Examples***********************
'Open a folder: ?fHandleFile("C:\TEMP\",WIN_NORMAL)
'Call Email app: ?fHandleFile("mailto:dash10@hotmail.com",WIN_NORMAL)
'Open URL: ?fHandleFile("http://home.att.net/~dashish", WIN_NORMAL)
'Handle Unknown extensions (call Open With Dialog):
' ?fHandleFile("C:\TEMP\TestThis",Win_Normal)
'Start Access instance:
' ?fHandleFile("I:\mdbs\CodeNStuff.mdb", Win_NORMAL)
'****************************************************

Function fHandleFile(stFile As String, lShowHow As Long)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
'First try ShellExecute
lRet = apiShellExecute(hWndAccessApp, vbNullString, _
stFile, vbNullString, vbNullString, lShowHow)

If lRet > ERROR_SUCCESS Then
stRet = vbNullString
lRet = -1
Else
Select Case lRet
Case ERROR_NO_ASSOC:
'Try the OpenWith dialog
varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
& stFile, WIN_NORMAL)
lRet = (varTaskID <> 0)
Case ERROR_OUT_OF_MEM:
stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
Case ERROR_FILE_NOT_FOUND:
stRet = "Error: File not found. Couldn't Execute!"
Case ERROR_PATH_NOT_FOUND:
stRet = "Error: Path not found. Couldn't Execute!"
Case ERROR_BAD_FORMAT:
stRet = "Error: Bad File Format. Couldn't Execute!"
Case Else:
End Select
End If
fHandleFile = lRet & _
IIf(stRet = "", vbNullString, ", " & stRet)
End Function
'************ Code End **********





Where is the disconnect?
Where have I screwed it up?

gloworm,

I'm going to throw a wrinkle in here. From the screenshot you attached it looks like you are trying to use the built in Switchboard Manager that access provides. Either you should NOT use that or you should use the Switchboard Manager properly.

If you are going to use the Swichboard Manager, then what you want to do is use the wizard to create the buttons. You want to enter a caption for the button, then choose to RunCode and then you want to enter the ShellExecute code in the bottom box of the form.

If you aren't going to use Switchboard Manager then just use a Blank form and add buttons to them.

To expand on what DBGuy is telling you. The Wiki article that DBGuy linked to includes code that makes it easier to open files. To use the code, you have to go to the Web site linked and copy all the code into the module you created. The error you are getting is telling you that the function ShellExecute doesn't exist. Which it does because you didn't create it.

Hi,


Since I don't know what you named your button, try adding this in there:

Private Sub Command7_Click()

ShellExecute "H:\AccessProgs2003\allrock2000.mdb"

End Sub

Private Sub cmdOpenDatabaseA_Click()

ShellExecute "H:\AccessProgs2003\allrock2000.mdb"

End Sub

Just my 2 cents...

I tried to use the wizard, but there wasnt an option to open another database in the options I found.

I did copy all of the code and pasted it.

What more should i have done to it?

I see ShellExecute in the code?

How is it not created?

I copied and pasted this into the button and I still get the same exact error.

What can I be missing?
You and the other guy seem to think it is very easy.
To me, obviously it isnt.


Can you tell me from step one, how I am supposed to build this switchboard?

I want to figure it out on my own, but that does not appear to be happening.

Okay, here are the basic steps:

1. Copy and paste the entire code from the website into a Standard Module called "modShellExecute"
2. Create a new form and add a button to it.
3. Select the button and in the Click event select [Event Procedure] from the dropdown
4. Click on the three dots (...) next to [Event Procedure] to get to the code builder
5. In between the Private Sub... End Sub, enter the following command:

ShellExecute "H:\AccessProgs2003\allrock2000.mdb"

6. View the form in Normal View and click on the button

Just my 2 cents...

I started with a new database and followed your steps to the letter......................


Still does not work.

When I click on the button, I get:

Compile Error
Sub or Function not defined.

Here is how the screen is colored after I tell it ok to the error:
Click to view attachment

Hi,

Can you also post the module content in that new database? Thanks.

Here it is in 2 screenshots:

Click to view attachment

Click to view attachment

Okay, thanks. I apologize, I should have looked at the code closer sooner. Try the following instead:

fHandleFile "H:\AccessProgs2003\allrock2000.mdb", 1

Just my 2 cents...

Where do I put it?
I found it in the code but it was commented out or didnt have a path beside it.

Use it instead of:

ShellExecute "H:\AccessProgs2003\allrock2000.mdb"

So, just replace that part in your code with the new one I gave you.

Just my 2 cents...

First, since DBGuy and I are covering different parts of this it would help if you indicated who you are responding to or to use the Quote feature.

Yes there is no option to do that in the wizard. I told you to select the Run Code option. When you create a new menu item, you have three boxes. The first box is the caption that will appear next to the button. The second box is an action to take when a button is pressed. This is a pull down offering option like Open form, or Run Code, which is what you need to select. The third box is the parameters of the action to take. In this case you want to use:

fHandleFile "H:\AccessProgs2003\allrock2000.mdb", 1

Now this is all moot if you choose to just create your own switchboard by adding buttons to a form.

It worked.
I was very emotional when it finally happened.
I had to find something wrong under my desk so I could hide the emotions.


What did that change do?

Hi,


Congratulations! Glad to hear it worked for you.

We just needed to make sure that we use the "actual" name of the function, which I assumed to be "ShellExecute" but it turned out that it was actually "fHandleFile." Go figure...

Cheers

PMFJI:


Maybe a less troublesome approach would be to create buttons with hyperlinks on them to the various databases.

Click to view attachment

Oh, come on, George... That would be too easy.

I have tried to follow your steps but they do no coincide with what I see in Access.

I created a Switchboard in a blank database.
Dropped a button on the form and used the wizard.
At this point, I have no idea where the things you are talking about are located.
I am asked for an action for when the button is pressed.
I do not have an option to run code anywhere in the options.

I have categories and actions but none of them say run code.

What now?

Click to view attachment

Not the Button Wizard. In a previous screen shot, you showed a the form where you added a button. That form looked like it was created using Switchboard Manager which I specifically referred to. If you are just dropping a button on a blank form, that's different. I thought you might want to use Switchboard Manager because you referred to a Switchboard and your screen shot indicated using Switchboard Manager.

Scott,

The switchboard was my first thought.
I created some years back and figured it was a good place to start.
I guess not.

I have created so many databases today that I have lost track of what I did in them.

I tried to start a blank database and use the Switchboard Manager.
I go through the steps but for some reason it does not create it in the Forms like earlier today.
It creates a table called Switchboard Objects or something, but does not put an actual switchboard in the database.

I know you and everyone else is tired of seeing me type this, but what am I doing wrong now?

DB. Call me lazy, but I almost always prefer a 2 dollar solution over the 25 dollar variety if it does what I want it to do.

Glo. The switchboard in Access is designed to handle INTERNAL objects: other forms and reports, etc. Even if you can figure out how to modify it to work with EXTERNAL objects like other databases, you're choosing the 25 dollar solution when there's a perfectly good 2 dollar option right there. Inasmuch as the switchboard route appears to be a bit beyond you at this point anyway, why go on pounding your head on it right now. Once you've gained better mastery over Access, you can revisit this, IMO.

Hyperlinks on buttons is pretty simple and it will work.

Lets first pick one method and stick with it. If you want to use Switchboard Manager then we'll help with that. If you just want to use buttons on a blank form, then lets stick with that.

Sorry, Scott. I wasn't trying to step on your toes.

I appreciate all the help.

I like the hyperlink way better.
It will be a little easier to maintain, if a DB name changes.

I haven't used a switchboard in over 6 years.
It was probably Office XP.
I have a lot to brush up on.


I have noticed one thing that I am wondering about.
Why did the DB create a table called Table1, and why is the form linked to it?
What purpose does it serve?
If I delete the table, the form will not open.

"Why did the DB create a table called Table1, and why is the form linked to it?"

This is a default name for a default table. Access creates defaults when you create a new database. If you save them, they are there with the default name.

We don't see your form, so we have no way to know why this form is linked to this table.

The form was BOUND to the table. Yes, that means the form can't work correctly if the table is gone.

Not at all. I thought the OP might have wanted to use Switchboard Mgr, but its much better they focus on one method.

Is there a way to turn that behavior off?

If I want a table created, I will do it on my own.



NEVERMIND. If I just close the table, it goes away.

When the links open the new database, is there a way to make it not open in full window mode?

I am just using the hyperlink, not code.

Been poking around.
Thought I would see if it worked in the OnClick part under event tab.
Started it and tried to use doCmd. but then there wasnt an option for open database.

Should I be looking at this another way?

Okay, the hyperlink on the button is an alternative to code. Totally. No reason to go looking for other events. That's the whole point, in fact. Nothing to code.

"...is there a way to make it not open in full window mode?"

What do you have in mind here? What would that look like?

I like being able to see what else is on the screen, very seldom do I have one single program hogging up the whole screen.


That is no biggie.