Slow Access via LAN and windows 10

[gemma-the-husky]

Can you provide the exact code you are using to maintain the persistent connection.
You can do this in a variety of ways.

I have an empty table called dummytable
In my startup, I declare a recordset, and connect it to that table

public dummyrst as recordset

function startup()
dim db as database
set db=currentdb
set dummyrst= db.openrecordset("dummyTable")
end function

function checkdummyrecordset() as boolean
checkdummyrecordset = not (dummyrst is nothing) 'this syntax to avoid run time errors by trying to use a recordset that does not exist
end function

Now an unhandled error will trash my public variables including dummyrst, and the persistemt connection will fail
I add a button to my switchboard to check the persistent connection, although I never need it

if checkdummyrecordset then
    msgbox "The persistent connection is active"
else
    msgbox "The persistent connection is not active"
end if

when my database closes, I add

if checkdummyrecordset then 'again, to avoid run time errors.
   dummyrst.close
   set dummyrst=nothing
end if

One thing that you will see is if you try to reconnect your data tables

If you don't have a persistent connection. it might take 1 second per table to connect the tables for a database that is already in use by another user. So a 20 table database might take 20 seconds. Every time you open a form, or run a query, the same delay happens.

If you create the persistent connection FIRST, and then connect the tables, it will take one or two seconds., and as long as the persistent connection remains, you don't get any further performance hits.

 

[vagman]

no idea, haven't used access 2000 for twenty years. Her is a link to the patches that were issued

Microsoft Access Version Releases, Service Packs, Hotfixes and Updates History

Microsoft Access has many versions, Service Packs, Hotfixes and other Updates with Office

www.fmsinc.com

you should be able to see what has been implemented in something like File>Help or it's equivalent for 2000

However I doubt that is the issue, more likely the code is not doing whatever it does very efficiently

You haven't said whether or not you have tried the suggestions already made.

In answer to a question you asked, I do not know of any MAJOR changes to file locking mechanisms from Win7 to Win10, but there could be some complexity with changes to Trust Center. Microsoft CAN'T easily change locking protocols because so many non-Windows products emulate Windows File Sharing. If MS "broke" file sharing, users and 3rd-party vendors would raise a ruckus that would be audible around the world. However, the two versions of Windows would have different PATCH history. You would do your best to keep up with patching each system for as long as patching is available, though for Win7 I think that is mostly out of support status.

It might be useful to take some metrics on your Win7 and Win10 machines. Find one that runs fast and one that runs slow even though both are on the same (or approximately the same) speed hardware. Use Task Manager and select the Performance panel. Near the bottom of that you can select the Network display and see average or peak data rates. (The graphs are pretty but it is the actual numeric data rate you want, UNDER the graphs.) You might have to find a way to split the screens between Task Manager and Access so you can "do your thing" with Access and see what TM has to say about network speed. Run your app on each system. If the corresponding average and maximum network speeds are within a few percent of each other on the two machines, the problem isn't the network.

Yes I tried all suggestions already made and tried also LAN settings, cables, routers......
I realize that the problem is with forms that have VBA code events.
When I remove the code everything works fast if not access form hangs up in random times.
I don't know if there is a solution to replace the vba code or an update of Access 2000 or to go back to windows 7

 

[Minty]

Have you ensured that the locations for all the files are in the Trust Centre?

Access security can slow things down significantly, if not.

 

[vagman]

Have you ensured that the locations for all the files are in the Trust Centre?

Access security can slow things down significantly, if not.

I have the old Access 2000
I stay with this for many reasons like :
I don't like the new Navigation Pane (Please inform if there is a way to have the old menu of Access 2000)
Also in Access 2000 when you pressed contentiously with mouse to the next record in a form you see all next records with one click
With new versions of Access you have to press many times to advance to the next records.

 

[Minty]

That may be your problem. @The_Doc_Man may be able to shed some light on this.
My understanding is that the older versions of Access probably use SMB1 protocol and that new versions of Access will be using a later (not depreciated) version.
I suspect that will be slowing down the processes.

