Split Database but now access is slow HELP!

[Tulip]

Hi there,

I have just split my Access 2000 DB using the wizard and copied the frontend onto each users local folder (14 users). I am now finding though that opening up the tables and queries from the local copy can be quite slow (15-20 seconds sometimes. Does anybody have any suggestions please?
I had already tested the splitting myself and had had no delays but now, when more people are using it, it is a problem.

Thanks in advance,

Tulip.

 

[Lozere]

Please give further details:

What is the network you rely on?
What are the size and number of fields of your tables?

Do you have correctly optimize your indexes and relationships? Ask the performance Wizard.

On a LAN a few more seconds could come from such a distribution but not a handfull of.

 

[njitter]

Hello,

i found out the same thing. I had the idea of splitting my database and just give people the front-end. In theory this should speed up the database (only the data is transferred through the network, not the interface) but instead it slowed down.

I still haven't found a reason for it but i went back to the old situation.

Martijn

---
There are no stupid Questions -- just stupid People

 

[Tulip]

It's an NT 4 network, with W2k workstations.Total size of backend DB is 1576KB, with 20 tables. The 2 main tables have around 1000 records each with 15-20 fields.
A while back on an access course, I had someone check out the relationships and tables and they said they were ok. I will make a copy though and run the analyse wizard and see what it says.

The main reason I split the DB was because at least twice a day it was crashing and I had to run a compact and repair which meant getting all users to log out of it.

If you need more info, please ask.

Thanks,

Tulip

 

[njitter]

Access is not really meant to be used in a multi-user environment. The problems with the database constantly crashing is probably related to that.

You should consider to 'Supersize' it to some SQL-server platform.

Martijn
---
There are no stupid Questions -- just stupid People

 

[mikehoot]

Is you network stable or do you have problems with lost connections?

If an access BE is open across the network when a connection is lost then you can corrupt your db.

When you say your db was crashing, was this the FE or BE?

 

[Tulip]

The network is stable, only installed 6 months ago from scratch. I only split it today to try and resolve the crashing issue. It hasn't crashed yet since the split but if I can't get the access to speed up I may have to go back to the old way.
I think the reason it was crashing was incorrect shut down, ie users didn't close out of a table before closing out of access.

Any ideas?

Thanks again

 

[CID8]

Someone earlier suggested upsizing to SQL Server, this is one of the best ways to solve the performance issues. Access and SQL work in opposite ways. Access is technically a front end tool, when you send a request from the front end for particular data to the back end, access sends the whole table and the front ends pc processor's the result. This results in substantial network traffic. SQL on the other hand processors the request and just sends the results to the user - a lot less traffic.

With your current combined version are the users accessing a single networked database? When using this method each users pc will create a temporary copy on the local pc so affectively using a local version, which should be quick. Access will then replicate back to the shared version after updates.

I've found that access really struggles to cope with more than 10 concurrent users. Does your db use Incremented AutoNumbers? When converting to a replicated db all AutoNumbers are changed to "Random"? This apparantley works much better in multiuser environments as due to less internal conflicts when trying to generate the AutoNumbers, this might explain your crashes.

Hope this helps

CID8

 

[jjgraf]

i agree with njitter and CID8 Access is not desinged to be in 2 tier application. go get SQL server.