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)

 

[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.

 

[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