Access 97 switchboard to open .mdb

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

plucnik

Thread Starter
Joined
Aug 19, 2003
Messages
132
This is proably a simple question but.......
We run many Access 97 databases for use in the office. I wanted to create a "switchboard" of sorts to allow one interface instead of the many icons we now have on the desktops. The switchboard wizard that comes with Access allows an interface within a particular database but I guess what I'm trying to do is use a database / switchboard to open other databases. Any ideas? :D
Thanks
Peter

P.S. - "code" is not my strong suit so go easy on me if I have to write a routine to a module
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
I don't know that I'd do this with Access because it would just have to close this instance of Access and reopen another. Could get a bit tough on a PC throughout the length of the day.

However, you could make a folder that just contains shortcuts to the various DBs. As long as everyone has the same setup, you should be able to copy that folder and send it to anyone...
 

plucnik

Thread Starter
Joined
Aug 19, 2003
Messages
132
Thanks for the reply Dreamboat.
I don't think having multiple copies of Access databases is an issue because many of the employees keep dipatch, billing, rating etc open at the same time (it's a trucking company). The running copy of Access is on the local machine and the databases are kept on the server.
I did find a post on another site that gave some instructions on how to do it, but it doesn't seem to work (pasted below, maybe written for a later version of Access?). It stops at RunArguments() = Split(rs![Argument], ";") with an error message saying varible not set.
Thanks
Peter



Hi, I would like to make a menu in one database (.mdb file) and have several buttons that will open other databases (.mdb files)
Is there a way to do it?

Thanks,


I actually just did this same project a few weeks ago - I assume this is a sort of "organizer" combining a bunch of separate Access MDB files? If so... it's really easy to do if you want it to open up a new instance of Access, using the existing switchboard mechanism with a few modifications...

First off, create a Module named basLoader, and put the following code in it:

Dim appAccess As Access.Application
' This is dimmed globally so that the app. doesn't close when the subroutine completes

Public Sub LoadMDB(strDB As String)
' Sub to load another MDB file
' (Takes full path to database as argument)

On Error Resume Next

' Create a new instance of Access.
Set appAccess = New Access.Application

' Make sure we can SEE the new database. :cool:
appAccess.Visible = True

' Open requested database in our new window
appAccess.OpenCurrentDatabase (strDB)

' Check to make sure things opened correctly, and if not, alert the user
If Err = 7866 Then
' 7866 = Unable to open database

' Close the database we just opened
appAccess.Quit acPrompt

MsgBox "Error: Unable to open database." _
& "Database could be locked by another user or nonexistent." _
, , "Error!"

End If


' Close our original database (optional)
' CurrentDb.Close

End Sub

Next, make sure you have a switchboard form already. If not, create one using Tools-> Database Utilities -> Switchboard Manager.

Next, I recommend that you make a slight modification to the switchboard code, to allow the passing of arguments to functions when the Run Code action is attached to a button... open up the switchboard form in design view, and then look for the following bit of code:

' Run code.
Case conCmdRunCode
Application.Run rs![Argument]

and replace it with this:

' Run code.
Case conCmdRunCode
' If the argument passed has a semicolon in it,
' the portion of the string after the semicolon is
' treated as an argument to be passed to the function
Dim RunArguments() As String

RunArguments() = Split(rs![Argument], ";")

If UBound(RunArguments()) = 0 Then
Application.Run rs![Argument]
Else
Application.Run RunArguments(0), RunArguments(1)
End If

Now, you can set up new "Run Code" items in your switchboard like this:

Text: Training Databse
Command: Run Code
Function Name: LoadMDB;\\server\shared\trainingdatabase.mdb

And the database will launch "trainingdatabase.mdb" in a new window.

Hope this is what you were looking for!

James
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
[Argument]

Seems to me that Argument ought to be replaced with an actual argument and not just the word. Sort of like someone telling you to name a file filename.mdb, you know? I don't do VBA really, tho, so...
 
Joined
Oct 13, 2000
Messages
941
Nah, ignore that post. (Not dreamy's, the one you copied.) The only solution for opening one database without shutting the instance of Access that's running is found here, and it works like a dream. Read the instructions that come with the file, which tell you how to write the code. I repeat, automation--that's what that other post describes--will not solve this problem--you have to dip into COM, as the inimitable Trigeminal has done.
 

plucnik

Thread Starter
Joined
Aug 19, 2003
Messages
132
That last post may point to something that would be a bit beyond my skills. Let me ask my question in another way. If I created a form with command buttons for each of the databases that could be opened, what code modifications would I have to make to open up the different .mdb files?
 
Joined
Oct 13, 2000
Messages
941
Huh, I don't really understand the "other way" you're asking. It's pretty straightforward to use the little add-in I showed you; as the text file explains, you just (1) copy it to your system directory, (2) register it, by doing Start > Run and typing regsvr32.exe tsisoon90.dll, and (3) dropping a little piece of code into a public module in your db:
Code:
Public Function OpenMyDbOnShut()

    Dim strFile     As String
    Dim bytUserID   As Byte
    
    strFile = "C:\MyFolder1\Etc\MyAccessDbToOpen.mdb"

    With COMAddIns("TsiSoon90.Connect").Object
        .FileToOpen = strFile
        .Exclusive = False
        .FileIsAdp = False
        .CloseAll
    End With

End Function
Then, on the on-click event of the button you want to run, you just put "=OpenMyDbOnShut()".

The only other way to open other dbs is by opening other instances of Access, which is not a great idea--Access is meant to be a single-instance application. But you could do it using the RunApp macro and a command-line style thing, I suppose.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
"The only other way to open other dbs is by opening other instances of Access, which is not a great idea--Access is meant to be a single-instance application."

I HAVE BEEN VALIDATED!!!
:D
 

plucnik

Thread Starter
Joined
Aug 19, 2003
Messages
132
Hey guys, don't get me wrong. I really appreciate the help. I guess I'm a lttle slow sometimes and I need to have it spelled out for me. I see where Downwitchyobadself is going now (and thanks for your help and patience). I just didn't grasp the concept. Also, many thanks to Dreamboat for keeping this post alive.
Peter
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
LOL, Peter!
I often give the Access questions my best shot, even tho I don't consider myself very good at it. When down or thoey or somebody comes in and doesn't shoot down what I've said, it feels good. Lets me know I'm still doing okay with Access even tho I no longer have regular training on it. Don't take my comments personal. :)
 
Joined
Feb 12, 2001
Messages
3,420
Gee, Dreamie, can't remember ever shooting down one of your posts... Always spot on... ;) Besides, since you work with it more than I do now, I trust your opinion more...
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top