D
dmu
Hi,
Can someone point me to some good books/references specifically on improving
performance for adps and/or on form design strategies? We're using SQL
Server 2000 and Access 2000 adps made into ades, Windows 2000 network. We
now have several sites across the country accessing the SQL Server and are
starting to have large amounts of data, so are starting to see performance
problems.
From searching access newsgroups I know the main thing is to limit the data
coming in from the server to the ade, but I'm unclear on how differing
methods pull data and on where filtering takes place. Some of the main
questions I'm starting with are:
1) How do the following methods differ: setting a form's recordsource to a
select statement (which may get complex) vs setting the recordsource to a
view on the SQL server. I really prefer setting the recordsource myself
rather than maintaining a lot of views, but I don't know how much of a hit
this is.
2) Where does filtering taking place, or how do I control where filtering
takes place? I'm unclear if opening a form with DoCmd.OpenForm
"frmComplaint", , , "[ComplaintID] = " & Me.lstName, , acDialog limits the
actual records pulled from the server or pulls all the records and just
limits the display. Same with reports. Does opening a report with
DoCmd.OpenReport strReport, acViewPreview, , "[ComplaintID] = " & Me.lstName
limit the records pulled from the server?
3) How does loading combo and list boxes affect things? On some forms I
have several combo boxes with Select statements (some from other tables than
the form is based on, some from the same table that the form is based on) as
their Row sources. I often use these to limit acceptable data and set Limit
to List to Yes.
4) How do subforms affect things? I have some subforms I created that get
reused in multiple forms, though in a few cases the subform is based on the
same table as the parent form. I like the convenience of only having to
update a subform once instead of hunting down all the occurrences of that
group of data but maybe that's not the right answer. Does the Master/Child
link mean that only that record is pulled, or does it pull all records and
limit only the display? How does performance differ between putting a form
in as a subform vs opening it with DoCmd.OpenForm.
5) I've also seen mention of setting forms properties to data entry. Does
opening a form with DoCmd.OpenForm "frmComplaint", , , , acFormAdd, acDialog
do the same thing? Or should I set the form property to data entry via VBA
on form open or load? I may use the same form for displaying data (locking
it) as well as for data input.
Thanks!
-dmu
Can someone point me to some good books/references specifically on improving
performance for adps and/or on form design strategies? We're using SQL
Server 2000 and Access 2000 adps made into ades, Windows 2000 network. We
now have several sites across the country accessing the SQL Server and are
starting to have large amounts of data, so are starting to see performance
problems.
From searching access newsgroups I know the main thing is to limit the data
coming in from the server to the ade, but I'm unclear on how differing
methods pull data and on where filtering takes place. Some of the main
questions I'm starting with are:
1) How do the following methods differ: setting a form's recordsource to a
select statement (which may get complex) vs setting the recordsource to a
view on the SQL server. I really prefer setting the recordsource myself
rather than maintaining a lot of views, but I don't know how much of a hit
this is.
2) Where does filtering taking place, or how do I control where filtering
takes place? I'm unclear if opening a form with DoCmd.OpenForm
"frmComplaint", , , "[ComplaintID] = " & Me.lstName, , acDialog limits the
actual records pulled from the server or pulls all the records and just
limits the display. Same with reports. Does opening a report with
DoCmd.OpenReport strReport, acViewPreview, , "[ComplaintID] = " & Me.lstName
limit the records pulled from the server?
3) How does loading combo and list boxes affect things? On some forms I
have several combo boxes with Select statements (some from other tables than
the form is based on, some from the same table that the form is based on) as
their Row sources. I often use these to limit acceptable data and set Limit
to List to Yes.
4) How do subforms affect things? I have some subforms I created that get
reused in multiple forms, though in a few cases the subform is based on the
same table as the parent form. I like the convenience of only having to
update a subform once instead of hunting down all the occurrences of that
group of data but maybe that's not the right answer. Does the Master/Child
link mean that only that record is pulled, or does it pull all records and
limit only the display? How does performance differ between putting a form
in as a subform vs opening it with DoCmd.OpenForm.
5) I've also seen mention of setting forms properties to data entry. Does
opening a form with DoCmd.OpenForm "frmComplaint", , , , acFormAdd, acDialog
do the same thing? Or should I set the form property to data entry via VBA
on form open or load? I may use the same form for displaying data (locking
it) as well as for data input.
Thanks!
-dmu