access 2007 form sql subform

[Guy RULKIN]

When i use a database with linked table on sql 2005, each time i open a form
with subform, access load all the record before showing the form. If the
table has many records, it's taking a long time (30 seconds) before loading
the form (a table with 30000 records and 100 fields).

With access 2, 95, 97, 2000, 2003, it worked fine. Only a few pages of data
on opening

 

[Jeff Boyce]

Guy

Instead of binding the entire recordset (table) to the form, and the entire
recordset (table) to the subform, consider using something, say, like an
unbound combobox in the header of the main form to allow selection of the
ONE record you will need to see/work with in the main form.

And if you've used standard main form/subform construction, the subform will
"look" at the main form on open, see that there is NO record to get subform
data for, and load NO records.

You'd use the AfterUpdate event of that combobox to requery the form. You'd
base the form, not on the table itself, but on a query that uses a Selection
Criterion for the ID field that points back to the combobox, e.g.:

Forms!YourMainFormName!cboYourComboboxName

When your form first opens, there's nothing in the combobox, so the query
returns no records. The form and subform should load very quickly. When
you select a record from the combobox, the AfterUpdate event refreshes the
form (and subform).

Is that what you were looking for?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[guy rulkin]

Jeff,

In older version of access, all works fine. I know your tips but it's not i
want. My form should shows more than one record as previous version of
access does. If the user wants to show all the records, he must click on
the "last record" button. It was very nice for me.

Microsoft has changed how access get the records. Even if the subform shows
records of an empty table (the query is very fast then), the master form
gets all the records from the master table. This is a bad thing for network
activity and speed!

Thank you for your answer,

Best regards,

Guy