Nav Pane - what features are missing, I actually prefer the new one with the search bar.
Mouse scrolling - this was actually a bug, that they have fixed. It could frequently lock up a database when it was automatically scrolling through millions of records. You can easily press the last record button on the Record selectors, or make your own command button to take you to the last record.

 

[The_Doc_Man]

SMB1 vs newer SMB versions will not affect the observation that the effect may be related to forms with event routines. The question is going to boil down to what the event routines are doing.

To the best of my understanding, newer SMB versions can do more with connections, but if we are talking about a situation that already uses persistent connections, a lot of the SMB improvements become ... not zero, but maybe a bit less impactful. It is possible that I missed something last time I checked that, though. It was a LONG (and BORING) list of new features.

Also in Access 2000 when you pressed contentiously with mouse to the next record in a form you see all next records with one click
With new versions of Access you have to press many times to advance to the next records.

That sounds more like a system setting that might be found in the FILE >> OPTIONS >> CURRENT DATABASE or CLIENT SETTINGS arena, or with a system's mouse auto-repeat feature (related to keyboard auto-repeat).

Reviewing the thread, I note that in your post #17, you suggest that when you do a Compact & Repair and a recompile, that things got slower. Did you try a recompile? This sometimes improves matters if there are specific types of corruption involved.

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

www.fmsinc.com

("Recompile" is not the first heading of the site. Scroll down a bit.)

 

[vagman]

SMB1 vs newer SMB versions will not affect the observation that the effect may be related to forms with event routines. The question is going to boil down to what the event routines are doing.

To the best of my understanding, newer SMB versions can do more with connections, but if we are talking about a situation that already uses persistent connections, a lot of the SMB improvements become ... not zero, but maybe a bit less impactful. It is possible that I missed something last time I checked that, though. It was a LONG (and BORING) list of new features.



That sounds more like a system setting that might be found in the FILE >> OPTIONS >> CURRENT DATABASE or CLIENT SETTINGS arena, or with a system's mouse auto-repeat feature (related to keyboard auto-repeat).

Reviewing the thread, I note that in your post #17, you suggest that when you do a Compact & Repair and a recompile, that things got slower. Did you try a recompile? This sometimes improves matters if there are specific types of corruption involved.

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

www.fmsinc.com

("Recompile" is not the first heading of the site. Scroll down a bit.)

Yes I did recompile and compact and repair
It has some improvement but after few hours the same again.

 

[vagman]

I see that the problem is worse when a form has the Me![test].Requery

 

[Minty]

What is the SQL for the record source for the form?
Are you bringing every record in from the underlying table?
How many records are in the table?

 

[vagman]

What is the SQL for the record source for the form?
Are you bringing every record in from the underlying table?
How many records are in the table?

This form has a table for source with 22000 records and 3 sub forms with 120000 6000 30000 records
Every time that form is opening there is an Requery event with subforms
The problem of delay appears in random times (1-2, 20 times per day)
Before windows 10 everything worked fine (Access 2000)

 

[Minty]

Okay, that is not really an optimum design. I assume you have a search facility on the form?
Do you need to see all 3 subforms all the time?

Why are you re-querying the subforms or main form? If you have set up the Master/Child data points correctly that should be unnecessary.

Depending on your answers I would
a) Load the main form with no records, use the search to then load with a greatly restricted recordset.
b) If you can move some of the subforms (the ones you don't need to really see all the time) onto tabs. Then only load the subform if the end user clicks on the tab. (This will obviously depend on the relevance of the data they are displaying)
c) Don't requery everything all the time. Unless you know there are new records added you don't need to do that, the subforms will automatically refresh when you change the master record on the main form.

 

[vagman]

Okay, that is not really an optimum design. I assume you have a search facility on the form?
Do you need to see all 3 subforms all the time?

Why are you re-querying the subforms or main form? If you have set up the Master/Child data points correctly that should be unnecessary.

Depending on your answers I would
a) Load the main form with no records, use the search to then load with a greatly restricted recordset.
b) If you can move some of the subforms (the ones you don't need to really see all the time) onto tabs. Then only load the subform if the end user clicks on the tab. (This will obviously depend on the relevance of the data they are displaying)
c) Don't requery everything all the time. Unless you know there are new records added you don't need to do that, the subforms will automatically refresh when you change the master record on the main form.

