Forum Discussion
First addnew record slow
I am running an Access split database application using Office 365. I have a form that is not bound to a table -- the user fills in multiple fields on the form and then clicks an "Add Record" button...
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?
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 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?
- Yes, the problem goes away with local tables.
Tomorrow I'm going to try to just use the Form wizard and create a table bound to the underlying table and see if that makes the problem go away (I'm not fond of bound tables, though -- there are quite a few things I do that work better with an unbound form).
I suppose I could set up a local table and post the records the user is adding to it, and then execute an APPEND query when the user closes the form, or clicks a button called "POST RECORDS," But that's pretty clunky. I can't shake the feeling that there's a simple answer here. To be continued.....
Thanks so much again for your help. I'll keep you posted.- If I had to speculate it is environmental, not a programming issue.
I bet if you created a very small repro, we would not see this problem in our environments. That would be a logical next step. If I am right, then you really have to suspect your infrastructure.
Would you consider upsizing the BE to SQL Server?
