Shared Applications: A Basic Question

[JerSand]

A fellow programmer has warned me that I am in for something completely different -- that my next ACCESS 2000 application will inevitably be buggy because it will be shared. (He says I should instead use SQL Server -- with which I have no experience -- and use ACCESS 2000 as a front end. However, this database will not be so large as to stretch ACCESS's capacity, so I don't understand why SQL Server offers advantages.) I'd be grateful for any confirmations, rebuttals, and pointers that would bear on my now-anticipated frustration upon distribution of the application.

To this point, I have designed ACCESS 2000 applications for single users. Occasionally, three or four people might be working on a project. For these, I would distribute copies of the application for non-shared use. When a report became necessary, I would ask the users to send me their databases; I would concatenate the pertinent tables and produce the report.

Now, I'm instructed to design an ACCESS application that a dozen people will use. I will place it on a shared drive on my office's network. I'd be grateful for opinions on whether this is something that will make the application somehow inherently unreliable or balky.

Thanks.

JerSand

 

[CTOROCK]

Well, SQL Server is a database for huge loads. You can create a multi-user application in access very easily. Just build your application, then use the database splitter.
I think that it's under AddIns. This will create another database with just your tables of data (you should put this on a network drive). you then Have your original file, which no longer stores the tables, instead they are linked to the other database. Then all you have to do is copy your old .mdb file and put them directly onto everyone's computer in their c: drive. It will have the links to the tables(it knows where to go, no matter where you put your mdb file) You now have a multi user database.
Let me know if this helps.
"The greatest risk, is not taking one."

 

[cheerio]

Multi-user applications should be designed as two databases. The frontend sits on each user machine and holds forms, reports and queries but no data. In the tables section there are links to the tables. The actual tables sit on the backend database which should be on a shared network drive visible to all users. They will need read and write rights to that drive.

This solves three major problems:

First, all the data is in one place so no need to consolidate and easy backup.

Secondly, if you design new forms or reports just make the updated front end available to your users. You could not do that before because their data was in their front end.

Finally, if the system does grow you can simply repoint the links in your front end to a major database such as SQL server and carry on as before. Later still you might want to move some activities such as update queries into the database. If SQL server is a long term possibility I would avoid spaces in names of tables and fields to avoid a possible need to rename things later.

The great advantage of SQL server is that updates can be done at the server. With linked Access databases the front end will run a query by calling for each record across the network and inspecting it locally to see if it is of interest. This creates a lot of network traffic.

For the time being build up your skills by using the two database system.

Good luck

Ken

 

[nberryman]

I seem to remember Microsoft class Access as a desktop database and recommend a 10 multi user limit.

YOu may find that the database is very slow but do follow the advice above on using a front end back end system or you will have problems. You can speed the operation up a little by placing the front end on each users PC so all heavy work is done locally.

Also go to Tools Options and click the Advanced tab. There is some sharing and record locking options there that you should look at.

From personal experience do not try replication as this causes more problems than anything else in Access
Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[oharab]

The 10 user limit is a myth propagated by bad database designers!
I have Access files running with 30+ concurrent users with no problems. I have heard people whom I trust & believe tell me about their databases with over 100 concurrent users.
As has been mentioned, you must split your database and have the front end file on the users machine. Have a look at faq705-2010 for my way of keeping the front end up to date. Also make sure your network is sound. Nothing will stuff your database quicker than a dodgy network! Access is a powerful beast that is more powerful than many "knowledgeable" programmers will credit.

Best of luck in what you intend to do.

B
----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[JerSand]

CTOROCK, cheerio, nberryman, and oharab: Many thanks for your terrific information and good wishes. Everything you told me was a revelation, and I know it will help immensely in my efforts to build a reliable shared application.

Sincerely,

JerSand