Speeding up databases

[Vel.]

Hello,

Any chance I could get some basic tips on speeding up a
database over a network. In general, it works alright,
but some of my reports run particularly slow. Also, a
form used for posting payments is very bogged down due to
a combo box. The form in question uses txtClientID to
enter a client number, then txtServiceDate to enter a
date of service, then there is a combo box that is based
on a query to display only those activities the client in
question had on the date in question. The problem is
that tblActivity is a very large table, and it takes
quite some time to load the form because of this. Is
there a way to "populate" that combo box with the
requested values without the extreme lag?

Vel.

 

[Lance]

Hi Vel,

For the combo box make sure you have indexed the client
and date fields and any other fields you are using to join
your tables or use in the where clause.

Lance

 

[Vel]

Lance,

Adding an index to the date field made a very minor
improvement, but the form is acting quite strangely. It
takes 10-20 seconds to move from form view to design view
and 30+ to move from design view to form view or to save
it. I don't understand how saving the form should have
anything to do with a combo box on the form. I think
maybe I'll try to make the form from scratch to see if
something else is going on...

Vel

p.s. Any other general tips about keeping networked
Access databases moving along at a reasonable pace are
still appreciated.

 

[Bruce M. Thompson]

Any chance I could get some basic tips on speeding up a

database over a network. In general, it works alright,
but some of my reports run particularly slow. Also, a
form used for posting payments is very bogged down due to
a combo box. The form in question uses txtClientID to
enter a client number, then txtServiceDate to enter a
date of service, then there is a combo box that is based
on a query to display only those activities the client in
question had on the date in question. The problem is
that tblActivity is a very large table, and it takes
quite some time to load the form because of this. Is
there a way to "populate" that combo box with the
requested values without the extreme lag?

See the following page at Tony Toews' web site for some suggestions:

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

 

[Joe Fallon]

Here is one idea:
Leave the rowsource of each cbo blank.
Then use the On Enter event of the cbo to load it at the last possible
second.

Note the variable lngCount is Dimmed at the module level as an integer.
This is a neat trick to allow full use of the scroll box. By "counting" to
the last row, you can drag the scroll box up and down the bar and the
records move. This is not the default behavior. It only works once you have
scrolled to the last record. Rather annoying for power users who don't mind
grabbing the box with their mouse.

Also, an hourglass is used to let the user know it may take a couple of
seconds to get a response.
Then the list is dropped down "automatically".

Private Sub cboData_Enter()
On Error GoTo Err_cboData_Enter

DoCmd.Hourglass True
Me![cboData].RowSource = "cboDataQry"
lngCount = Me![cboData].ListCount
Me![cboData].Dropdown
DoCmd.Hourglass False

Exit_cboData_Enter:
Exit Sub

Err_cboData_Enter:
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source &
vbCrLf & Err.Description, , "FormName - cboData_Enter"
Resume Exit_cboData_Enter

End Sub