ABC and JET database engine

ABC uses the Microsoft JET database engine to save and retrieve data. The data is stored in the .MDB file located on the computer we call the "server". This is not a true server because the .MDB is not an active database controller; it is simply a file of data. Sometimes called a File Server database.

• JET is now renamed ACE by Microsoft, and also known as the ACCESS database engine as it is used by MS OFFICE and VBA.

The work of selecting and processing data is done locally on each PC by its own copy of the JET database engine controlled by ABC. If ABC requests even a single record from a file, the JET engine may have to bring the entire file across the network to the local PC in order to work on it to select the one record. If the file is large this could be slow (it depends on the speed of the network connection).

• By comparison, an "active" server has an database engine (program) located on the server computer with the data. Requests are sent to that engine, and it returns just the required data. For example if you request one record, then only one record needs to be returned over the network.

It might seem that using a proper active server is a much better system (and it can be -- for LARGE databases with thousands of users), but it isn't a simple comparison. For various reasons this can actually be slower than the simple File Server used by ABC (eg. JET is very efficient at caching data so often only the first request needs to use the network; also an active server may be busy dealing with other requests or internal housekeeping leading to delays in processing your request).

Conclusion. We keep checking, but at present the system used by ABC is still regarded as the best solution for up to 200 users, and databases less than 2GB in size. With fast network connections, there should be little speed difference between a single-user ABC and a multi-user system.

We tested this

To investigate factors influencing network speed we used 3 computers, Windows 10 , Windows 7, and XP. Connected together first with WiFi, and then cable. Tried each PC in turn as the "server". Testing done with a large ABC database (150Mb, approx 200,000 Sales Transactions).

Summary of Results

There was not much difference between any of the computers (ie. using XP as server or Win10).

Workstation PC's connected by cable run ABC faster and more reliably than those connected by WiFi (although WiFi worked well for us. But in areas suffering radio interference, cable is much better).

For best results

• Use cables rather than WiFi.

• It is a bad idea to have one PC set aside as the "server" and not used for running ABC, because that means all the workstations have to fetch the data across the network (ie. slower). Running ABC on the same PC as the data files will always be faster than from another workstation (maybe only slightly faster with small files). Any intensive data operations (Compacting the database, Monthend, Backup, Snapshot, Check Account Files) will be faster if done from the server PC rather than from another workstation.

• The network is used not only by ABC but also by any other programs running on the computers. Therefore try not to put the ABC Database (server file) on a PC that is also used for internet browsing, emails, faxes, cloud backups, etc, etc, or those operations could slow down all ABC users.

• Smaller files allow ABC to run much faster over a network. ABC includes a utility to remove (safely) old unwanted history (under your control) to make files smaller.

---

Speed up tips for ACCESS

Here are some suggestions from people who had slow network response using MS ACCESS (based on JET engine) and who said one or other of these speeded it up for them.

NOTE: TRY THESE AT YOUR OWN RISK. We tried them all and did not notice a dramatic speed increase.

1. Increase buffer size for database connections by changing Registry settings. For both the following, change MaxBufferSize to 50000 (decimal). (on your PC, some numbers may be slightly different, eg Office 12 instead of 14)

      32 bit Windows
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0] [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE]

      64 bit Windows
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0] [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE]

2. Possible conflict between IPv6 and IPv4 in the TCP settings of the network card using Win7. Go into the properties of your NIC card and untick the box Internet Protocol Version 6 (tcp/IPv6)

3. Temporarily disable antivirus or firewalls from actively protecting the network location where the database file is. If it makes a difference, you can look at configuring the security software to avoid the delays.