Trying to open other db's from another

[jlm722]

Pleeeeease someone help me! I have four access 2000 databases. Let's call them eenie, meenie, miney, and moe. I currently have four shortcuts on my desktop that opens each. I want to create a generic database that opens a form which has four command buttons named "eenie" "meenie" "miney" and "moe", that will launch each application. I don't need the buttons to open a specific form, i just need it to perform the same function as the windows desktop shortcut. I've tried several ways, and the problem I keep having is when I finally get a command button to work, it will open multiple instances of each db when clicked again and again. I want to be able to keep each db up and running, and when the command button is clicked, it will just bring the running db to the front and not open another instance of it. SURELY this isn't impossible!

[dcrake]

I have an app that allows the users to open anyone of several mdb's and when launched it brings that mdb to the forefront and keeps the focus until the app is closed then the user is returned to the original mdb. Do not have it with me today but will get the code to you on Monday if that ok.

David

[jlm722]

Yes yes yes please! I would be soooo greatful!

[jlm722]

David, the deal is, I need to be able to keep the four mdb's up and running and switch back and forth between them. Will your app allow this?

[dcrake]

 
Public Function Toolkits(Letter As String)
Dim stAppname As String, Kit As String
Dim PathName As String
PathName = " C:\AccessSystems\"
Kit = "X"
Select Case Letter
    Case "S": Kit = "Stores2000.mde"
    Case "E": Kit = "Extrusion2000.MDE"
    'Case "D": Kit = "MasterDirectors.MDE"
    Case "I": Kit = "Inspection2000.mde"
    Case "F": Kit = "Finishing2000.mde"
    Case "Q"
    If Dir("C:\AccessSystems\Sales2000.mde") = "" Then
        FileCopy "V:\Toolkits\Access 2000 Toolkits\Sales2000.mde", "C:\AccessSystems\Sales2000.mde"
    End If
    Kit = "Sales2000.MDE"
    Case "W": Kit = "Moulding2000.MDE"
    Case "T": Kit = "Tool2000.MDE"
   ' Case "Y": Kit = "MasterEnquiry.mde"
    Case "Z": Call msgbox("Please launch the download from the Development PC as it is the latest version and will run more efficiently", vbCritical + vbOKOnly, "Warning")
    
    
End Select
If Kit <> "X" Then
    stAppname = "msaccess.exe" & PathName & Kit
    Call Shell(stAppname, 1)
End If
Toolkits = Kit
End Function

In the above example I had a custom menu that listed the applications that the user could open. These were in a shared location on the server.

The command line on the menu item was =ToolKits("x") where X is the parameter sent to the public funcion ToolKits().

As you can see in the code the fisrt thing was to detect if the file existed in the known location then it used FileCopy to copy it from the server to the local machine. This was to reduce the load on the network as Access would have to drag the app across the network along with the data.

I then used Shell to open up the app. The app also appears in the task bar on the desktop so this allows me to tab back to the master mdb and if required I could open up a further mdb.

Hope this helps

David

[jlm722]

Mr. David, no disrespect meant, but I have no idea what this code is you've posted for me. I've tried to use it, but I'm evidently not understanding something. I still wonder if I'm making myself clear on what I'm trying to accomplish. I need a command button to open a .mdb! It needs to work JUST like a desktop shortcut. If the .mdb is running in the background, I need it to bring it to the front instead of opening another instance of it. The files i'm trying to open are Check.mdb, Title.mdb, CC.mdb, and Daily.mdb. Can you or someone write code that I can use behind a (4) command buttons to open each of these? (actually just for one, of course i can duplicate) i want my master form to stay open and not close when these are clicked.

[dcrake]

In the main declarations section of the form you first need to declare a couple of variables

 
Dim strPath As String
Dim strMDB As String
Dim strAppName As Sting

In the OnLoad event of the form set the location of the mdb's

strPath = "<<PathToMdbLocation>>"

Example:
strPath = "C:\Program Files\Databases\"


On the OnClick event of a given command button type in the following

strMDB = "<<NameOfMDB>>"

Example:
strMDB = "Check.Mdb"

 
    stAppname = "msaccess.exe" & strPath & strMDB
    Call Shell(stAppname, 1)

Follow these steps and you should be ok

David

[jlm722]

ok guys, I told y'all I'm new here. What on earth do you mean when you say "in the main declarations section"? I have searched and searched and find no such explanation

[jlm722]

i've tried, and tried to make this work, all i get is "file not found", and David, is your "r" not working? read your post, it says Dim strAppname as "sting", and strAppname in places and stAppname in others. just curious

[dcrake]

It'snot my r that is not working it's my finger. Compound this with my dislexia and leaving my glassess at home, I consider the reply was ok.

Go to the design of the form then right click on the top left hand corner on the little box and select build event. You may see the words Option Compare Database. You will also see two combo boxes in the left choose (general)
underneath and before and sub routines are declared is known as the main declarations section. This is where you need to delclare your variables.

David

[jlm722]

:0). Lol ok. Thanks Mr. David. Now It works, and opens my file, but still repeated clicks of the command button are opening multiple instances of the db. I want the command button to bring the open db to the front active window, and not open another instance. What is the trick for that?

[dcrake]

So what you are saying is Yes to opening the first instance of a known mdb but No to second or subsequent instances of the same mdb.

To do this you will need to include a bit of admin.

Create a table in the main BE that holds the names of the known mdb's include in this table a field (boolean) named IsOpen and set the default value to No. Now when the user clicks on the command button from the master mdb it performs a search on the admin table to see if the value of IsOpen = False if it is it then it updates the field to True then proceeds to open the database in question.

Each of the child mdb's will have a link to the admin table in the BE. When this child mdb closes it peforms an update on the admin table to reset the IsOpen field to False.

So in conclusion the master will only allow the child to open if the IsOpen Field = False.

Taking this one step further are there going to be more that one person using the master at any one time? If so you will need rethink your strategy by including the computer name into the admin table to check if the user of that machine has got the child mdb open or not.

David