Multi user environment

[Nishikawa]

Hi,

I need to allow my MDB to have 15 users accessing at the same time. They will be updating different parts of the same table using different forms. The system will also do an automatic update from another system in 15 mins interval.

Is it possible? If so, how do I configure my access database?

Please help.

Thanks!!!

 

[Dennisk]

Access is multi user by default. Place your tables called 'Back End' on a shared server.

Give each use a local copy of the Forms/Queries called 'Front End' etc on each Local Hard Drive and ensure each FE is linked to the one BE.

 

[speakers_86]

I am very interested in this. So the tables are on the shared server with the forms and reports on the local hd?

What if there is one column such as invoice amount that is used by multiple people, such as collections and payroll? There would only be one person adding the invoiced amount, but multiple people will use this number in a query. Will this work?

 

[Nishikawa]

Must it really be placed at the back end? Is it possible that everything is accessed through the server? (All local PC with act as a dummy computer)

Do I need to do the same thing if I were to publish the database on the web?

 

[The_Doc_Man]

Nishikawa - if the local PC is a "true" dummy, you should not approach this solution. If you are thinking about using the server as the place where Access is run, with something like a Terminal Services connection or CITRIX to support the multiple-user environment, you are asking for lots of trouble.

First, legal trouble. That style of Access use violates the end user license agreement for Access unless you have one copy of Access for each user.

Second, technical trouble. This forum has several threads in which users trying to share Access in this way have found that it too easily locks up and prevents sharing from working right when in the Terminal Services environment.

The idea of placing data in a back end and forms, reports, macros, and query definitions in the front end is optimization (performance). Access uses a server holding an .MDB file as a FILE SERVER, not an application server. It must read the tables (in most cases) in their entirety to process a query. But if the definitions are local, at least that much of the application doesn't need to be loaded over the network.

You would need something special on the Web Server to allow it to get to Access databases. Though you are right that you are more likely to get better sharing in the Web case. (Not guaranteed - it is a matter of relative probabilities for what gets accessed when.) I'm sure others here can tell you more about the web use of Access than I can. Our web database isn't built on Access, so all I know is principles, not details, in that case.

 

[speakers_86]

So I am going to store the tables on the shared documents, and the queries, forms, and reports will be stored locally. Is it hard to link the local queries with the shared tables?

What about security? Would it pose a problem to make the tables on the shared drive hidden?

 

[Len Boorman]

You must also have a file locking strategy.

Edited record must be locked by first editor, otherwise you are heading for a major problem

Row level locking (Edited record) is the sort of locking required.

Means only one person can edit any part of a specific record at a time

Other records free to edit by other users

L

 

[speakers_86]

Initially I was working on this locally, with tables and queries all in one file. I just moved this file to the shared documents, in preparation for office use. when I did that, access created another file, and when I hover the mouse over it it says "record locking information". Is that what you are talking about?

 

[Len Boorman]

Not really. An ldb file is created each time an mdb file is opened.
Process is:
First User created ldb
Next users modify ldb

as people close
users modify
Last user deletes


If you look at a the properties of a form you will see an item Record Locks
You can set here or under Tools/Options/Advanced you can set default locking.

But default locking could end up locking records which are being opened as read only as well ( I believe)

L

 

[Simon_MT]

Nishikawa

You can use Access in a Terminal Server environment with no problems at all. The CAL Client Access Licenses I understand legitimises the mulriple use of licences. If you are using this TS I would allocate a Front End database version for each user and ensure that the default database folder is different for each so that they don't overwrite over each other's exported files.

The only time locking becomes an issue is when to uses are trying to update the same record simultaneously.

An enterprise wide solution is a much better bet with one database on the server and Client Front Ends and it matters not whether or not you implement Terminal Services.

We have a New York and a Cork Street gallery linking into Hoxton in London handling thousands of images. The data was no problem but the images required greater bandwidth which was resolved by SDSL.

Simon

 

[The_Doc_Man]

Ah, Simon, the Client Access Licenses are new to me. Thanks for the info. But we still have many posts about how "touchy" the TS environment can be if you don't hold your mouth just right.

 

[Simon_MT]

DocMan,

I have been away so forgive the tardy reply. I know from my own experience that there are several things you need to think about with TS.

Performance over an ADSL from the Terminal Server with a limited upload speed is slow. To eliminate this an SDSL line is a much faster solution. This is not a fault of Access but simply a result of the up traffic.

On the client side Business ADSL is required as there are inherent performance latency, not guaranteed with standard ADSL.

Using a dedicated line helps or not using the broadband line that transports email and anecdotally removing tracking cookies increases the bandwidth.

Terminal Servers do work and it is a good solution but can become moribund with the demands created by the traffic. We have thousands of images used referentially within Access - these created the performance problems more than anything else.

My last comment having gone down the synchronising route, Terminal Services is a much better solution because the organisations documents can be delivered remotely and these files are often just an important as the database.

Simon

 

[Nishikawa]

My company uses a corporate license for MS office, it should not be of any license problem if multiple users uses the same database at the same time. I'm just worried that different users logging in and updating different table will cause the database to malfunction.

Moreover, I do not have much programming experience. How do I convert access to a back and front end database?

 

[Dennisk]

one of the tools available is a database splitter. But its just as easy to do it mannualy. Create an empty db and import your tables. Create another db and import Queries/forms/reports/macros/modules.

Open the db comtaining the forms and select file link a file dialog will appear. navigate to your backend and select the tables to link. One your tables have been linked you can re-link from the tools menu using the link table manager.