Forum Discussion
First addnew record slow
Almost correct. You want a permanent database connection to the BE. This is how I do it:
AutoExec macro calls Startup public function in a standard module.
As one of the lines in that function I open a form frmHidden with acHidden. This form is bound to a small lookup table in the BE (e.g. tblUsers or tblStatus).
The form stays open for the duration of the app.
That excessive delay should be gone now. I hope it is not transferred to opening frmHidden.
Tom, thanks very much for your suggestion. Unfortunately it did not bring about the desired result. I created a dummy table on the BE, and in the Autoexec macro I open a newly created form that is bound to that table. The form has a single control that is bound to one of the data fields in the dummy table. The macro opens the form as "hidden" (I tried it hidden and normal -- same result).
The timing of data entry on the form that concerns me was still taking 25-35 seconds to enter the first record. Every subsequent record takes under a second to add. (Out in the field I get reports of waiting over a minute for the first record to be added.)
I tried the same scenario, but instead of a hidden form bound to a dummy table, I created a hidden form bound to the same table where adding a record is so slow on the first record. The form is just bound to one field on the bound table. Same result. I even tried a line of code that when this hidden table is opened, requery the single control -- I guess I was hoping to force the data to be pulled in across the network -- sort of like priming the pump. But still, that first record was agonizingly slow. Not sure if you or anyone else has some new tricks up the sleeve. I hope so.
Thanks again.
- Hmm, what's so special about your table?
Does it have a PK?
No excessive fields, indexes, or relations?
Same result if you made a structure-only copy of your table, and wrote to this empty table?- It's a pretty standard looking table. The table has 21 fields, one primary key that contains autonumber data,, one relationship with another table, There's just a couple of thousand records out there (I archive older records in a different table). Adding a record should be a low stress thing, and other than the PK, there are no additional indexes . And it is very fast, AFTER you get the first record of a session in there.
For the record, I give the users a button they can click after selecting a record from a list box and delete that record. If that's the first thing I do in the session, it takes a long time to delete. If I first enter a few records and then try to delete a record, it's very fast. Weird.
I've been writing code for Access since Access 2.0 and I've never seen this sort of behavior.
As a separate test, I create a small table and wrote some code to add a record using an INSERT SQL statement instead of using the DAO ADDNEW method. That also took a long time, and the target table has only 2 fields just a couple of records that i added.
<< SIGH >>- For your next test you move the BE to your workstation. Now it is fast, right?