I'm trying to increase the speed of some of my forms, I heard inserting the form code into modules saves speed, if this is true how many modules should you use? - I would have thought the more the better?
Also which is faster -> bound controls or unbound controls with values assigned on load.

Performance of Access forms depends on three fundamental areas, the speed of the connection to the data together with the size of the data, the amount of memory available vs the amount needed (data size is again an issue) and the number of processor cycles required to run necessary events in the context of the speed of the processor.

Assuming a multiuser environment with a backend database on a LAN connected server, using a 100 Mbit LAN, most 486 computers can process the incoming stream faster than the LAN can send them so a great deal of thought must go into addressing the volume of data must be transferred over the LAN. This is especially true in a client server environment using an Access application against an Access database.

After that issue, the matters of memory and processor are orders of magnitude less important. Processors have always been faster than the ability of hard drives to serve them so even if a database is a single user on a machine that has the database on a local harddrive, the size of the data is still a bigger factor than processor speed. As you run out of memory, the disk swap file then becomes a limiting factor, but never as limiting as a LAN connection. A few orders of magnitude less important are processor optimizations (with the exception of certain large multilevel nested loops).

Changing code behind a form to code in a module will make little difference. When a module procedure is referenced, the entire module is loaded into memory and it is not released until Access closes. Sharing code commonn between several forms will mean the common code is loaded with fewer instances consuming memory and this impacts the time to load from disk and the total memory used.

I would start optimizing by looking at the data.

Ciao
Jurgen Welz
Edmonton AB Canada
[email protected]

You mention client/server scenario. I am in the same situation.
I wonder if anyone is operating MS Access in a Server/Terminal server configuration i.e. the MS access front end operates on the Server along with the back end data. The workstation operates as a terminal. Therefore only screen data and input data is networked all the processing and data work is carried out on the server.
Has anyone found this to be better?

Cheers Ray

One of my clients runs such a configuration. 20 users, 5 satellite offices all connected to a server with a single private DSL line. A few of the users may connect by VPN when not in one of the offices. All 20 users have a thin client terminal in the office, there are 5 workstations and an additional 6 laptops. The server hosts all MSOffice applications plus mail and some additional software for the 20 users. The deployment initially used 128K ISDN lines, NT4 Server with 1 Gigabyte of RAM and WinCE terminals (NT4 on workstations/Laptops) running Office 97. Performance was marginal with the number of users. The upgrade to DSL made the application entirely viable. The system is now being 'upgraded' to Office 2003 and hosted by Win 2003 Server where I have access to a test server running this configuration.

I had written some custom drag and drop code that worked very well over a LAN but the screen performance is quite choppy in a Terminal environment. We also had a bug in the terminal configuration that degraded screen performance when Outlook or Access was once loaded. Former terminals did not have this issue but even with them, trying to extend a selection in Word or Excel often resulted in the screen scrolling thousands of rows or columns (or a dozen pages) before the user could react to the scroll. Powerpoint (and any graphics based programs really sucked with the ISDN connection) and if you try to run an MPG file, in a 30 second clip you might get 5 'screen shots' to a movie. Of course there is no sound and color depth is limited to 256 bit. A really huge issue for us was that there is no ability to transfer files between a terminal client computer and the server but I understand that this is a limitation of Microsoft's implementation of Terminal Services. Apparently other vendors have this capability.

Overall, there is no doubt that the data retrieval is very fast and that complex queries are many times faster than the conventional LAN environment from which the system was upgraded. It was necessary to have a server in each of the five offices and use a two level synchronization to get updates from laptops to their servers and from the servers to a consolication server. For most users who were connected only to their local server, no real difference in performance was noted, but in that case, no office had more than 6 concurrent users whereas the new environment hosts more than 20 (terminal plus a few workstations on occasion) to a server.

How well such an environment will perform is subject to many variables. I had initally written the application to cache much data locally (getrows to populate arrays to fill lookups with callback functions) knowing that caching to virtual RAM on a local machine was many times faster than the LAN. This approach is counterproductive in a Terminal environment.

Another alternative is to host an Access application on an ASP server which also makes it 'client-server'.



Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

Thanks Jurgen for such a comprehensive reply.

Our system will be a Win 2003 server with about 10 workstation (PCs and Laptops) on an office LAN and some remote users. I will look more deeply into the Terminal Server alternative.

Cheers Ray Pinnegar

Cheers Ray