Setting for Access to open files simultaneously

You can do this via code.

Something try something like this in the first DB:

Dim acc As Access.Application
    Set acc = New Access.Application
    acc.OpenCurrentDatabase "c:\YourFolder\YourOtherDB.mdb"
    acc.visible=True

Thanks Boag2000:

I'm not sure that this is something for code, but I could be wrong.  The scenario: The user clicks on the mdb file on their desktop to work on that application.  But they need to reference another record in the other mdb file, so they go to click on it when needed.  This closes the open mdb, and then opens the other mdb.

It's not that I want them to open simultaneously each time.  Just so that Access gives them the ability to have several open mdb files and not just one at a time.

They could be opening 3 or 4 different files (not often), but Access limits them to have only 1 open at a time.  This seems like a function of Access program itself, as some users (Access 2003) can open several files at a time.

Also, if I were to add your code - would that be a module?  Or where would I stick that code?

Thanks,
Kandice

Then perhaps you should consider "Linking" to the tables in the other DB.

Any technique to "Open another database" and exchange data is a system that will be fraught with peril if not done with extreme caution...

Do you have iron-clad error handling and rollback code in place?

If the DB is already open
If the DB is being edited.
If the Location cannot be found
If the DB is of a different version
If the user runs the code at an inappropriate time
If you try to run a command that relies on settings in the "wrong" database
...and God help you if the network hiccups while you are doing any of this, (now will need iron-clad error handling and "Rollback" code...
If any conflicts arise between any of the front ends and Backends of any of the databases involved...


So could you *first* explain the need for this...?

JeffCoachman

Thanks Jeff:

When I came to my current employer I inherited a mess of multiple front end databases.  There are 3 distinct MDB files that relate to 3 different parts of the business process and each user has their own version of these 3 mdb files (exponentially that means that we have, at minimum, 18 different versions of 3 similar mdbs – insert facepalm here).  

Each of these 3 distinct mdb files (front ends) link back to the same SQL server database.  My best guess is that the person (no longer employed) who made these mdb files tried to tie them into 1 large front end app.  But the oral histories of those who still work here tell me that it "broke" everything.  And the only way that the amateur designer could make this work was to split the large mdb into 3 front ends that each had their own purpose.  They have been using/reliant on these mdb to run the entire business process for 8 years, not without issues.  We do have record locking and deadly embraces occasionally.  Working fast and furious to get us out of the current mdb files.

When I was hired, it was decided to stop trying to "fix" what was unfixable in the current mdbs.  We hired a database designer and we are in the process of creating a brand new front end application as well as a new SQL server design.

In the meantime, we still need to use these older mdb files.  And sometimes they need to be opened at the same time as they were poorly designed.  The users have been dealing with the limitation of opening them one at a time for now.  And the new version will not require more than 1 front end.

But I was hoping to give them some help until the new version is live.

Hopefully this explains, probably too much.

Thanks,
K

Hi Nick:

Thanks for your response.  I am using a single user and her mdb files to scour for the
Application.Quit

I also ran the same test that you did.  I am able to open up and have 2 mdbs open at the same time.  Each opens their own Access program.  They are in 2 different file locations.  The new test file I created was opened first and then I double-clicked on the monster.mdb file and was able to open it.  So there must be something crummy with the existing mdb files that makes them not play well together.

But when I try to do the same from within Access, it does not work and the program closes the open mdb, and then opens the 2nd requested mdb.  Also, it seems that if both files (referring to the moster.mdb files and not the new test file) are in the same location (specifically the desktop) - no matter how I open them; I can only have 1 open at a time.  I should note that the user claims that she has been able to have both open but that it is intermittent.  And I cannot successfully reproduce her result.

Still looking in the moster for the Quit code...

Thanks again,
K

Don't leave Jeff!
<But when I try to do the same from within Access, it does not work and the program closes the open mdb, and then opens the 2nd requested mdb>
That is the expected and unchangeable behavior as Jeff noted.
You may open multiple mdb's from shortcuts and double-clicks
You may only have one file open within each instance of Access, so using File|Open closes whatever you have on the go, and opens something new.

Now, do the monster files kill any other open Access file?
Open your new test one, and then open a monster (using double-clicks or shortcuts)
Does the new one close or remain open?
If the monsters only kill each other you can comb the code for their filenames

It may not be Application.Quit but it will definitely should be .Quit

Logical places for such code are in any AutoExec macro or module, or in the the Open/Load events of the first form that opens when a moster is roused.

As soon as I have access to the users desktop, I will report back with my test results.

Thanks Nick and Jeff.

<Off Topic>
Nick,  shoot me an email, my email is in my profile

Hi Jeff and Nick:

I created the new test database, opened it and was able to open up the monster database successfully, each within their own instance of Access.

I then closed both instances of Access.  Opened Monster1.mdb and then opened Monster2.mdb both with success.

I then restarted the computer, and attempted both monsters again - this time it did not work and one closed out the other.  Next I opened the test.mdb then the monster1.mdb and had success with both mdb files staying open in their own instances of Access.  Finally I opened both monsters.mdb files and they both remained open with their own instance of Access.

I did another reboot, and this time both monsters.mdb files opened in their own instances of Access without closing each other out.

I see no true pattern here.  Sometimes it works, sometimes it doesn't. I think that this is more a problem with the Microsoft Access program on this particular machine.  And not with Access in general.  

Thoughts?  If that is the case, I have no idea how to distribute points.  

Thanks for your help,
K

Points are really of not much interest to Jeff or me.
He has a heck of a lot more because he's been at it longer :)
No Biggie
Do what you think is best
Jeff's post at ID:36504986 was important to defining the problem
Mine at ID:36504620 suggesting that new files be tested was also important.

<I think that this is more a problem with the Microsoft Access program on this particular machine.  And not with Access in general.>

Profile issues can crop up, too
Logon as a different user to the problem machine and go through the same troubleshooting steps
Same results...then you are looking at something global to the machine
Remove/re-install may be in order
That failing, formatting the HDD and re-installing Windows may be in order.

If a different profile doesn't have problems, then a profile re-build may be in order

CPKGDevTeam,

As Experts here in the Access zone, we love Access.

But Access has some of the "Strangest" error you will find.
;-)
The oddest ones are the errors that have absolutely nothing to do with the underlying issue.
:-O

That is why I posted my concerns on doing things like this in: 36503891

Perhaps a strategic peppering of your code with "DoEvents" would help, perhaps it is a network timing issue, perhaps one of the "Advanced" options in Access would resolve this...

...Again, I know that you sometimes must "Build with the bricks you are given".

;-)

JeffCoachman

There is more than going on here than has to do with Access.  I believe that the users machine is in need of a re-image.  The erratic behavior of the Access program is either due to the operating system or the monsters DBs that we have been saddled with.

Both solutions given helped with troubleshooting the issue, so I split the points as best as I could.

Thank you as always for your help Jeff and Nick.

KLB