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. All tables are linked to an Access database file on a network. There is VBA code behind the form that checks for user errors and finally uses a DAO recordset object to use the ADDNEW method to put the record into an Access table that resides on our network and is the repository for multiple users. The code works fine, but the first record added in a session can take 30-60 seconds or more. Every subsequent record in the same user session is added in less than a second.I tried to just execute an INSERT statement, but got similar results. I tried opening a recordset object when the form opens and keeping that open until I need it to ADDNEW.This is driving me nuts. Any help greatly appreciated

> I tried opening a recordset object when the form opens and keeping that open until I need it to ADDNEW.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_van_Stiphout 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?

First addnew record slow | Microsoft Community Hub

16 Replies

Tom_van_Stiphout
Iron Contributor
Aug 03, 2022
> I tried opening a recordset object when the form opens and keeping that open until I need it to ADDNEW.
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.
- RichNewman
  Copper Contributor
  Aug 03, 2022
  Tom_van_Stiphout
  
  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.
  - Tom_van_Stiphout
    Iron Contributor
    Aug 03, 2022
    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?

Forum Discussion

First addnew record slow

16 Replies

Resources