Configuring Access for multiple user access across a workgroup net

V

vanGogh

I developed an Access 2003 application, using forms, macros and VBA code.
The users that I developed this application for are running on Windows XP
peer-to-peer network. I installed the application on one of the users
machines.

On this machine, I opened up a directory to other users on the workgroup by
sharing the specific folder where I installed the Access database files.
Next, I created a shortcut on each of the other user's machines so that they
would be able to access the database from this shared network folder.

The Access database now contains more than 5MB of data.

The problems I am running into are:

1) When other users try to access the application, they are getting locked out
2) Often, the Access application freezes and becomes locked and none of the
users are able to access any forms or data
3) Sometimes the appllication crashes due to a number of people trying to
access data or forms at the same time

Is there a better way to configure the application for multiple users to
access the application?

As I move forward, Are there any suggestions as to how I can resolve some of
these locking issues?

Have we outgrown the ACCESS based solution? Should we be moving to something
like SQL Server with a web front end?

Any comments or suggestions would be greatly appreciated.

Thanks
 
M

Morris

vanGogh said:
I developed an Access 2003 application, using forms, macros and VBA code.
The users that I developed this application for are running on Windows XP
peer-to-peer network. I installed the application on one of the users
machines.

On this machine, I opened up a directory to other users on the workgroup
by
sharing the specific folder where I installed the Access database files.
Next, I created a shortcut on each of the other user's machines so that
they
would be able to access the database from this shared network folder.

The Access database now contains more than 5MB of data.

The problems I am running into are:

1) When other users try to access the application, they are getting locked
out
2) Often, the Access application freezes and becomes locked and none of
the
users are able to access any forms or data
3) Sometimes the appllication crashes due to a number of people trying to
access data or forms at the same time

Is there a better way to configure the application for multiple users to
access the application?

As I move forward, Are there any suggestions as to how I can resolve some
of
these locking issues?

Have we outgrown the ACCESS based solution? Should we be moving to
something
like SQL Server with a web front end?

Any comments or suggestions would be greatly appreciated.

Thanks
Click to expand...

A lot depends on the number of users that you need to service.

An intermediate solution could be to use an Access backend and web front
end. You can configure IIS on one of the XP machines so long as it's XP
Pro, and use that as the server for the network. Your network traffic will
be greatly reduced. Once you get rid of all the forms and code modules,
your db size will reduce too.

An Access database driven website can comfortably handle 10,000 page
requests a day. I know this, because I run several, and our internal
contact management system is built the same way, with about 10 users.

Morris
 
A

Albert D. Kallal

Access 2003 is very reliable on a network if you set things up correctly.

If you don't set things up, then you will be the brunt end of people saying
"I told you so...ms-access is no good!!"/

I have applications with about 50 to 60 highly related tables, and small
data sets of 75,000 records. This system on a cheap office network has run
for 5 years, and have 5 users all day. While the tables are small, and only
in the 75,000 to 100,000 record range, and I there is only about 5-6 users
working with the data ALL DAY long at the SAME TIME, I have not had ONE
SERVICE call from these folks in about 4 years. Of chouse, with such small
tables, response time is near instant for most all of the forms.

So, all you need here is to have a proper setup, and ms-access will give you
years of trouble free operation.

So, what you need to do:

You must split you database, and place the "front end" part on EACH
computer. Further, you should as a good habit put a mde on each workstation.
Is there a better way to configure the application for multiple users to
access the application?
Click to expand...

Yes, as metnoned, split your database (tools->database utilies->).

Spend a few minutes reading about splitting here:

http://www.granite.ab.ca/access/splitapp.htm
Have we outgrown the ACCESS based solution? Should we be moving to
something
like SQL Server with a web front end?
Click to expand...

You don't mention how many users you have at the same time. You also don't
mention how many reorders you have in the larger tables.

As I mentioned at the start, if you only got 5, or 6 users, and little
tables of only 75,000 to 100,000 records, then ms-access will barely be
breaking up a sweat.

Any comments or suggestions would be greatly appreciated.
Click to expand...

Just make sure you have a proper setup, and you will be rewarded with good
performance, and good reliability. Often, much of this stuff you just have
to learn. For example, a mechanic can tell you it is a good idea to change
the oil in your car..but if you don't change the oil, the next day the
wheels don't fall off. However, over time...that advice is still good!

Another great check list of things concerning performance you need to look
at can be found here:
http://www.granite.ab.ca/access/performancefaq.htm

I mention the above, since often splitting the database can introduce some
performance problems, but if you follow the above list, then you get all of
the performance back....and all will be well again...
 
Top