Access Backend Performance Issues

[stephenmbell]

I am not even sure if this is the place to post this, but I will give it a shot.

I have an application that runs in msaccess. There is a backend db that resides on the server and a front end db that resides on the clients machine (has all of the forms / reports as well as linked tables from the backend access db)

I am running into performance issues with certain aspects of this and I am wondering if I were to put the backend tables in a MS SQL Server, and link the tables in the front end to this, if my performance would improve.

Any input is appreciated.

Thanks

sb

 

[genomon]

The "Access - Other Topics" might be a better place.
Anyway, latency on the network is likely the cause of your performance issues. If you can migrate the database to SQL Server I am almost certain you wouls see an increase in performance, especially if it's a large RDB. You don't say what the backend DB is, though...

"Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes."

 

[pbaldy]

You may also find some help here:

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

Paul
MS Access MVP 2007/2008

 

[stephenmbell]

The backend is also an access mdb

 

[JoeAtWork]

I usually don't see much speed increases by simply moving the backend to SQL Server and leaving it at that. And in fact if you have very complex and nested queries, things can actually get a lot slower.

However, in moving to SQL Server there are almost always opportunities for huge increases in efficiencies if I find the main bottlenecks and move the workload on to the server side, usually using stored procedures.

 

[Ed2020]

I think the advice to move to SQL Server is premature at the moment.

In addition to Paul's advice I'd recommend following this:

http://support.microsoft.com/kb/261000

I have got the biggest imrpovements from maintaining a persistent connection to the backend.

Obviously the usual advice on field indexing, number of users etc applies.

In order to assess the need to migrate to SQL Server properly we'd need more information on the database - data volumes, user numbers, usage, quality of current design...

Ed Metcalfe.

Please do not feed the trolls.....