Speedup access 2003

[raffie77]

Hello people,

I'm making a access database for the company that I work for.
and it wil be used on a few hundred computers.
I'm affraid that Access can't handle the speed and it wil become slow.

is it a good idea to put the data on a SQL server and link the access database to SQL and use the forms and reports from access to display the linked records?

does that make the system faster?


Ralph

[boblarson]

Using SQL Server as the backend in itself does not necessarily make the system faster. So, the big question is how much data you are needing to store, if the database will be split (which it should) and will each user have a copy of the frontend on their machine (which it needs to be).

And when you say, "I'm affraid (sic) that Access can't handle the speed and it will become slow" what exactly do you mean? What speed is it needing? What type of data are you storing? What type of interfacing do the users need? What are they doing in this database?

[raffie77]

hi boblarson

let me give some more details

it's not a split database
the users are working in the database adding records, its a database for a call center to make appointments for calling back people.
when I open a callback form the speed or performance is ok, not fast but fair enough, right now there are I think 50 people working on it, not all the time, but at the end there will be I think about 300 users. if the speed or performance is ok now for these few people how will it be when 300 users are going to use it.

and isn't SQL not a better database technique than access?

and what's the benefit of splitting a database?


Ralph

[boblarson]

First off, I'm surprised that your database hasn't corrupted yet. It MUST be split for multi-user use in order to avoid corruption and to help with speed issues as well as being able to make modifications to the frontends.

Also, if the database is not split, the limit is officially 255 users but practically it is maybe 10 users. Also, if the database corrupts, you would be at risk of losing ALL of your data AND ALL USERS would be unable to do their work. If you have a split database, the backend holds just the data and is less likely to corrupt. The frontend, can still corrupt but it would likely be for only one user and so everyone else could just continue on without a problem. You would then give the user with the corrupt frontend a new copy and they would go on with work. Not splitting is like playing Russian Roulette with your whole database. If someone is in there and a network disruption occurs for ONE of the connected users, that can corrupt the entire thing.

So, I hope you have FREQUENT backups because if it goes corrupt then you would need to restore a backup and then RE-ENTER all of the data after that backup point.

And SQL Server as a backend is a good way to ensure that you have less likelihood that you would lose data. But you still need to have SEPARATE COPIES of the frontend for each user.

[raffie77]

ok I'll give it a go.
I'll split the database using the wizard in access

so it's tables only?
isn't it faster to put the querys and relations in the backend?
I can imagine that the calculations of relations en query's, that it's faster to do in the backend then over the network

[boblarson]

Relationships will be defined in the backend. The tables go in the backend the queries should be in the frontend.

And you wrote:

I can imagine that the calculations of relations en query's, that it's faster to do in the backend then over the network

well, if Access were a server then yes that would be the case. But it isn't. It is a file based system and the frontend is going to be processing everything on the user's machine, regardless of whether it is split or not or if the queries are in the backend or on the frontend. If they are in the backend then the objects still need to be transferred over the network for the frontend to run. So, no - having them in the backend is not preferred.

The relationships can ONLY be set in the backend. That will maintain referential integrity of your data. Setting them in the frontend is pointless really.

[raffie77]

Relationships will be defined in the backend. The tables go in the backend the queries should be in the frontend.

And you wrote:

well, if Access were a server then yes that would be the case. But it isn't. It is a file based system and the frontend is going to be processing everything on the user's machine, regardless of whether it is split or not or if the queries are in the backend or on the frontend. If they are in the backend then the objects still need to be transferred over the network for the frontend to run. So, no - having them in the backend is not preferred.

The relationships can ONLY be set in the backend. That will maintain referential integrity of your data. Setting them in the frontend is pointless really.

ok thanks I'll give it a try

i'll be here before anyone logs in to split the database
thanks a lot for your time
I appreciate that

Regards,

Ralph

[joy9393]

Also, if the database is not split, the limit is officially 255 users but practically it is maybe 10 users. Also, if the database corrupts, you would be at risk of losing ALL of your data AND ALL USERS would be unable to do their work. If you have a split database, the backend holds just the data and is less likely to corrupt. The frontend, can still corrupt but it would likely be for only one user and so everyone else could just continue on without a problem. You would then give the user with the corrupt frontend a new copy and they would go on with work. Not splitting is like playing Russian Roulette with your whole database. If someone is in there and a network disruption occurs for ONE of the connected users, that can corrupt the entire thing.

__________________
Mohsin