Difference between dbl click and shift-click

[jerrypd]

I have an access database as a front-end to a MS SQL server.

On a form, I have a bound grid that selects a bunch of fields from a table.
When I have relatively few records in the table, I see no issue.

I just loaded 65000 records, and now the form takes about 2 minutes to actually open.

When I use shift-click and run the form manually, it loads almost instantaneously.
I am assuming somehow the debugging mode is maybe late-binding my data?
How can I get this functionality from the 'double click' which my users will be doing instead of the 'shift-click' I use for debugging?

Hoping someone can shed some light on this, and if there is some other solution, i would be most open to hearing it!

Thank you,
Jerry

[pbaldy]

Personally I would never load a form bound to a table with 65k records (though I have a client who does). I might have a form where the user chose the date/employee/whatever they wanted to view, and then this form opened filtered to those records. You don't want to be moving 65k records over the wire in my opinion.

[jerrypd]

Thank you for your response, pbaldy, but it does not answer my question.
I understand this is not an optimal solution.
I am inquiring as to why it loads fast in the debugger, yet not at runtime.

[pbaldy]

Sorry, I don't know what you mean by opening a form with shift-click. From the navigation pane (which I don't let users into)? I can't open a form with that combination of keystrokes. Perhaps someone else understands better than I.

[jerrypd]

I apologize. I misspoke. It is a double click while holding the shift key.
If you higllight the access database, then while holding the shift key, you double click the icon, you will bypass the startup macros along with some other differences.
One of them seems to be in the area of how bound forms are populated as far as I can tell.
As stated above,
when I launch the database while holding the shift key, then manually launch the form, it loads fast.
when I launch the database the normal way (just double clicking the icon), the form loads via a menu, and hangs up for quite a while.
I hope this explains it further?
Jerry

[CJ_London]

I suspect what you are seeing is an illusion - a form will display the first few records, but will not be useable (e.g. to sort/filter) until the whole form recordset has been loaded.

To test this possibility, ensure your form is displaying the navigation buttons at the bottom - the recordset is not fully loaded until the recordcount is populated

Have you tried opening the file without double click and then opening the form directly from the navigation window rather than a menu form?

Beyond that, you need to provide more information. For example when you open the from from the menu, what is the code you use? Are your tables in SQL Server properly indexed? What is the recordset to your form, what open/load/current events are you using? Does the form have subforms, combo's, listboxes based on large datasets?

I agree with Paul, you should not be using tables and filters to manage your form recordset, you should be using queries and criteria, Note that the WHERE parameter for the openform command, applies a filter, not a criteria.

As an example a 'customer form' based on a table with 65k records which has a 'search combo' for the user to select a customer would be loading 65k records twice, once for the form recordset and again (tho probably fewer fields) for the combo.

Hopefully you will see that there are a number of issues here, whether they apply in your case, only you can say.

[jerrypd]

I unserstand the questions, and I am not trying to be obtuse.
The form is quite complicated, and it is not my design. I inherited this and need to keep it running until a replacement is coded.
I am certain you are right Ajax, that it is an illusion. But it is an illusion I can certainly live with.
Is there a way to get this functionality from the "normal launch" of the app vs. the "debugging" launch? The users cannot sit around waiting for all the records to load on startup.
It is also true that 99% of the time they will be using the first x number of records, but there are times they need to go deep into the recordset for older data.

[jerrypd]

To further support your theory, I added a recordcount textbox that is populated in the Form_Current() subroutine.
When I debug launch, it shows 101 records.
When I regular launch, it shows 61,430 records.
Yet in both cases, I can scroll ot the bottom and get to the last (61,430) record.

So it looks like the debug launch gets an initial 101 records, then the rest in the background. This is the functionality I need.

Hope this makes sense??

[CJ_London]

when you open a form directly, if the form has filter properties set then these will be applied. If opened using code (openform) then they will be ignored.

So sounds like your form has filters set. If it has then to test, in 'debug mode', remove the filter, save the form and reopen

You have not really clarified whether you have tried what I suggested. What does debug launch mean? You opened with shift? or provided the code, answered about indexing, etc. I would also add, what does your opening macro do? Until you do, I don't believe I can assist further as all I am doing is making guesses

In short, in my experience, opening with shift has no impact on performance. The issue is with your form and the way you are opening it. And/or possibly your autoexec macro.

I suspect the solution as already advised is to move away from filters and use criteria. No idea if it will work or meet your requirements but as a temporary fix you could try setting the form filter to something like False and Filter on load to Yes which will stop any records being displayed initially, or put "False" in your openform where parameter.