Microsoft Access Development (VBA, SQL)

MS Access is a good application to use, store and interact with data. Most of my projects involve an Access file holding the data (“back end”) on a networked file-server and then a User Interface (“front end”) which is also an Access file but is stored on the user’s hard-drive. These files are sufficient to comprise a system to enter and maintain data and also support the business processes associated with that data.

One can build a fairly potent user interface without using VBA (Visual Basic for Applications) programming. But including program code in the front end does a couple of valuable things.

One is the ability to add features that non-specialist programmers can maintain. Even if Access has a feature to serve a particular function, I’ll use VBA code for the function because then it can be explicit and documented.

This ability to document a user application in code is another valuable feature itself.

Structured Query Language, or SQL, is the language used to extract or modify data in many database systems. If you build a query in the Access interface, behind the scenes, Access is building an SQL statement. One can also use SQL to manage data behind the surface of a user interface, in the VBA.

I’ve been able to resolve a number of mission critical situations solely using Access. If you are interested in putting your data to work for your small (up to 20 member) team, Access could be well-suited to your need. (Twenty is not a software-imposed limit. In situations where the data are not being frequently altered, the number of simultaneous users could be 200 or more. But an Access system can support intense level of data utilization for a compact network of 15 – 20 users without performance issues.)

An Access system can be as simple as a repository for survey data. If the survey is simple, such a system could be built in a couple of days. But I have also built systems that involve multiple surveys with numerous pages each, and these might require a week just to specify, and then another week or two to build the system. I have built such a system that involved extensive user requirement (e.g. text in Mandarin) and required more than a year to build.

Please excuse me while I make an urgent appeal. If you are in contemplation of building a complex data entry system, I strongly encourage you to involve the database developer as soon as you are laying out the data on paper or a screen. I would, for example, encourage the use of numeric options if possible rather than, say, checkboxes, as the data can be entered more quickly if it is numeric.

Access is quite suitable for situations from the very simple to the very complex. I have worked with a system that was practically an Enterprise Resource Planning system and, as it had a limited number of users – really just a handful – Access was well suited to fulfill the business requirements.

Get Your FREE Quote TodayGet Started