Ms Access - backend speed Inprovment (1 Viewer)

Number11

Member
Local time
Today, 06:41
Joined
Jan 29, 2020
Messages
607
Hi,

I am trying to gain the understanding on how changing these settings can improve the speed..
1649763564161.png
 

Minty

AWF VIP
Local time
Today, 06:41
Joined
Jul 26, 2013
Messages
10,355
None of them will make much difference I think unless you have network issues.
Have you altered them to see on a test database?

A hardwired gigabit network connection will make the biggest difference, as will a good network infrastructure, assuming a local backend datafile.
 

Number11

Member
Local time
Today, 06:41
Joined
Jan 29, 2020
Messages
607
None of them will make much difference I think unless you have network issues.
Have you altered them to see on a test database?

A hardwired gigabit network connection will make the biggest difference, as will a good network infrastructure, assuming a local backend datafile.
Thanks we now have many users connected through VPN, so we are experiencing slowness caused by home working and VPS.
 

Minty

AWF VIP
Local time
Today, 06:41
Joined
Jul 26, 2013
Messages
10,355
VPN is not a great way to connect to Access as you have discovered.
Unless you have a solid gigabit connection between the user and the host it will be painful to use.

With many users I would implement a remote desktop solution, this will mean the end-user logs in to a per-user dedicated RDP or RDP application shortcut that opens on a remote machine that is local to the server network. Each user has their own folder.

Speed will then not be an issue, but your RDP server will need sufficient resources to cope with the number of concurrent users.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 02:41
Joined
Apr 27, 2015
Messages
6,286
With many users I would implement a remote desktop solution, this will mean the end-user logs in to a per-user dedicated RDP or RDP application shortcut that opens on a remote machine that is local to the server network. Each user has their own folder.
We use CITRIX with a SharePoint BE. It isn't ideal but the speed and reliability is much better.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:41
Joined
Feb 19, 2002
Messages
42,981
Using a VPN with a Jet/ACE BE can be painfully slow and you can't fix it. Remote desktop (via VPN) is fine as is Citrix but having the Access FE on your local computer and using the VPN to link to the BE is not a good solution.

If you can't implement Remote Desktop or Citrix, then the best solution is to convert the Jet/ACE BE to SQL Server. This will cause other problems if your FE is not designed to be used with a server based BE. For starters, if your forms are bound to queries without WHERE clause or directly to tables, you will be asking SQL Server to download the entire contents of a table each time you open a form. Think about how expensive this is over a LAN let alone over the internet and encumbered by a VPN on top. At best, your internet connection is about 10% the speed of your average LAN so the VPN will always be slow. The only way to improve that is to reduce the number of rows downloaded when the form opens.

To speed up forms, you need to add search boxes where you type in the "ID" you are looking for, You can use combos, but you should probably use local tables to populate the combos which adds another layer of pain. You can also use search forms that let you specify critera, again, you probably want to keep the tables that populate the combos local. You can populate the local tables each time the app opens and you should also add a refresh option to refresh them on the fly.

Applications designed to work with SQL Server et al will work just fine with Jet/ACE so you don't have to use two design patterns. Just design for SQL Server and Jet/ACE will be just fine.

Think about what it would take to populate the table/s that power the combos. Even without converting the BE, that might give you an immediate boost if that data is downloaded just once when the app opens and then refreshed during the day if necessary. That change alone may give you some breathing room. For me, this is actually trivial since all my combos of the straight code value variety exist in a single table. Therefore, in addition to the table/s of simple codes, I would need to download a trimmed down version of the customer table with just the ID and name, and the same with the orders table and the employees and whatever other combos I need to populate in addition to the simple "code" tables like maritalStatus, Gender, Race, OrderType, etc.
 

Users who are viewing this thread

Top Bottom