Best Way To Access Database On Server, Access 2003

[adi2011]

Hello,

I have a large Access db (300 MB at least) which is located on server. It is called patients.mdb and it is a Access 2003 version. This db is used by 4 PCs:

1. PC i - Win XP - Office 2007
2. PC ii - Win XP - Office 2003
3. PC iii - Win XP - Office 2003
4. PC iiii - Win XP - Office 2003

On all these PCs there is one simple shortcut which opens patients.mdb on server.

This is the *current* condition of my db:

- it works very slow,
- it is huge and it takes a lot of space (300 MB),
- some users can't open db from shortcut from desktop, instead they have to open Access and than open db from shortcut.

This is what I wand in *future* for my db:

- to work much faster,
- to avoid error caused from opening db using Office 2003 and Office 2007 from two or more PCs,
- to decrease size of db,
- to avoid craching db and similar.

My question is how can I realize my *future* requests and what are your recommendations based on your experience.

Any help is appreciated and many thanks in advance for prompt reply!
Adi

 

[pbaldy]

The db should be split for starters, which may solve all the problems.

http://www.granite.ab.ca/access/splitapp/index.htm

 

[adi2011]

Thank you very much for your suggestion.

I have made changes based on your suggestions and here are some important updates:

- I have split database to FE and BE,
- I have made copy of every FE on 10 user PCs (yes I had to install it on extra 6 PCs) and I have placed BE on server.

These are the results:

- Users are complaining still on slow speed specially when they make some Search ot run Queries,
- On some PCs I *had* to remove copy of 'local FE' and put direct shortcut to 'server FE' because it worked faster.

I am not user if I am right but it looks like that my server is full and lacking of space and memory because I have done everything suggested but no result at all unfortunately;(

I plan to put this BE part of database on brand new server and then to check if that helps..

If you can give me suggestions or warn me if I made something whrong, please do not hesitate to write.

Thanks,
Adi

 

[pbaldy]

The first item here will often fix speed issues, plus the others are worth looking at:

http://www.granite.ab.ca/access/performancefaq.htm

I would also check that you aren't opening forms based on entire tables, which causes the entire table to have to be loaded to the FE. I just split a db for a client and their performance improved, plus it got rid of an intermittent corruption problem they had.