Thank you very much.
Yes we need to see all 3 subforms all the time.
I will try to remove requery from code.
I don't know why the programmer add this before many years (I can't find him to ask)

 

[vagman]

Update
access works great with tables in the same pc but when I have linked tables in lan sometimes works fast sometimes very slow
Is it a problem of lan ? of Switche maybe?

 

[The_Doc_Man]

OK, that gives me at least a little hint. Understand that Windows imposes file locking on ALL FILES, at the file level, and it does so long before any other app gets involved. You also don't have a choice. Even if the file in question allows FULL CONTROL (not recommended, by the way), Windows file systems WILL check the file permissions each time you attempt to access the file. File-level locking is ALWAYS managed by the computer that is acting as the host of the file. If this is a shared back-end file on a system accessed through the LAN, that system is the "distributed lock" manager for that file.

The Access .LDB or .LACCDB files are added layers for extra locking of data within the file. BUT unlike Windows, which uses Distributed Lock Management, individual copies of Access (on each user's machine) are always the lock managers for the content locks managed through the .LDB/.LACCDB lock file. Those lock files are ALSO managed by Windows first and Access second.

When you say "always fast for (local tables) but (variable speed) for LAN-based backend tables" this is a primary sign of lock contention. A locally held lock operates at memory speed because the locking data structures are held in the memory of Windows itself, locally visible to Windows as it considers that database AND also local to Access running in the same system's memory.

A LAN-based file must go through network-based "access arbitration" (note LITTLE "A" in this case - not MSACCESS.EXE but FILE ACCESS arbitration). That network lock transaction is anywhere from 10 to 1000 times slower than local arbitration depending on the speed of the LAN. Further, every lock held on the file must be checked for each new operation. Fortunately, a lot of the information is cached in what is called "scratchpad" memory, but even so, it takes time to read through the cached data. If you have 5 simultaneously active users you can expect the access checks to take 5 times longer than if you are the only active user.

Check all of the queries that are involved in this "main+3 subs" situation. See if they are set for either NO LOCKS or OPTIMISTIC LOCKS. If any of them are set for PESSIMISTIC LOCKS then change that. It would appear (to me at least) that your problem is visible when you have queries that must contend with each other (the "variable speed" issue). I am guessing since I can't see your machine, but that variability is most likely due to more than one user being in the system at the same time using the same "main+3 subs" facility.

I am again guessing, but given the numbers you mentioned earlier, frequent requeries are going to bog you down when multiple people are able to execute multiple queries essentially simultaneously. If there is a way you can reduce the number of records that must interact, perhaps by introducing extra WHERE clauses to act as filters, you would do yourself a favor. You were given a link to an article that talks about ways to speed up operations. It might not hurt to revisit that article to see if you have missed anything.

 

[Minty]

Another possible option - if any of the sub forms a for viewing only, make sure you specify a snapshot recordset type, it will be read only but operates a great deal quicker.

 

[The_Doc_Man]

And to add to Minty's excellent comment, that would be a case where a query could specify NO LOCKS safely. (Still doesn't stop Windows from checking things - but a lot less to check.)

 

[Pat Hartman]

NO LOCKS is the default and you should never have to change it. It is best to let Access control the locking and add code to trap errors if necessary.

 

[vagman]

All forms are with no locks and the 3rd subform is with snapshot recordset type (this doesn't make a difference).
I put for test another same file with tables on server
I see that if I connect my db to this there is double speed browsing the form (100 records per 10 seconds)
if I connect my db to the old file already opened by 2 more users I have usually 58 records per 10 seconds and few times 100 records per 10 seconds
So I think that the problem is when many users are connected

(the speed of browsing from local disk -no lan is 120 records per 10 seconds)

 

[The_Doc_Man]

Your "local disk" case suggests to me that somehow you are dragging along those three child tables. Or more precisely, they are dragging you down. That speed seems abysmally slow. The relationship between the parent and child tables must in some way be improperly formed.

 

[vagman]

I made compact the client mdb and I put it in 3 pc
On my pc I see again 58 records per 10 seconds
on 2 other pc's 100 records per 10 seconds
Maybe there is a hardware problem with my pc network?