DB access by two users...

[maxcessnapilot]

How to allow two users to access a DB and save at the same time in Access?

thank you.

 

[BNPMike]

If you get the two users to open the same database, what problem do you get - it should be OK?

 

[dhookom]

To add to BNPMike's comment, there shouldn't be an issue if the users are editing different records. If users are editing the same record at the same time this usually suggests your table structure is wrong.

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

If you have simultaneous, multiple users, your app really should be split into a front end/back end database, with each user having a copy of the front end on their PC and the back end. with the tables on a shared drive. Here's a good tutorial that explains how to do just that:

http://bytes.com/forum/thread737435.html

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[SeeThru]

I've had 10 on a single access file (Access 97). Slightly slow, but still works.

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[JoeAtWork]

I've had 10 on a single access file (Access 97). Slightly slow, but still works.

Not a good idea. You are increasing the likelihood of corrupting your database exponentially.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[Hap007]

Hi,

Having 10 users open a single Access file is bad, but if you have a Frontend on each user's PC, linked to one Access Backend, then you will have no problems.

I have several clients who have over 20 user's, who open Frontends on their PC, all linked to a common Access backend, with no problems.

You just need to have good coding habits.
1) Close all recordsets in code, before exiting module
2) Make sure users exit Frontend properly
3) Backup and compact the backend on a regular basis.

Note: When my clients exceed 10 users, I try to recommend moving to MS SQL, but not all clients are willing to spend the extra dollars for SQL and a Server.

Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[SeeThru]

We are looking to move to SQL, but not got there yet.

I think the times we have had loads of users on a single system we have got away with is as
* Used Access97 - apparently better for this
* Users were only doing simple updates and adds - no bulk updates
* All users had identical versions on the same O/S

Interestingly, I did try splitting the database. It would work fine in testing, but after 2 or 3 hours of use, it would start giving out of memeory errors. I speant ages looking fo unclosed recordsets, objects still allocated, and found nothing - I could only assume that the closing and re-opening of a linked table was consuming resources slowly.

I moved back to a single file, and have had no problems since. Database corruption is very rare (maybe once this year in one of approx 120 databases)

We are working on moving to SQL with a VisualStudio front end now anyway, but I rather like the simplicity of a single Access file.

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[KornGeek]

I've had 10 on a single access file (Access 97). Slightly slow, but still works.

I once drove recklessly, exceeding the speed limit, weaving in and out of traffic, not using my signal, etc. Nothing bad happened, nobody got hurt, and I didn't get a ticket. In fact, at the time, it seemed fun. This does not mean that it is a good idea, and I definitely wouldn't go around recommending it to others.

I'm glad that you have encountered few errors, but if you read these forums, you will find that this puts you in the minority.

If you have more than 1 user simultaneously using an Access database, it is best to split it into front- and back-ends. This also allows you to modify the interface design (front-end) without touching the data (back-end). It greatly reduces the odds of corruption, and improves performance. You might want to consider this approach even if it is a single-user database.

 

[maxcessnapilot]

Thank you so much for all your input. I think Front End Back End (just like our email system) will work best for us. Thanks!!!