Dear all,
while testing the Janus Gridex Control Gridex2000 within Access 2003
(works smoothly) I was testing an unbound form a là "master/detail"
fashion.
You can make some very nice screens and "grids" of data using the built-in
controls. Take a look at the following continues forms
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm
So, I think that continues forms, and using the "native" ability of
ms-access is the way to go.
That is I was wondering what is the fastest way to retreive data and fill
in master & detail data, also questioning how Access does the job, when
form and subform are bound tightly.
The above job is really much the same as if you built the sql string, and
hand coded it.
First of all I was wondering if there's any benefit performing recordset
cloning, in terms of speed and efficiency, or if there's any speedy way to
preload data
No, not really. For some combo boxes, and some things, sure pre loading of
some stuff makes sense, but then what do you pre-load? Are you taking about
a adp proeject, or talking about a mdb with linked tables to sql server? If
you are using linked tables, then you do need to use "views" to get good
perfoamdcne for sorted combo boxes, and especially if that combo has values
from more then one table.
Rookie (me) would perform the task in this way (pseudocode):
1) Load recordset into the "master" (about 2000 records out 4000,
increasing 2000 per year)
No, why load 2000 records? If 10 users start the application, you just
loaded 20,000 records of network bandwidth, and server load. What is worse,
you done NO useful work yet!! Can you imagine if a instant bank teller
machine loading ALL accounts, and THEN asked you for your account number?
So, my point is that pre-loading needs to be used with caution, as you are
doing work, and causing network loads without figure out what you want to do
in the first place.
There is absolute no need to load 1000's of records and THEN ask the user
what they want to do. Why not prompt the person for the one record they
need? Load it...and let ms-access do the work.
2) Load recordset into "detail" given the proper ID in the WHERE clause
in the underlying query (0 to 20 records out of 10000, increasing 5000 per
year)
No need to dot the above. The child records are simply a query, and if you
got 20 records, then only 20 records are transferred. I see no reason why
building your own recordset would save anything (but would of course use up
valuable and expensive people ad developer time).
If you load ONE master record to the form, then the sub-form will
automatically load its child records. 20 reword is absolute NOTHING to worry
about from a performance point of view. Writing a whole bunch of code to
load those 20 record is a waste of expensive developer time, and yields
little, if any benefit.
3) On keyup or click in "master", check if row changed, if this is the
case perform step 2.
As I Mentone, really no benefit.
The key piece of information here is to NEVER EVER just willy nilly load up
a main form with NO restrictions on the records. It makes no sense to
pre-load up a instant teller machine, and one of the worst things you can do
is just blindly open up a main form attached to a large table with FIRST
asking what record to load.
in a simple approach to load a customer you could go
do
strInvoice = inputbox("enter customer invoice to edit")
if strInvoice = "" then
exit do
end if
docmd.OpenForm "frmCustomer",,,"invoice = " & strInvoice
loop
The above use of the "where" clause will restrict that bound form to ONE
record. This even applies when you don't use sql server, and are using a
office LAN, an sharing the data. If you do the above (even without using sql
server), then the 10 users on a typical office network will have absolute
screaming performance in terms of record load and retrieval time. With very
small tables in the 50,000 to 150,000 record range, response time will be
absolute instant, as the main form is only going to pull one record across
the network. if you got a table with 20, or 200,000 records, the load time
will be SAME!, and we not even using sql server yet!!).
This same approach needs to be applied to sql server, and you use the
"where" clause to restrict what the form loads. So there is no need to every
load up a form that is just blindl attached to a large table. Why just open
up a form attached without any regards to what it loads? (this is just us
lazy developers not thinking!!).
Here another example of a making a good search form that performs very well
with JET, or sql server based applications:
http://www.members.shaw.ca/AlbertKallal/Search/index.html
