dcount

[igg via AccessMonster.com]

PatientTable contains thousands of patient records. subform current record
selection should for a given clinic ID, update the parent totalpatients text
box with total number of patients in good health. But the
update is slow. like to speed it up so that a current record selection,
updates the text box almost immediately.

Private Sub Form_Current()
Me.Parent.totalpatients = DCount("patient", "PatientTable", "patient='Good'
And ClinicID='" & Me.Parent.Clinic_ID & "'")
End Sub

 

[Stefan Hoffmann]

hi,

update is slow. like to speed it up so that a current record selection,
updates the text box almost immediately.

Private Sub Form_Current()
Me.Parent.totalpatients = DCount("patient", "PatientTable", "patient='Good'
And ClinicID='" & Me.Parent.Clinic_ID & "'")
End Sub

The most important step:
Create a combined index in your table [PatientTable] and add the two
fields [Patient] and [ClinicID] (in this order).

Is your parent form bound or unbound? Does Me.Parent.Clinic_ID reference
a bound field or an unbound control?


mfG
--> stefan <--

 

[igg via AccessMonster.com]

parent form is unbound. And, Me.Parent.Clinic_ID is also unbound.

hi,

update is slow. like to speed it up so that a current record selection,
updates the text box almost immediately.

[quoted text clipped - 3 lines]

And ClinicID='" & Me.Parent.Clinic_ID & "'")
End Sub

The most important step:
Create a combined index in your table [PatientTable] and add the two
fields [Patient] and [ClinicID] (in this order).

Is your parent form bound or unbound? Does Me.Parent.Clinic_ID reference
a bound field or an unbound control?

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

The most important step:
Create a combined index in your table [PatientTable] and add the two
fields [Patient] and [ClinicID] (in this order).
Is your parent form bound or unbound? Does Me.Parent.Clinic_ID reference
a bound field or an unbound control?

Okay. Adding the index did work?



mfG
--> stefan <--

 

[igg via AccessMonster.com]

original remote table is being used and I can not modify it. i copied the
original database to a different remote folder with no user connected to it
and noticed significant improvement in speed. By adding a combined index you
do not mean adding a new column to the table, right?. just go to design view
and create an combined index of the two, right?.

hi,

The most important step:
Create a combined index in your table [PatientTable] and add the two
fields [Patient] and [ClinicID] (in this order).
Is your parent form bound or unbound? Does Me.Parent.Clinic_ID reference
a bound field or an unbound control?

Okay. Adding the index did work?

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

original remote table is being used and I can not modify it. i copied the
original database to a different remote folder with no user connected to it
and noticed significant improvement in speed.

This should only be the case if the new remote location is on another
server/drive.

But there is another optimization you may try: A permanent recordset.

Create a form with this events filled:

Option Compare Database
Option Explicit

Private m_Recordset As DAO.Recordset

Private Sub Form_Load()

Set m_Recordset = CurrentDb.OpenRecordset( _
"anyRemoteTable", dbOpenDynamic)

End Sub

Private Sub Form_Close()

m_Recordset.Close
Set m_Recordset = Nothing

End Sub

ACE/Jet will create the lock file (.ldb) once. So this saves file I/O.

By adding a combined index you
do not mean adding a new column to the table, right?. just go to design view
and create an combined index of the two, right?.

Yes. No new columns, only the index.


mfG
--> stefan <--

 

[igg via AccessMonster.com]

your first suggested optimization to create combo index worked very well. for
the second optimization that you have suggested, just use the form already i
am using or should i create a new form?. Just making sure. these should get
added to the form i use to do queries, right?. Thanks.

 

[Stefan Hoffmann]

hi,

your first suggested optimization to create combo index worked very well. for
the second optimization that you have suggested, just use the form already i
am using or should i create a new form?. Just making sure. these should get
added to the form i use to do queries, right?. Thanks.

No, explicitly a new form.

Open it as soon as possible after your application has started using
WindowMode := acHidden, so that the users doesn't see it.

mfG
--> stefan <--

 

[igg via AccessMonster.com]

is it possible to have the main form to start this new form and close when it
exits?. if yes, how?.

 

[Stefan Hoffmann]

hi,

is it possible to have the main form to start this new form and close when it
exits?. if yes, how?.

Use the main forms Load event, e.g.:

Private Sub Form_Load()

DoCmd.OpenForm FormName := "formWithPermanentRecordset", _
WindowMode := acHidden

End Sub


mfG
--> stefan <--