One user at a time?? 1

[booboo0912]

Can someone tell me why only one user is able to access a database at a time? I'm using NT permissions, and this used to not be a problem, but all of a sudden only one user can get into the database...others must wait until that person exits. Is this normal? We're using Access 2000.
Thanks!

 

[ajdesalvo]

You may have the database set for exclusive use of the start up of the database. You can check this setting from the Database window menu bar:

Choose the following:

Tools
Options
select the "Advanced" tab

Set "Default Open Mode" to "Shared"

Hope this helps! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[booboo0912]

Thanks for responding, Anthony! I checked, and I do have "Shared" selected. Any other ideas? My Default Record Locking is set to "No locks", and the "Open databases using record-level locking" is checked.

 

[ajdesalvo]

Can you give me the exact message the users are getting when they try to open the database?

In Access 97, more than one person could edit a mdb on a shared drive at a time. Access 2000 will allow 2 or more people to open the database, but a warning is issued that any changes made on objects may not be saved.

Is your application using a front-end/back-end setup. If so, having the workstation running the front-end database on each workstation may solve your issue.

This adds some additional work when an update is made to the front-end database, and you need each workstation to be updated. I create icons on the users desktops that runs a simple batch file to copy down the latest version of the front-end database if it has been updated.
Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[booboo0912]

The message we get is "Could not use 'path to database/file.mdb'; file already in use. This message pops up if one user is in the database...no one else can get in.

I'm not sure what you mean by using a front-end/back-end setup...the database resides on a server, and all the users have a shortcut to that database on their desktop. Does that help?
Thanks again!!

 

[ajdesalvo]

The front-end database contains objects like the Forms, Queries, Reports, Macros, Modules and any temporary tables used for processing.

The back-end database has the Tables that are shared by all users.

The front-end database links to the tables in the back-end database.

This way, if you need to modify a form, you can make changes and deliver the database to the users without overwriting the data in the tables.

Have the NT permissions changed recently? All users must have read, write, and create rights in the folder where the database resides in order for Access to create the .ldb file it needs.

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[booboo0912]

Interesting...I've never created a db like that! I just have the one database with all the objects (tables included), with the users accessing it via the shortcut on their desktop. How difficult would it be to change it? And would you suggest changing it?

The NT permissions haven't changed...BUT only a few users have access to write to and delete from the db (read and execute as well). All other users only have read and execute access...we've had problems with people accidentally deleting data so we need to limit add/delete priviledges.

Is it normal for Access to behave like it is, with the way I set up and access the database?

 

[ajdesalvo]

It is not hard to set it up. Create a new database and import the tables from the original database that woudl be considered "shared data" into it. This becomes the back-end database.

Then go to the original database and delete all "shared data" tables. Then, in the database window, click on File, then Get External Data, and Link Tables.

Then Browse for the location where the back-end database is stored. The Link Tables dialog box will appear. Press the Select All button, then click OK. The tables are now linked.

If every has the same drive letter mapped for the server where the back-end database is, then every one should be able to run the front-end database on their workstation.

In order for the users to run Access on the server, they need to be able to write to the folder. Opening Access creates a .ldb file, which if it cannot be created, then Access won't open the database.

You can test this out by changing the user's permissions to see if they can then open the database.

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[booboo0912]

Hmmm...so the database with the tables would reside on the server, but the original database (without the tables) would be copied to everyone's desktop (no shortcuts involved)? And maintain the NT permissions of the database on the server?

 

[ajdesalvo]

>>Hmmm...so the database with the tables would reside on the server, but the original database (without the tables) would be copied to everyone's desktop (no shortcuts involved)?

Yep!

>>And maintain the NT permissions of the database on the server?

If you mean not allowing writing to the server database, then the answer is no. Even though the workstation database is being opened, as soon as any data is accessed from the server-side database (linked tables), the .ldb file would have to be created. Therefore, write permissions are still needed. Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[booboo0912]

How would I prevent those that currently have read only rights from adding/deleting to the database?

Also, if I made a change to, let's say a form, if everyone has a copy of the front-end on their desktop, how would I ensure the new form gets updated on everyone's desktop? (Please bare with me...I'm learning a lot!)

 

[ajdesalvo]

>>How would I prevent those that currently have read only rights from adding/deleting to the database?

Use Access's Security features. You can assign rights to objects in the database to individuals or groups. Read Microsoft Knowledge Base Article - 207793 for all the info you will need on security!


>>Also, if I made a change to, let's say a form, if everyone has a copy of the front-end on their desktop, how would I ensure the new form gets updated on everyone's desktop?

We have several clients that accomplish this by creating a batch file that has a copy command for copying the latest database to the local workstation. A shortcut to this file is added to the Windows Startup folder. Every time the user logs into their workstation, the latest version of the database is copied to their workstation.

Using the Notepad, create a new file. Add the copy command, for example:

copy f:\Access Database\Latest Version\MyApp.mdb c:\Access Database
Save the File with .bat as the extension, for example:
c:\Access Database\UpdateMyApp.bat

Create a shortcut to this file in the Windows Startup folder.

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[booboo0912]

Unfortunately, copying anything to our hard drive is forbidden (no backups are done on the C: drive). I will have to figure out a work around, or at least try to minimize any updates to the FE objects...which shouldn't be a problem...the customers are pretty set with what they have so far!

Thank You VERY much for all your help! What a learning experience this has been!! Have a great day!!

 

[proed55]

Hi, ajdesalvo. You sound like really know a lot about Access. Question: I have tables with auto number. When I create a copy of a table, modify it and try to replace the original with modified one. My autonumbering gets screwed up. Any way around it?Thanks