Bound to unbound form....what's going to happen?

[Cheese_whiz]

Hi all,

I have an app with a main form with 5 tabs, teh first of which has the main
records from teh query behind the form. All the other tabs have subforms.
The subforms are not loaded until the 'change' event fires on the tab
control, which runs a 'select case' block and loads teh appropriate subform.

There is a combo box on the main form/main tab to look up existing records.
There is also a search form that opens from the main form, and which uses
various controls to create an sql string which is then applied to the form as
a filter.

Again, the main form is bound to a query. My question is this: I removed
recordsource query and everything seems to work. I know that loading all
those records for nothing is slowing down my application (or, at least it
could be....some of the issue is the chicken wire and scotch tape network we
got going). The form takes like 10 seconds to load at work. Once it's
loaded, everything else works great and no other portions of the app appear
to have performance issues like that one.

Will that work? Is it that simple? Can I just remove the recordsource from
a bound form like that and everything still work?

perplexed,
cw

 

[Arvin Meyer [MVP]]

No, it won't work by simply unbinding the recordsource. If you do, every
control on the form will read:

#Name

But, if you unbind the controlsource of each control, and use an Append
query, or the Recordset.AddNew Method, you should be able to do it fine. You
will need to fill the form from a recordset as well. A lot more work, to be
sure, but much quicker and more stable on a flaky network.

As an aside, you really need to fix the network to use any fileserver
database.

 

[Allen Browne]

No: I could not recommend changing a form from bound to unbound (or vice
versa) on the fly.

Attempting to do so would mean changing not just the RecordSource of the
form, but also the ControlSource of each bound control. If you really want
to do that, I suggest you make sure that the Name of the controls is not the
same as the name of any field (e.g. City field is bound to a text box named
txtCity, not City.) If you do refer to the fields (rather than the controls)
in your code, that could also be problematic.

Even aside from that, Access does not cope well with fields that just appear
or disappear while the form is running. Even a field that changes data type
while the form is running can cause some versions of Access to crash (shut
down by Windows.)

Stepping back from the details, your goal seems to be performance. If you
design your forms well, and they are based on normalized and correctly
indexed tables, you should not have to waste time doing this kind of thing.
A form with several subforms should load quite snappily. Access does this
stuff really well: it doesn't actually load all the records initially--just
enough to display what it needs. So it might be better to pin down what
specifically is causing it to be slow, and address those issues.

The "chicken wire and scotch tape network" sounds *really* scary. If you do
have unstable connections (machines that could fall of in the middle of a
write), you can expect the database to corrupt frequently.

 

[Cheese_whiz]

Thanks for the replies, guys!

Well, I didn't think it would work, but it seems like it does. Here's a few
responses:

The form opens on a new record, which may explain why I don't get the ?Name
showing up in every field.

I always name my controls something different from the data they hold. I
try to follow the loose convention of adding an abbreviation for the type of
control to the front of the data item....so, txtCity for City data control
text box.

If the form doesn't load all the data when it first opens, then I might not
see much of a performance boost. I was laboring under the apparent delusion
that it WAS loading the entire recordset.

I probably was a little harsh on the network. It IS slow at times, and
occasoinally a connection will drop, but that's pretty rare. I've been
working with this application for over a year (on the network) and I've never
had any corruption issues. My small user group has been using it for the
last 6 months or so. I back up religiously with daily (or more frequent)
backups and about once a week I back every related file up to a separate
server. That means I have the live set of files including the back end db,
backup copies on a second server updated daily (at least (which, in turn, are
backed up by the IS group)), and a full set up backup files on a third drive
(my own harddrive, at the moment) backed up at least once a week.

Back to the issue at hand, if the recordset loading isn't slowing that form
down, then I don't know what is. Also, and I know both you guys have been
developers a lot longer than I have, but I still don't understand why it
seems to function fine without the recordsource. It loads fine, the combo
that finds individual records works fine, and the seperate search form that
opens records up back in that main form all seem to work like they normally
do. I just don't get it....

Thanks again for the replies, and I welcome any other feedback.
CW

 

[Arvin Meyer [MVP]]

I don't understand what you mean when you say "It loads fine without a
recordsource" What could it possibly load? A record source can be bound, or
unbound, but you must have one to have any data in the form. When using
unbound forms, you should load a single record for FormView, and multiple
records for Continuous or Datasheet view.

You can do the same with a bound form, by using a well-indexed query as a
filtered recordsource. Or:

DoCmd.OpenForm "MyForm",,,"ID = 123"

so a form opens with a single record. Even on a 100,000 records the form
above should open in about a second.

 

[Cheese_whiz]

Hi Arvin,

My mistake, I was sloppy with my choice of words.

What I should have said is the form 'opens' without problem without a value
in the recordsource property.

After the other responses (and yours as well), I'm beginning to think that
it probably isn't going to have any positive effect on the form opening speed
at work anyway.

I still don't understand what makes the form open so slowly at the start.

Thanks for the reply,
CW

 

[Arvin Meyer [MVP]]

Some high traffic networks benefit from a permanent connection from the
front-end on the workstation to the data in a back-end on the server. You
can maintain that connection by opening a hidden form connected to a small
table on the back-end, when you first start Access. Try that and see if it
will help.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Cheese_whiz]

Hi AM,

I actually read that somewhere and implemented it some time back. I think
it did help a little.

I'm starting to wonder what the issue actually is. I know when a user opens
the form in question, there is a prolonged time during which the status bar
indicates "calculating". That's why I thought it was because of all the data
being loaded into the form (well, all the data being gathered to use in the
form).

I wonder if a decompile would be worth trying? I can check the query behind
the form again, but I think it's in good order and properly indexed.

Sigh...

Thanks again for the suggestion.
CW