I have a MDB file on the network drive.
It is set to sharing under options -> Advanced.
I have also set: Tools -> Security -> User groups and permission
under: Owners -> Allocating the tables/queries/forms to the "Users" group.
I managed to open the MDB file from 3 different computers at the same time.
However, after 3 days there is an error message that says the file is locked. Cant even open the MDB file from a second computer.
What is the correct/best way to implement sharing for the MDB file?
Thanks!!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi sky99yahoo,
I used to share an mdb file between about 5-10 colleagues and what you are describing happened to us about 1 or 2 times per day. The problem happens when a user, knowingly or not, locks a table (usually by entering design mode on a form in our case). A good way to solve the problem is to set up a dedicated sql server (MySQL http://dev.mysql.com/get/D
Cheers!
The "proper" way is to split the db into front ends and one back end. All tables in the back end, and everything else in the front ends, of which there are as many copies as users. Initially create backend and frontend, link the tables from backend to frontend, then copy the front as many times as required, giving each a different name. Then provide each user with a shortcut to their copy of the front. This process should resolve all simultaneous opening issues.
Chris B
Yes, I agree with Chris. But *avoid* keeping the backend in a MSOffice product. For example, you will have worse locking problems if you use Excel or Word. Using MSAccess is possible, but I guarantee you will still have locking problems (we did). ;-) Try using a dedicated backend product. MySQL is a good example because it is *free* and *very* well supported, plus it runs in Windows on even crappy hardware (not recommended though).
Sean
<<MySQL is a good example because it is *free* and *very* well supported, plus it runs in Windows on even crappy hardware (not recommended though).>> Entirely true, however MSDE is a good example because it is *free* and *very* well supported, plus it runs in Windows on even crappy hardware (not recommended though), and Microsoft gives it away. Let us not be too hasty in knocking Microsoft.....
Chris B
Your locking issues came about because you (apparently) had several users SHARING the same frontend. Sean's locking issues came about due to users attempting to make design changes to a live, in-use product (not a good idea), but I doubt you'll run into this. As Chris said, you should split your database and deploy the Frontend to users. Each user should have their own copy of the frontend, and all frontends should connect to the SAME backend. While this won't completely resolve your recordlocking issue, it will certainly go a long way towards relieving it.
While I'm a big supporter of server-based data, a well designed Access database can easily support 10 - 20 users. Merely moving your data to SQL Server, MySQL, et al is no magic pill - in many cases merely moving your data to a server-based product will actually slow your performance, and then there's the issue of support. If you're having trouble getting Access properly configured, I can guarantee that you'll have trouble with one of the server based engines, and often this means hiring a consultant to get things straight. Besides, if you've got record locking issues with Access, you'll have the same with any data store until you resolve the reason for the locks.
LSMConsulting is right about the speed. Moving to a dedicated back end may slow down operations, but record-locking will not be an issue any longer. MySQL (and the others) place table or record-level locks on data as changed as being made. Instead of the mdb completely crashing for 15 people and requiring everyone to CTRL-ALT-DELETE msaccess.exe, a dedicated backend will make a simultaneous writer wait .025 seconds or so and then write the record (at worst, the user would have to repeat the write-operation sequence). That said, LSM is right about complexity. MySQL is not ridiculously hard, but know your limits because it is a bit "non-trivial."
Sean
http://en.wikipedia.org/wi
MSDE
From Wikipedia, the free encyclopedia.
MSDE stands for Microsoft Desktop Engine. It is a toned down version os Microsoft SQL Server which is free for non-commercial use (and certain limited commercial use as well). MSDE and SQL Server are Microsofts database platforms. MSDE does not have all of the optmizations and thus is not as fast as a normal SQL server installation, but it is fully functional. It can be distributed with commercial products by registering with microsoft - in most cases this distribution is also free of charge. The product is intended primarily to compete with MySQL, and get users familiar with SQL Server, so that when the migrate to larger needs they will choose to purchase the retail version of SQL Server, rather than another database solution.
Wow!
Thanks for all the comments.
Actually I would prefer to port the db to mysql/php as I have some knowledge there. However, the client is quite resilient to the shift from their MSaccess file, which they have been manually updating for some time.
I merely created a form to make it simpler for some of the temp staff to key-in data. The manager stills prefer to enter data directly into the table view.
However, I am new to access, so I guess I must have made a mistake in having the forms in the same MDB as the tables...
I have made it such that the users will not be able to see the database window(hidden on startup)
Thanks for all the comments once again. I'll consider making a front end and a back end in Access. Failing which, I will have to persuade the client to take up a shift to a server-side solution.. :p
"This was supposed to have been a simple fix... -.-"
Business Accounts
Answer for Membership
by: InteractiveMindPosted on 2005-03-28 at 02:31:57ID: 13642646
Have you considered running a small weberver (such as Apache's one), then sharing your files that way?
lol -- just a thought. :)
>> IM