Performance Tips to speed up your Access database

[isladogs]

The link is to an extended article written by Luke Chung from FMS.
https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/dd942824(v=office.12)#access-modulevba-performance-tips

Although written several years ago, it contains the most comprehensive advice I have seen in one article for improving the performance of each part of your databases. Well worth bookmarking

Another very informative article on improving performance is this extract from the Access 2002 Developers Handbook https://docs.microsoft.com/en-us/previous-versions/office/developer/office-xp/aa188211(v%3doffice.10)

 

[Uncle Gizmo]

I saw Luke Chung present at the Microsoft Access Users Group at the Microsoft campus in Reading a few years back. Very interesting presentation about the problems imposed on MS Access by company IT departments. I kept a copy of the presentation he used at the meeting and asked him if I can share it. It's here if you want to have a look:-

Where Access Fits in the Overall Database Strategy of an Organization - By Luke Chung

Basically what it boils down to is that MS Access cannot be beaten on its return on investment (ROI)... I'm not 100% sure if Luke actually said this, but this is what I recollect, basically one of the issues is that the boss, or a department head will come up with an MS Access solution, it may well become unwieldy and in need further expansion. Who does he turn to? The IT department. The IT department don't see the value of MS Access, in fact they see it as a pain for this very reason! They don't want to have to Wade in and sort out the boss's database. The IT department can avoid this problem by banning MS Access in the organization! A rather short-sighted solution.

 

[zeroaccess]

This is great information.

Do you guys see anything that has changed with Access 2013, 2016, or 2019 with regard to the items discussed in the article? I just want to make sure I'm using best current practices.

 

[isladogs]

Most if not all details still apply apart from :
- ACCDE instead of MDE
- avoid ActiveX controls as far as possible as many organisations block these

The article was written before a 64-bit version became available with Office 2010.
Although 64-bit is now the default version, there is no advantage to using 64-bit for Access apps
However there are lots of disadvantages including certain older reference libraries don't work in 64-bit and all API declarations need updating

Other than that, I can't think of any obvious omissions at this point.

 

[zeroaccess]

I asked about this then second-guessed it based on some of the responses...but what about the following discussion about persistent connections?

https://www.access-programmers.co.uk/forums/threads/addressing-performance-issues.291269/

I don't see it addressed in the article.

 

[isladogs]

Yes. Having a persistent connection is important.
I'd be surprised if that's not covered in either of the links in post #1

Lots of good advice in the thread you linked in post #5

For more tips about improving performance, see this article Optimising Queries
Its one of a series of Speed Comparison Test articles

 

[Deleted member 147267]

If one thinks that article is worth the information, you should probably copy the content. MS documents about unsupported application versions have a way of disappearing.

 

[gemma-the-husky]

That Luke Chung article really encapsulates a lot of my thoughts about Access.

One observation I do have (which might be covered in the article) is that although IT departments may despise Access, they willingly allow users to develop mission critical but poorly designed excel spreadsheets. Spreadsheets where data in columns can be blank, erroneous or badly formatted, where data can be incomplete without anyone realising, new rose can be added without formulae being adjusted to include the data, and where calculation errors can proliferate without being detected.

 

[Uncle Gizmo]

they willingly allow users to develop mission critical but poorly designed excel spreadsheets.

I've never understood why they think MS Access is a security risk. I mean, it's a Microsoft product on your desktop PC, just like Excel Word and the rest! What is so different about MS Access that makes it a security risk?

 

[zeroaccess]

I've never understood why they think MS Access is a security risk. I mean, it's a Microsoft product on your desktop PC, just like Excel Word and the rest! What is so different about MS Access that makes it a security risk?

I've heard this at my work, too. The managers just repeat what they heard and have this idea that there are "security issues". I asked them to cite these issues. I never get a response.

 

[GinaWhipp]

Hmm, I'm wondering if it's because people don't normally store PII information in a spreadsheet but don't hesitate when it's a *database* which is on their local machine with no thought of who can see that information.

 

[RogerCooper]

I recently upgraded from stand-alone Access to Microsoft OfficeProPlus. This resulted in huge improvement in stability, especially in multiple-user situations.

 

[isladogs]

As Access is the same whether purchased as a standalone product or as part of Office, there should have been no change.
Perhaps the reinstallation, fixed some issues such as registry settings.

 

[zeroaccess]

As Access is the same whether purchased as a standalone product or as part of Office, there should have been no change.
Perhaps the reinstallation, fixed some issues such as registry settings.

That, or maybe they had the Runtime version which had issues with some aspect of the design?

 

[RogerCooper]

The improvement may have been caused by moving to the latest version of Visual. We were on Access 2013 before. The tip here is that upgrading to the latest version can improve performance.

 

[isladogs]

That may be your experience but a lot of people would say the exact opposite. It can also make performance a lot worse due to the various bugs in recent Windows 10 &Office 365 updates

BTW The current version of VBA is still v7. It hasnt been updated since A2010

 

[Pat Hartman]

I have several clients who have developed (or had developed for them) all of their line of business applications in Access. Most of them use SQL Server as the BE because it is more stable and secure. But the FE's are Access. The clients tend to purchase accounting systems which Access can generate feeds for. These clients have < 200 employees and probably no more than 100 concurrent users but once the BE is SQL Service, the concurrent user count is dependent on the number of seat licenses for SQL Server rather than Access.

I've also developed departmental applications for many fortune 500 companies. In most cases, I had trouble with IT getting them to agree to allow me to use SQL Server to hold the data. They all pretty much despised Access but interestingly, their complaints were all complaints about Jet/ACE and they of course couldn't in their minds separate Access from Jet/ACE and they were convinced I wouldn't be able to support 100 users without a problem. They of course were wrong.