1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 97 switchboard to open .mdb

Discussion in 'Business Applications' started by plucnik, Sep 9, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. plucnik

    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
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    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...
     
  3. plucnik

    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
     
  4. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    [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...
     
  5. downwitchyobadself

    downwitchyobadself

    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.
     
  6. plucnik

    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?
     
  7. downwitchyobadself

    downwitchyobadself

    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.
     
  8. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    "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
     
  9. plucnik

    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
     
  10. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    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. :)
     
  11. THoey

    THoey

    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...
     
  12. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    ROFL!! I wouldn't go that far!!
     
  13. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/163525

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice