ACCESS FEED: Re: [MS_AccessPros] database very slow on network

WAN (Wide Area Networks) are almost always slow. I tried to install a front end database on a remote site with a backend in our servers. None of the command buttons would work, like forward, open form, etc. This was with a 3 gig circuit too.

I finally gave the user there Remote Access to a machine that had the front and back ends on it. He signs into that computer using RDC, and operates in the environment without any major issues..

I do have one database with a frontend in that location with a backend on my servers in the main office. It seems to work okay, but it only has a couple of command buttons.

I don't know how complicated your database is. But the more forms, command buttons, etc. you have, the slower it will operate.

On Wed, Jul 10, 2019 at 11:03 AM Rob Koelmans r..koelmans@metamicro.nl [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Possibly, but why would Access be so performance selective between local disc- and network-i/o?

Have a nice vacation and keep me posted. Would be great to hear what the issue was.

Rob

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Sent: woensdag 10 juli 2019 19:32
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] database very slow on network

I am pretty sure that its exclusive to Access.

I will be on vacation for a couple of weeks . I will try some of your suggestions when I return

Sarah

---In MS_Access_Professionals@yahoogroups.com, <r.koelmans@...> wrote :

If they are both 2003 it's not converting dynamically between the old and the new file-structure. Setting up connections over the network has much more overhead than onto a local partition. Why and if they conversion process was using more connection setups or not, I don't know. We saw that it was doing hundreds of separate connection setups which made opening of a form containing many controls en subforms taking up more that a minute to open.

Anyway, from 2003 to 2003 it's probably not that so that brings you back to the exclusion process. I presume the server is in the same ip-segment, so tracert in a cmd.exe would give you only one node. You can check that (you may need to install tcp/ip tools onto your windows instance). If client and server are in the same segment, you could refer to the server with its ip-adress instead of the server name, so it doesn't need to be resolved.

Then I should copy a 100 Mbyte file or so in both directions and measure what the transfer speed is. Both the copy running on the client copying from server to client and vice versa. Same for the copy running from the server and all four or some running combined. Then there is benchmark software that also measure connection setup time by doing hundreds of setups per minute.

You can compare the performance with copying on client or server locally. You can also check combined traffic by for instance running a copy on a client from a file from server to the server and vice versa. Everything above can also be done for hundreds of very small files/documents aaaaand you can check whether xcopy is making a big difference to copy.

After you concluded the problem is in Access exclusively or not, you know a lot more.

Rob

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Sent: woensdag 10 juli 2019 02:53
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] database very slow on network

They are both 2003. Still trying to understand why its only a problem on the network not on the local drive.

Sarah

---In MS_Access_Professionals@yahoogroups.com, <r.koelmans@...> wrote :

If your Backend is 2003 and your FrontEnds are 2007 or higher, start with upgrading your backend. Then reconnect ALL your clients before updating data.

Rob

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Sent: dinsdag 9 juli 2019 16:56
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] database very slow on network

I will check with technical support about the server, I don't think its a compressed folder.

The lock files are on the server.

Sarah

---In MS_Access_Professionals@yahoogroups.com, <r.koelmans@...> wrote :

BTW, is the fileshare perhaps a compressed folder (on a very busy server)? Is it a true partition of that server or some SAN virtual partition? Is it modifying/creating lock-files on client and/or server? Are both the client machine en de user of the same AD-domain of the server?

Start with upgrading the BE MDB IMO.

Rob

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Sent: dinsdag 9 juli 2019 09:28
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] database very slow on network

It should be Access 2007-2016, I think. Not 2002-2003.

I'm almost sure the live conversion was between those two. There's a small chance the issue was between Access97 format and later, but I should definitely rule it out if your forms have 25 or more controls. It's better to have it converted anyway. No reason to keep it on 2003.

Rob

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Sent: dinsdag 9 juli 2019 03:46
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] database very slow on network

'In my experience, there's your problem.'

Not sure what the 'there's' that you are referring to is.

Sarah

---In MS_Access_Professionals@yahoogroups.com, <r.koelmans@...> wrote :

In my experience, there's your problem.

Rob

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Sent: maandag 8 juli 2019 18:26
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] database very slow on network

the backend db is access 2003

Sarah

---In MS_Access_Professionals@yahoogroups.com, <r.koelmans@...> wrote :

Ah, and what type is the back-end database? Forms with many controls load extremely slow if access is live converting to an old database format.

Rob

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Sent: maandag 8 juli 2019 09:47
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] database very slow on network

What Crystal says reminds me vaguely of some issues specifically with Access in the past. If I recall right Access did have an over average of connection setups in relation to the amount of data that was transferred. This may lead to specific slowness in Access still with the cause in the network infrastructure somewhere. So specifically a slow connection setup but normal data throughput.

Rob

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Sent: maandag 8 juli 2019 06:53
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] database very slow on network

hi Sarah,

1. did turning off AutoCorrect and SubDatasheet on tables in the back-end make any difference?

2. are you using wi-fi or a cabled connection?

