Aug 02 2022 03:34 PM
Aug 02 2022 03:34 PM
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
Aug 02 2022 05:35 PM
Aug 03 2022 02:24 PM
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.
Aug 03 2022 02:41 PM
Aug 03 2022 03:18 PM
Aug 03 2022 04:43 PM
Aug 03 2022 04:56 PM
Aug 03 2022 05:07 PM
Aug 03 2022 10:09 PM
Aug 04 2022 02:09 PM
Aug 04 2022 05:41 PM
Aug 05 2022 02:31 PM
Aug 05 2022 03:32 PM
@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.
Aug 06 2022 09:25 PM
Aug 06 2022 10:28 PM - edited Aug 06 2022 10:32 PM
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:
select * from yourTable where (CriteriaHere);
for new record:
select * from yourTable where (1=0);