How can I improve concurrent usage?

[Guest]

We've recently implemented a new access database. Its not a big database its
about 6Mb. But we seem to have difficulty accessing it on times. One user
in particular has alot of trouble. We are still loading historical data
however.

Is there anything I can do to improve this??

 

[Alex White MCDBA MCSE]

Hi,

is it a single file mdb? if it is consider spliting the database into
front-end and back-end with the front-end copied onto the workstations so
that the only bit of access on the server is the back-end (data only).

what version of access is it?

have you got all the right sp's for office/access and windows installed on
all the computers? http://office.microsoft.com
http://windowsupdate.microsoft.com

do a compact and repair on the database.

 

[Guest]

Hi its Access 2003 it is a single file. The most people that will ever use
the database concurrently is 10 and that is absolutely max. Maybe 3 or 4
will be the average use.

Would spliting it make much of a difference?

NoviceIAN

 

[Alex White MCDBA MCSE]

YES.... I would never try to run more than one user in a front-end and your
single file means that you are running more than one at some points. In my
experience it is not the volume of data that access has problems with it is
the number of users that will cause more problems, there is a database
splitter under tools,database utilities.

If you have any more queries post back here

 

[Joseph Meehan]

Hi its Access 2003 it is a single file. The most people that will
ever use the database concurrently is 10 and that is absolutely max.
Maybe 3 or 4 will be the average use.

Would spliting it make much of a difference?

NoviceIAN

It will make a very big difference. Once split only the data will be
accessed and all the other functions like display sorting etc will take
place on the user's computer freeing up the LAN and the server for what it
needs to do for the other users.

Make a copy and use the splitter wizard to do the job, tweaking if
needed. Look under the Tools Database Utilities menu for the splitter.

 

[Guest]

I've never needed to do this before how complicated is it? Is there any
chance of data loss or corruption? If it remains the way it is what are the
potential problems?

 

[Alex White MCDBA MCSE]

Hi,

I would suggest completly backing up your single database, copy it to 2
different locations, then open the database holding down the left shift
button, then go to tools,database utils, database splitter, click on split
database, the next dialog is where to put the backend, (on your server), if
you put the right location for the back-end life will be easier, at the end
of the process the back-end will be on the server and the original file can
be copied to each workstation.

No chance of data loss as you have copies of the original (before splitting
mdb).

Seriously this does make a difference, as access is only multi-user within
the data, not forms/modules/queries etc, the idea of allowing each person to
run the same instance of the front-end is unstable to say the least. Good
luck and post back here if you have any problems.