tim johnson said:
I intend to convert a db I bulit by from bound to unbound
forms. Presently all forms are bound.
This is the first time I am doing this since it was
always easiest to use bound forms.
I have a few questions for those who are experienced with
this
1. How much of an improved perfornamce can I expect and
under what conditions?
I never seen any real performance difference. I mean, you have to load up a
reocrdset, then you have write code to put that recordset data to the form,
and then you write a bunch of code to take the form data, and then write the
data back to a recordset (and, if you close the reocrdset...you take a huge
performance hit). How on earth will thus huge hunk of code you now have
speed things up? If anything..it will slow things down!
I don't see any reason why a bound form is a performance problem? I mean, if
the table is small, say only 50,000 or 100,000 records, then I don't see why
using a bound form is a problem? Of course, I sure you NEVER just be a lazy
person and open up a form attached to large table without a where
clause...right? I mean, if you need to edit a invoice, you prompt the user
for the invoice number, and then load the form using the "where" clause to
that ONE record right? I mean, it would be TERRIBLE to just open a form
attached to a large table without any regards to what records are going to
be loaded into that form. You can't just open a form to a large table...and
then have your users go at it! Can you imagine if a instant tell machine
loaded EVERY name BEFORE you even done anything useful?
If you are not proving a good means for users to find the record BEFORE the
form loads..then your designs are not going to run any faster with bound, or
un-bound forms anyway. For some ideas as to what I mean by searching, check
out:
http://www.attcanada.net/~kallal.msn/Search/index.html
and
http://www.attcanada.net/~kallal.msn/Articles/Grid.htm
In my databases with small tables (and say, 4-5 users) response time is
instant. Are you experiencing any form load delays now?
2. I have the option of using SQL vs recordset. Is one
faster than the other or is there any benefit of using
one over the other?
Well, you can stuff the sql direct into the forms record source like:
Forms!MyCoolForm.RecordSource = "select * from tblCustomer custId = 123"
However, I never seen any benefit or speed increase when using a recordset,
or sql. In both cases, the record has to be loaded. As long as there is a
index, then the above can be written as:
docmd.OpenForm "MyCoolForm",,,"CustId = 123"
The above is a bound form, and only the ONE record you specify in the
"where" clause will be sent down the wire...so, bound, or un-bound, I can't
see how there will be a difference in performance. You are only grabbing one
record here if a index is available.
3. How do I address form having a subform in a 1 to many
relationship. I am not sure if I can still link unboubd
forms using Linked Child and Linked Master field?
You will have to use temp tables. So, no, you can't populate the sub-form
via code anyway. You either have to provide a table, or a query. Again, I
don't see any issues of performance here again if you are careful with your
designs. For example, I often have 4 or 5 sub-forms, but they are behind tab
controls..and I only load the sub-form when the tab is hit. So, my form
loads as fast with 1, 2 or 6 sub-forms...since I only load them until needed
(but you again don't need to use unbound forms here to achieve this).
For general editing of data and most forms you will not see any increase in
performance at all. Good performance in ms-access occurs by using good
designs, and restricting the records loaded into the form in the first
place. Trying to fix poor performance with un-bound forms will not help at
all in most cases.