3. what is the Data Type and Size of?? Primary Key and Foreign Key fields? (you might have a lot, so let's just start with the tables for one slow form at a time)

4. are Relationships defined? with Referential Integrity?

Trying to help, but it is hard without knowing more about your database and environment. If something is confusing, no problem, we can clarify for you.?? Thanks.

respectfully,
crystal

On 7/7/2019 4:09 PM, crystal 8 strive4peace [MS_Access_Professionals] wrote:

ps again. I'm sure this is frustrating for you, Sarah. Please know we are doing our best to help even though you haven't answered any questions, and this is an Access forum

a couple more things (aside from what has already been mentioned) you can do with the database to make it faster are:

````````````````````````````````````````````````````````````````````````````
Turn off AutoCorrect

Microsoft Access Flaws - Failures caused by Name Auto-Correct, by Allen Browne
http://allenbrowne.com/bug-03.html

if you haven't turned it off, then it is on

in 2007:

1. Click the Microsoft Office Button Button in the upper left corner
2. choose --> Access Options (lower right corner)

in 2010+

1. File ribbon menu
2. Options (near bottom)

~~~

then:

3. Current Database from left sidebar

in Access 2000 - 2003:
1,2,3. from the menu --> Tools >> Options >> General

-----------------------------------
THEN:
4. Under Name AutoCorrect Options:
uncheck --> Perform Name AutoCorrect
5. Compact/Repair
6. then go back and uncheck --> Track Name AutoCorrect info
7. Compact/Repair again

````````````````````````````````````````````````````````````````````````````

once this is done, Set SubDatasheet to None in all tables.

Microsoft Access Flaws - Problem properties by Allen Browne
http://allenbrowne.com/bug-09.html

~crystal

On 7/7/2019 3:31 PM, crystal 8 strive4peace [MS_Access_Professionals] wrote:

cabled connections are much faster than wi-fi

On 7/7/2019 3:27 PM, crystal?? [MS_Access_Professionals] wrote:

hi Sarah,

it makes absolute sense! Access communicates with the back-end whereas other applications like Excel or Word don't usually have back-ends, so they wouldn't be affected by this.

It could be a cable going to YOUR computer that is bad (or other device).. Are you on a local area network with cables, or a wide area network without them? It could just also be a bad connection ... maybe something isn't screwed in tight. Or maybe a cable is sharply bent.

And then, again, maybe it is something else -- but cabling and devices in between your computer and where the back-end is would be the first thing to check.

~crystal

On 7/7/2019 2:37 PM, sarahk@... [MS_Access_Professionals] wrote:

Rob, thanks for your offer but my client will not agree for you to remotely connect.

Suggestions about cabling , wifi speed etc don't make sense if this is the only application on the network that is running so slow.

Sarah

---In MS_Access_Professionals@yahoogroups.com, <strive4peace> wrote :

thank you for your input, Rob -- and very generous of you to offer remote assistance. Perhaps if Sarah takes you up on that, you can share back to the group what needed to change? Nice to see you here!

have an awesome day,
crystal

On 7/7/2019 8:43 AM, Rob Koelmans r.koelmans@... [MS_Access_Professionals] wrote:

Oops, some Office or Windows10 mail filters seem to not like a word I just used.

??

Regarding the issue, if it would help (and practically doable), I can ask a specialized co-worker to assist you with teamviewer, anydesk, skype, remote assist atl.

Rob

??

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Sent: zondag 7 juli 2019 15:11
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] database very slow on network

??

??

It???s difficult te jump in because it???s an exclusion process. You start by checking the settings on both sides (type of cabling (Wifi??), speed settings, full/half duplex, auto negotiate going wrong, rx/tx-buffers settings, optimizations like jumbo packets/ packet size limitations like QoS), DHCP going wrong so traffic awaits time-outs all the time before choosing a secondary path or protocol, then protocol settings (unnecessary protocols like IPX/SPX, NetBEUI/NetBIOS, IPv6 or IPv4). Then address resolving slowness issues (Wins/Windows-DNS. Then traffic storms, faulty switches and network-cards, unconfigured devices on the network, detected/undetected wrong cable-configuring, then fileshare settings, self-referencing network paths in remote desktop, corrupted disk-i/o, overheated components trying to survive in not working cooling, cpu/memory load at both sides, corrupted/outdated malware stuff. It???s complicated.

Rob

??

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Sent: vrijdag 5 juli 2019 23:05
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] database very slow on network

??

??

hi Sarah,

I see no one else has jumped in. This is not the right forum to be asking about network speed, although some here may happen to know ...

You didn't mention if you are on a local area network or wide area network. A consideration is how much RAM (random access memory) your machine has. Increasing RAM is one of the best things you can do to enhance machine performance, but the problem could be how you are cabled if local area network, the speed of switches and routers between your machine and the machine with the data, and type of cable. Video cards also have memory to process screen images, and this is another way to increase performance.

To see your machine specs:
From Access or any Office product, click "About [Product]" from File, Account, and then System Info...

Sorry I can't be more helpful.

have an awesome day,
crystal

On 7/4/2019 1:05 PM, sarahk [MS_Access_Professionals] wrote:

The path is very short.

??I realize that local is faster, but the network is so slow, every screen takes a few minutes to load that?? I think it most be some setting on this network. I have installed on other networks and its fine.

Read something about 'Opportunistic locking in the windows file sharing layer ...', but its too technical for me.

??

??

Sarah

---In MS_Access_Professionals@yahoogroups.com, strive4peace wrote :

hi Sarah,

Local will always be faster. One thing you can do to enhance performance is to use a short path. Where is the drive physically located in relation to where you are? And do you know the path of the cables?

a couple suggestions to help performance:

Use short primary and foreign keys like AutoNumber/Long Integer as opposed to text fields with big Size

Build indexes on fields that are used for searching

have an awesome day,
crystal

free code you can use in your projects
https://msaccessgurus.com/code.htm

On 7/3/2019 2:08 PM, sarahk@... [MS_Access_Professionals] wrote:

I just installed a new application: MS Access 2003, split. When I run it on a network drive it is extremely slow, but when I run the exact same application on the local drive it is very fast. I had to move the front and back end to the local drive, just moving the front end did not improve the performance. its a small db , less than 3MB for now.

??

What could be the reason?

??

??

All help is appreciated.

??

Thanks??

Sarah