Solved

Setting for Access to open files simultaneously

Posted on 2011-09-08
14
227 Views
Last Modified: 2012-05-12
Hi All:

I need to find the setting in Access 2007 (and 2010) that would allow the user the ability to open 2 different mdb files simultaneously.  Currently, if there is an mdb file open and I try to open another one; the originally opened file closes.

I have looked in the options and canoot find a setting to make this possible.  I have also looked here and on the googles and could not find a solution (although perhaps it might be the keywords I am using).  Please let me know if you need more details, here are some: the files are local on the machine and this happens whether I open from the file menu or double click the icon for each.

Thanks in advance for your help!
Sincerely,
KLB
0
Comment
Question by:CPKGDevTeam
  • 6
  • 5
  • 3
14 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36503694
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

0
 

Author Comment

by:CPKGDevTeam
ID: 36503824
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36503891
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
0
 

Author Comment

by:CPKGDevTeam
ID: 36504093
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
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 65 total points
ID: 36504620
I am clearly missing something here.
I regularly have 3, 4 , 5 mdb files open at the same time.
So there MUST be code in each of these monstrosities that looks for the others and automates Access to quit.

Comb the code of all three for
Application.Quit
because Access by default will let you open many files.

Go ahead and create a new mdb on the desktop.
Open a monster.
Then open your new one.

They both stay open, don't they?
0
 

Author Comment

by:CPKGDevTeam
ID: 36504819
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
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 60 total points
ID: 36504986
Just a note here for clarification, and I'll let you continue on with Nick.

in a general sense, one "Instance"  of Access will only allow you to open one database.

If you open another *instance* of Access (either by hitting the MSAccess.exe file, or opening another DB from disk) you can have two "Instances" of Access open, both running one database each.
(Thus you have two databases open, one db for each instance)

I only mention this because many people think that Access is like Word/Excel, ...et all, and can open more than one "File" in one instance.

Either that, or they hear that you "Can't" have more than one database open at a time.

;-)

JeffCoachman
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 26

Expert Comment

by:Nick67
ID: 36505104
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.
0
 

Author Comment

by:CPKGDevTeam
ID: 36505133
As soon as I have access to the users desktop, I will report back with my test results.

Thanks Nick and Jeff.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36505174
<Off Topic>
Nick,  shoot me an email, my email is in my profile
0
 

Author Comment

by:CPKGDevTeam
ID: 36505330
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36505417
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36506384
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
0
 

Author Closing Comment

by:CPKGDevTeam
ID: 36549341
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
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now