Access 97 Shared Database

[AccessHelp123]

Hi All,

We have an Access 97 database that used by several people in the company. Access to this database is granted using a .mdw file. Users have been complaining lately that people are unable to simultaneously make changes to the database. I checked the Tools-->options--Advanced section. The default open mode is set to "Shared". Is there anything else I can check to make sure multiple users can make changes?

Thanks.

 

[dhookom]

Check the folder where the Access file is located. Can every users create and delete files in the folder? When a user goes into the application, do you see an LDB file created in the folder?

Duane
Hook'D on Access
MS Access MVP

 

[AccessHelp123]

Yes. Users are able to create files in the folder and the .ldb file is visible when the application is open.

 

[dhookom]

What do you mean by

simultaneously make changes to the database

Are users attempting to make design changes or simply records changes/additions?

Duane
Hook'D on Access
MS Access MVP

 

[AccessHelp123]

Record changes using VBA and a form...

 

[dhookom]

Can you share a non-secured database in the folder?


Duane
Hook'D on Access
MS Access MVP

 

[AccessHelp123]

The problem seems to have resolved by itself. Not sure how... It would have been nice if I actually knew what happened.. Thanks for your help anyway....

 

[lameid]

Access 97 definitely uses Page locking and not recordlocking... so a record can span multiple pages and multiple records can span a page... Generally this means two people are changing things near the same spot in the table might be trying to lock the same page. That may have caused your problem as well as general network/server slow down.

My experience is that later versions of Windows Server are less forgiving to Access performance (Post NT4) by default. File fragmentation also seems to play a role in performance. Keep in mind an Access database tends to keep growing while other files get written to it adjacently on the disk. If this is the problem, a compact should fix/improve the problem, at least temporarily. An ideal solution is to put Access databases on a server not doing traditional file serving, configure file locking options (it has been too long since I even saw an article on it to be specific), and put the files on their own logical drive so file fragmentation is not forced but bunches of new files. Keep in mind you need free space as large as your largest file to compact it and more free space is good to defrag the drive occasionally. Of course I put this advice out there but setting up a logical drive for any reason is easier said than done, or at least the last time I did it it was non-trivial.