Don’t get me wrong. Access VBA can perform very well in most situations. There have been a few times where I’ve needed to improve the speed of some function I was using and Access just took too long.

One of those is hashing values for files. There is a hashing algorithm out there for VBA that will create an MD5 string from the contents of a source string. The shorter the string, the faster it performs. But getting the file contents into a file or system stream object and then processing it using the function takes a long time the more characters that are in the file. And it’s not on the order of milliseconds, but in the order of seconds. This can be ok for a file, but if you’ve got a whole folder of them you are comparing to stored file hashes in your db to see if they’ve changed, that just takes too long when you’ve got 20 files and each one takes 2 seconds.

In this case, I used DevHut’s Daniel Pineault’s VBA PowerShell Function to utilize a PowerShell command to return a string of all the file hashes for files in a folder and just parse that. The function itself takes a couple seconds to run, but it is much faster for getting a folder’s worth of hashes.

Other things that require cryptography tend to be slow if encrypting or decrypting large amounts of data, or trying to do constraint modeling realtime for problems like auto routing and auto populating schedules and conference brackets. Sometimes you just have to use an API to another app or run the calculations separately and store the results for later use. Have you ever run into anything like this?