First addnew record slow

[RichNewman]

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

[#2]

> 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.

[#3]

@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.

[#4]

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?

[#5]

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 >>

[#6]

For your next test you move the BE to your workstation. Now it is fast, right?

[#7]

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.

[#8]

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?

[#9]

It's doable, but this is a complicated application and I'm on a pretty short budget string. Using SQL Server would take time to migrate, and it also opens up some licensing costs.
A small version just to test is a good idea -- I'll try that. Also, I keep getting the nagging feeling that perhaps some Access setting might be involved, though nothing jumps out at me.

[#10]

"persistent" connection is not about Opening a Table from the BE and helding it open until you quit your app. It is about Opening the Whole BE (not just a single table) and helding it Open until you quit the app.

public function OpenBE()
dim sBackEnd As String
Static myBE As Dao.database
Set myBE = currentdb
'retrieve the backend path and name
sBackEnd = Replace$(myBe.TableDefs("oneLinkedTableHere").Connect, ";DATABASE=", "")
'open the BackEnd
Set myBE = DbEngine.OpenDatabase(sBackEnd, False, False)
end function

call the above from autoexec macro.
don't worry when there is no code to close this database.
it will get closed automatically when you close your app.

[#11]

Hi arnel_gp,

Sorry for the delayed response. I appreciate your help.

I'm having trouble getting this code to work. In the Replace$ statement, all I did was to swap the name of one linked table in my back end file, for the string "oneLinkedTableHere". I left the rest of that statement intact. So it now reads:
Replace$(myBe.TableDefs("tblActivity").Connect, ";DATABASE=", "")
When I run this, I get run-time error 3055 Not a valid file name. It's pointing to sBackEnd as faulty.

I changed your code just to test to the following:

Public Function OpenBE()
Dim sBackEnd As String
Static myBE As Dao.Database
Set myBE = CurrentDb
'retrieve the backend path and name
sBackEnd = "M:\Proposals Databases\Activity Tracker Data.accdb"
'open the BackEnd
Set myBE = DBEngine.OpenDatabase(sBackEnd, False, False, "MS Access;PWD=Password123")
End Function

In the OpenDatabase statement, I think I have to put in the password as you see above because that BE file is password protected.

Did I capture what you intended? My rewrite runs without error, but it didn't solve the problem. Maybe because of something I misinterpreted about your code?

Your help getting this right will be very much appreciated.

TIA

[#12]

there is a better explanation here but i think you got it.
https://www.fmsinc.com/microsoftaccess/performance/linkeddatabase.html

[#13]

Thanks again. Yes, I saw the posting you refer to last week and attempted that. That was actually the source for me to get the syntax of how to open a password protected database.

I did try today to set up a simple split database. On the back end the database contained one table with an index autonumber field, and a simple field that holds a date. On my laptop, I created another database that links to that table across the same network, I created a form that has only 2 objects: a textbox for input, formatted as a date, and a button that triggers the code to take the user input and use the ADDNEW method to add it to the table. I did not get any unusual delays for the first or any of the subsequent records I added. Now the trick is to figure out why. I can tell you that although I have a few dozen editing checks to make sure the user is following the rules, in my testing I commented out all of that and just did the ADDNEW based on user input. Got the same issue -- an extremely poor response time for the first record, and a very fast response for subsequent record additions. I will do some tinkering with the code, but off the top of my head it's hard to understand why the identical code runs over and over and only misbehaves the first time it is run in a session.

Thanks for hearing me out -- I very much appreciate the additional sets of eyes.

[#14]

@RichNewman That is an excellent result and more in line with what one would expect and what most of the Access users worldwide are seeing.

I suspect it is something on the machine. Maybe a bad software configuration, maybe bad hardware (NIC?). If it was my problem, I would decide ahead of time to spend X hours on trying to figure it out. After that, I would rebuild the machine from scratch (at least that takes a known quantity of time, while continuing with the first step does not, hence the timebox). If that did not help either, I would scrap it and buy a new one.

[#15]

out of curiosity, why are you using .Addnew method of a Recordset.
why not use a Bound Form?

[#16]

There are lots of posts about this, and folks tend to get pretty defensive about their preferred approach to forms,
https://nolongerset.com/use-unbound-forms-to-add-records/
https://www.access-programmers.co.uk/forums/threads/unbound-vs-bound-forms.322411/
Much of what you can do with an unbound form you can do with one that's bound. But I'm sure I can build much more user-friendly interfaces with better error handling with unbound forms. I feel I have far greater control, and if the GUI is designed well for your users, there's less training involved.
That said, there's definitely more work to reap the advantages. However, I've been doing it long enough so that it's not really a big deal to me at this point.

[#17]

well there are people Disagreeing on Mr.NolongerSet and he himself admit prefers Bound form (his last comment).
of course it is up to your choice. but looks like you are getting an obstacle already.

you can limit the number of records being edited/created by returning 1 record (for editing) and no record for adding:

for editing:

select * from yourTable where (CriteriaHere);

for new record:

select * from yourTable where (1=0);