Regular Expressions in VBA

A frequent question for most database platforms is how to use regular expressions in SQL queries. Regular expressions are an awesome tool for working with text and are built in to just about every widely used programming language, but are not standardized in SQL. Sometimes relational databases are employed to work on chunks of text and having regular expressions available is mighty helpful. In this article I'll show how to build regex functions callable from both VBA and within MS Access SQL queries, using the VBScript Regular Expressions library. Also see Increasing Performance of Regular Expressions in VBA for how to turbo-charge the performance of these utility functions.

Read more

What Microsoft Access is Good For

A lot of my posts here will be about how best to use Microsoft Access, from programming in VBA and Jet SQL to indexing and data application architecture.

For a lot of IT professionals the question this immediately raises is "Why?", as in "Why the heck would you ever use Access?" Searching for "Hate" and "Microsoft Access" yields plenty of results (of course "hate oracle" yields even more). From my first experience as a finance intern hacking VBA to get stuff done to later years both using and analyzing all the big boys of enterprise database development I went through my own cycle of disdain for Access. And yet I have come to firmly believe that Access has its place in the corporate IT landscape.

The most common defenses of Access (see Further reading below) revolve around low cost, quick development times, and ease of use for non-developers. These are all reasonable points and I leave it to the linked references to argue them. But two of the points that I think are most important are rarely highlighted, so I present them here.

Read more