The best way to speed up performance is to minimize the amount and frequency of data retrievals in your code and/or database design.  Do you allow users to browse 10k rows to look for a record, or search and get only a few returned records?  In all my years I've never used cache size, because it's just guessing.  "100" may improve for some tables but not others.  The biggest difference in performance has always been how I design the database.

Yeah, I've never used CacheSize either with record sets. Like Kevin said, you're better off limiting the number of records up front.

Can you explain what is going on with your record sets? Is there any record locking involved?

I just spent the day stress testing my ADS db in a variety of situations. So far, I've been mostly developing on an integrated development .accdb file, and things go dang fast. Then I moved to the network (on purpose a 100 mb choked hub) and saw performance go into the tank. For example, the fastest laptop runs the job in about 28 seconds when integrated. Splitting and onto the network, the same job takes 1687 seconds. With two other nodes running the same stress test (doing record set writes), performance drops to 7029 seconds. All of the record writes require an Access lock be set via the .read. The three workstations are all requesting the lock at pretty much the same time from the Backend. The good news, after a lot of tweaking, the integrity of the data was established. The performance however...

Which goes back to an older post with Richard on caching. Based on this stress test, I will be designing my dbs to run heavy month-end processes on a Frontend .accdb/e running on the Backend server, rather than over the network. An old-school approach that's bound to save hours of network banging.

Did my summary post fail to post, or did I do something wrong again with the text?

The simple answer is no we do not allow people to pull thousands of records from the internet, we use WHERE clause, in this case each user has only one record at a time to edit. All the combo boxes load blank until the users type in three characters in the combo box only then can a short list start building for selection.

Surprising the record set which collect data for tax transportation via an API takes about 26 seconds to complete , but if we use just an access back end it takes only two seconds, that is where our problem is. The internet we are using is star link which is well fast.

I'd use a text box as your filter and then perhaps fill in an open list box with the records once the user has typed in at least 3 characters. In the AfterUpdate event do something like:

if Len(FilterBox.Text) >= 3 then
' load data into the listbox

I've had something like this on the TechHelp list for a while. Perhaps a video is in order...

if Len(FilterBox.Text) >= 3 then
' load data into the listbox


We built a module to help initialise all combo boxes , some call it lazy combo boxes, also just check why it is not instant when entering data using a form or even directly in tables