DB runs slow over network

[qwertyjjj]

I have a 16Mb DB. The intention is for users to access this over the network without copying onto their local machines.

At present, many of the forms are located on 1 master form and they are all subforms on tabs. There are a lot of calculated control boxes on the forms, and these seem to be very slow to bring back the data over the network whereas on my local machine, the data is displayed in a couple of seconds.

COuld this be sped up somehow? Maybe using queries or other methods?

 

[RuralGuy]

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

 

[qwertyjjj]

Thanks, I'll have a look through that.
It's not just that though, I have the DB set to compact every time someone closes it and this is taking ages, whereas on my local machine it takes a few seconds.

I have to compact it otherwise, the DB will grow too big.

ANy thoughts ?

 

[qwertyjjj]

Do you think all the control boxes could be causing the problem or is it more likely to be one of the issues on that link ?

 

[RuralGuy]

Split your Access database into data and application

 

[qwertyjjj]

It's still very slow. I think each of the calculated control boxes must make a call to the DB to retrieve it's information. It then moves onto the next control box and requeries the DB again.
I'm sure it has to do with this because I have a subform, which only gets data from a SQL query and that is loaded immediately.

Do you think this could be it, data being transferred back and forth across the network causing problems ?

 

[Banana]

Another option is if you have it, to use a Terminal Server. I'd still have it split if you want to it to be used among several users, but it has the benefits of keeping DB entirely on the server and doing all processes on the server, and merely sending the screen image to user and accepting users' input.

 

[qwertyjjj]

I think using terminal server will be beyond most of the users.

I think moving everything into queries will help but not sure....

Any ideas?

 

[Banana]

Yeah, if you have large number of records, then using queries on all of your form to filter to only records that are relatively "current" (say within 90 days or whatever), then it'd reduce the work.

However, if *even* that doesn't help, then you might wanna to look at moving to SQL for the backend to help and reduce network traffic.

 

[GMLWORLDCHAMP!]

"qwertyjjj: Thanks, I'll have a look through that. It's not just that though, I have the DB set to compact every time someone closes it and this is taking ages, whereas on my local machine it takes a few seconds.

I have to compact it otherwise, the DB will grow too big. ANy thoughts ?"

BE CAREFUL...a networked database split or not can be a very touchy application at best...First thing, get rid of the ability for any user to Compact/Repair the system when they exit because among other things, this can and most likely has caused repetitive table record corruption...look in all primary tables first and see if you find special characters like the white box; that's Access's way of saying right at that point in trying to read that table, this record has become corrupted!

If that table is key in relationships and features an Autonumber field, your database will suffer damage because it can't understand what this symbol is, so, it crashes in response!

Again, any network shared database is always best compacted at its host PC...can it be compacted from a remote system or networked user, sure but at risk of cumulative corruption, guaranteed!

The reason it takes so long is the relay from the network as well as, most importantly, if other users are in the system, compacting it will absolutely adversely affect the database if it's even able to complete this process at all and again, look at your tables because I would guess there are problems that need your immediate attention before it is run again.

A former split database of mine suffered this consequence numerous times because despite my efforts to let people know that I needed everyone to exit the application as I worked on changes to the main system, someone would enter the application (just for a moment...) and crash away the primary tables...but that's people fault, not application fault so steer away from making these situations even available to your users, making your work life much smoother!

 

[The_Doc_Man]

Read your Access agreement very carefully. Using Terminal Services possibly could be a violation depending on how someone chooses to interpret the fine print. Access is a SINGLE USER product designed so that everyone has a copy thereof. (Bill, after all, wants to make exorbitant profits ...) Using terminal services such that a single copy of Access is used by several users simultaneously could well violate the terms of your agreement.

OK, it is unlikely that MS will come sniffing around... but if you work for a government agency at ANY level - national or smaller scope - you might well have an explicit policy that says you NEVER EVER violate a licensing agreement on penalty of getting fired or getting in other trouble. Be aware of the applicable policies before you apply Terminal Services to this problem.

 

[qwertyjjj]

Hi
I've checked all the tables and there doesn't seem to be a problem with any corruption.
ALso to make matters worse, I can't ask the users to compact this DB by themselves as they either do not know how to or will forget. I would therefore need to do this through code somehow, which as far as I understand is not possible.

There ust be some reason for it running slowly over the network other than this - maybe an infrastructure related thing?
Everything is in queries so when splitting the tables all the work should be done at the back end and then data is just transferred over the network isn't it?

 

[boblarson]

Actually, you can compact via code very easily. However, the caveat to this is that it only will work if there are no other people in the database at the time.

Put this code on a button (I've used this in Access 2.0, 95, 97, 2000, 2002, and 2003).

        DoCmd.Close acForm, "frmYourFormNameHere", acSaveNo
        SendKeys "%(TDC)", False

change the form name to whatever form you have open. When compacting it will require you to close any open objects, so if you have any other open forms, you may want to modify the above code to:

        Dim frm As Form
        For each frm in Forms
            DoCmd.Close acForm, frm.Name, acSaveNo
        Next frm
        SendKeys "%(TDC)", False

I usually don't like using SendKeys. In fact I don't ever use it other than in this piece of code. It has worked like a charm for me for years and years.