Open form from another database

[sphere_monk]

Hi everyone,

I have two databases. One is a billing database that has customer account info in it. The other is a contact management database to record phone calls as they occur.

Employees usually have the customer account form in the Billing db already loaded when a call comes in. I would like them to be able to hit an 'Enter Call' button that does the following:

1. Evaluates whether the Contact db is open already
2. If it is, it loads the 'Enter Call' screen
3. If it isn't, it opens the Contact db and opens the 'Enter Call' screen

I have the following code already:

Private Sub cmdEnterCall_Click()
On Error GoTo Err_cmdEnterCall_Click

Dim appAccess As Access.Application
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection


rst.Open "SELECT Filename FROM SharedLiveFEDatabases " & _
    "WHERE DatabaseName = 'ContactFE'", cnn
rst.MoveFirst
Set appAccess = CreateObject("Access.Application")
    ' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase rst!FileName
    ' Open Enter Call form.
    appAccess.DoCmd.OpenForm "frmContactEnterCall"

Exit_cmdEnterCall_Click:
    Exit Sub
    
Err_cmdEnterCall_Click:
    MsgBox Err.Description
    Resume Exit_cmdEnterCall_Click
    
End Sub

The problem is that with this code, the Contact database gets opened repeatedly, every time the 'Enter Call' is clicked. Does anyone know how to evaluate whether the db is already loaded? Or maybe there an altogether easier way to accomplish this?

Thanks,

 

[DCrake]

Why not import the enter call form into the billing db and the appropriate links then simply open up the form within the billing db. I know it's a bit of duplication but far simpler and less problematic than your first thoughts.

Reason for stating this is that if it is a one man db then your option could be made to work, however if you are running this in a multi user environment then the event call db may be open legitimately by another user. so each user would have to wait until there was nobody using the db.

David

 

[sphere_monk]

Hi David,

Thanks for your reply.

I wish it was a one man db, it would be much easier!

These databases are in an office environment where different employees have access to different shared databases. Almost all employees will be using the Contact DB where the Enter Call screen resides, but only a few will be launching the Enter Call screen from the Customer Account screen in the Billing DB.

I could import the Enter Call screen into the Billing db, but my screens are undergoing changes so much that I would rather not have to maintain two versions of the same screen. I thought there must be a way to automate the launch of a db and form from another database.

I am curious about your statement:

in a multi user environment then the event call db may be open legitimately by another user. so each user would have to wait until there was nobody using the db.

Is this because of the way the database is being opened in the code? Currently all of our FE's are shared by multiple users at the same time without any lockout problems.

Thanks, again,

Dan

 

[Mutdogus]

I use this bit of code to determine if a window is open or not.

Find out if an application is currently running

 

[DCrake]

If two or more people from differnt pc's are opening the same mdb in a shared location this can lead to corruption problems. Your thread seems to intimate this.

Even if the back end is split from the front end a dual or multi connection using the same route is established. I would be tempted to install the mdb one each of the users machines so that this is eliminated.

Back to main question you can open another instance of a database and by supplying a /macro name you can invoke a certain form to open. As long as that form does not have any dependancies on other forms being open or reliant upon.

 

[sphere_monk]

Thanks for the code Mutdogus!

I checked the code out. Please correct me if I'm wrong, but I don't think the code can tell me if a specific database is loaded. Since the user would be calling the Contact DB from another Access DB (the Billing DB) then wouldn't the code return a value of 'True' either way?

DCrake -

I've overheard in various forums that when a split database is used in a multi-user environment, that it is best to install a version of the FE on every user's machine. I have never run our databases in that configuration, I have always had users share both the FE and BE databases. I have never had any data corruption problems, even with literally hundreds of thousands of transactions being processed. I really would like to setup the databases to work correctly, but my design work would take much more time if I had to copy all my changes to every user's computer. Do you have a link or any other information that would detail the exact scenarios that create corruption when sharing the FE database?

Using the code in my first post I was able to open another instance of the Contact DB and invoke the correct form to open. I really would rather not open multiple instances of the same db if it's avoidable.

It sounds like there isn't really a way to tell if another database is open. Is there a way to set the focus to a form in another database? Assuming, of course, that the user has already opened the target db and form.

Thanks for your help!

Dan

 

[Rabbie]

I've overheard in various forums that when a split database is used in a multi-user environment, that it is best to install a version of the FE on every user's machine. I have never run our databases in that configuration, I have always had users share both the FE and BE databases. I have never had any data corruption problems, even with literally hundreds of thousands of transactions being processed. I really would like to setup the databases to work correctly, but my design work would take much more time if I had to copy all my changes to every user's computer. Do you have a link or any other information that would detail the exact scenarios that create corruption when sharing the FE database?

Using the code in my first post I was able to open another instance of the Contact DB and invoke the correct form to open. I really would rather not open multiple instances of the same db if it's avoidable.

It sounds like there isn't really a way to tell if another database is open. Is there a way to set the focus to a form in another database? Assuming, of course, that the user has already opened the target db and form.

Thanks for your help!

Dan

One of the main advantage of each user having a copy of the FE database on their own machine is improved performance because forms etc dont need to be loaded over the network.

 

[boblarson]

I've overheard in various forums that when a split database is used in a multi-user environment, that it is best to install a version of the FE on every user's machine. I have never run our databases in that configuration, I have always had users share both the FE and BE databases. I have never had any data corruption problems, even with literally hundreds of thousands of transactions being processed.

That is just sheer luck. Running in that configuration is not a matter of IF it will corrupt, just WHEN.

I really would like to setup the databases to work correctly, but my design work would take much more time if I had to copy all my changes to every user's computer.

How about a tool that would let you do up changes to a master copy and then the next time a user starts their copy it automatically copies the new file down and reopens for them, if the version has changed? I have such a tool (free) on my website and I have hundreds of users using this, including a large power company here in Portland. Here's the link to the tool and documentation (read the documentation carefully):
http://www.btabdevelopment.com/main/MyFreeAccessTools/tabid/78/Default.aspx

Here's also some information about split databases and running on a WAN:
http://members.shaw.ca/AlbertKallal/Articles/split/index.htm
http://members.shaw.ca/AlbertKallal/Wan/Wans.html

 

[DCrake]

Here is a demo I put together a while back that tells you which users have a specified mdb open and on what machines they have it loaded on.