Auntiejack56
Registered User.
- Local time
- Today, 12:22
- Joined
- Aug 7, 2017
- Messages
- 175
Hi,
I have an Access 365 FE with a SQL Server Express backend. It works fine in test on my machine (which has both FE and BE).
FE is 4mb, backend has half a doz fact tables with 2-300 records in each.
I've migrated the SQL to the client's (cloud) server, and connected the FE to it from the client site - FE is on one user's C drive.
If I open a parent form containing a subform set to a datasheet, bam - records return instantly. Change the sourceobject of the subform to a different datasheet, bam - shows the new records.
But if I leave the datasheet open and attempt to open a second form of any size, there is a 10 second delay. If the form is complex, then the delay is longer. When I say complex, I mean dynamically assigning a SQL String as a rowsource for a listbox. (That's not complex!).
Similarly, if I open a table, records show instantly. If I open a second table, even one containing no records at all, there is a 10 second delay.
Lastly, if I create a datasheet and then set it to Snapshot (or even if I set the recordsource to a passthru query), then open it, records show instantly. Then open a second form, 10 second min delay.
Anybody have any ideas that might help? Is it some form of dynamic allocation of the number of connections? It doesn't seem to relate to locking because I've used readonly datasheets to avoid that, and the problem persists.
Jack
I have an Access 365 FE with a SQL Server Express backend. It works fine in test on my machine (which has both FE and BE).
FE is 4mb, backend has half a doz fact tables with 2-300 records in each.
I've migrated the SQL to the client's (cloud) server, and connected the FE to it from the client site - FE is on one user's C drive.
If I open a parent form containing a subform set to a datasheet, bam - records return instantly. Change the sourceobject of the subform to a different datasheet, bam - shows the new records.
But if I leave the datasheet open and attempt to open a second form of any size, there is a 10 second delay. If the form is complex, then the delay is longer. When I say complex, I mean dynamically assigning a SQL String as a rowsource for a listbox. (That's not complex!).
Similarly, if I open a table, records show instantly. If I open a second table, even one containing no records at all, there is a 10 second delay.
Lastly, if I create a datasheet and then set it to Snapshot (or even if I set the recordsource to a passthru query), then open it, records show instantly. Then open a second form, 10 second min delay.
Anybody have any ideas that might help? Is it some form of dynamic allocation of the number of connections? It doesn't seem to relate to locking because I've used readonly datasheets to avoid that, and the problem persists.
Jack