Running Access Database from Server with multiple users.

[Krash878]

Hello all.

I have a database that many of you helped me develope by your problems and answers. I created a database that at first was supposed to be a single user application which now is residing on one of our servers.

My new task is to change the database to be a server application. When we put the database on the network we noticed a few problems. First the speed at which the database opens and returns data is very slow. Second every night our network is backed up. Some of our users forget to shutdown their PCs at night and stay logged into the database. When this happens the database becomes locked and gives an error that says the database is already in use. We must then go to our server and disable the connection in Administrative tools.

I do not know anything about how to speed up the database or where to look. My boss mentioned that we may be able to have apps on each user's PC that pulls data from the network Database and writes to it as needed.

I do not have any knowledge of Server Databases.

Any pointers would be helpful.
Thanks
Krash

Krash878@hotmail.com

 

[WildHare]

Krash, here's the scoop.

First, there are probably numerous utility functions floating around which you can add to your database to log people off after a certain amount of time. Even a hidden form with a timer set to check the time and after 18:00 hours or something issue a QUIT command is crude but works.

Secondly, Access is not a "server" database. It's a file server only. What this means is this: If the DATA is on the server and everyone has a front end on their PC, when User A says "get me one record from Table B", the entire TABLE B is sent over the network cable to user A's PC. User A's PC copy of Access then finds the one record.

If the Front end is on the server too, then the entire front end copy needs to be pushed down the wire to the client when they first open the database.

The upshot is, you have to realize that Access on the server is "dumb". Extremely DUMB - it is not doing any data evaluation at all - the entire table(s) needed for any queries or forms are shipped to the user on first request.

One minor tweak you can make is to keep fairly static "lookup" type tables on the Front ends on each PC. This cuts down the net traffic a little bit.

Another thing you can do, depending on the application, is to use shadow tables on the F/E for "ADD" purposes, and transmit them to the B/E on demand. This way, for example, you don't need to send down a 30K record table if you are just gonna open the form it's based on in ADD mode....

There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Sorcerer1]

also worth making sure that all access versions are the same, including your ADO and RDO versions, if you have incompatation ADO and RDO these can slow down the reterival time.

Have u considering storing data in SQL Server with Access front end??

 

[RSGB]

Hi

I'm considering this same issue as although the speed for people connecting at my site is fine, now people at another site are using it and it's too slow for them. This is my current thinking:

1) back end to SQL server. Seems to be thought of as the best solution BUT according to

http://www.SQLMag.com

there are a lot of steps involved to get the upsizing wizard to work ideally (e.g. maintaining referential integrity and 'cos Access and SQL server appear to use slightly different versions of SQL) plus redoing queries as stored procedures. Looks like a lot of development & testing time.

I tried this on a copy but it won't upsize because of referential integrity loops and it also truncated memo fields to 255 characters so won't work without redesign.

2) Replication. Give the other site a back-end replica and synchronise. Don't know anyone who's physically done this but views from the net imply it's not good with reconciling conflicting updates and seems prone to unrepairable corruptions.

I did try this on a copy but it doesn't like one of the tables (DK why yet...) so won't work anyway without redesign.

I guess the third doesn't help in your situation:

3) Use Citrix so they access through a front-end located physically at our site. Avoids the issues of the other two approaches but does incur licence fees per user so may be ruled out if prohibitively costly.

Haven't tried this yet...

I'd be grateful for any views/experiences.

Cheers

RSGB