Microsoft Access VBA (Visual Basic for Applications)

Resource Logic Client/Server Development Services

Legacy Maintenance

Microsoft Access was first released in 1992. The original version ran in Windows 3.1. Based on it's product positioning, it was to be used as a 'departmental' database where managers and administrative assistants could create their own tables, forms, and reports. With the inclusion of Visual Basic for Applications (VBA), a combination of fully relational database and general purpose programming language, the platform was soon used far more aggressively. With the emergence of Access 97, it was possible to create full blown Enterprise Resource Planning (ERP), Customer Relationship Management (CRM), or enterprise-wide custom applications. A number of companies created sophisticated (for the time) vertical market applications, in some cases superceding earlier versions written in QuickBasic, Foxpro, or C++.

Versions of Access from the mid-1990s onwards included a 'development kit' which enabled programmers to create stand-alone packages. This meant the database could be bundled with a distribution package and installed on an arbitrary number of computers without an Office license. This divorces custom or third party applications from the current Office environment, therefore someone running Office 2007 in Windows XP may still be using an EDI (electronic data interchange) package written in Access 97. Over time, users and developers may lose track of the application source code or installation package. This, in turn, creates the opportunity for a severe problem in a circumstance that might otherwise prove to be incidental.

The versions of Access that predated 2000 used the .MDB data file format and ODBC to integrate to third party databases (or non-database import/export files). The release of Access 2000 introduced a new file format: the .ADP, or Access Data Project. This was used for exclusive integration with client/server databases, such as SQL Server or Oracle. At the time, .MDBs were notorious for performance problems when shared, often only three to five users could share a database. SQL Server could easily integrate 60 simultaneous users.

With Office 2007 and 2010, the .ACCDB file type was added. While Office 2007 still supports .MDBs and .ADPs, the new format is focused on security, feature, and sharing support that has emerged with Vista, Windows 7, and SharePoint. When used with Sharepoint, the Access database becomes, in effect, a web-enabled application.

Unlike, say, ASP or VB6, Access applications have a direct upgrade path into a state of the art product. However, this has its pitfalls. Some VBA code from Office 97 will not recompile into later versions, although the issues are minor and easily fixed. Access 2010 has a '64-bit' version, which allows one to create programs that could fit in eight gigabytes. The difficulty with this is related to third party add-ins, these probably will not work with the 64-bit version. The more recent versions of Access integrate with Visual Studio .NET, however as of this point VS 2010 does not support 64-bit computing in the CLR (Common Language Runtime). 64-bit computing is supported with C++.

SQL Server is a preferred database engine for Access, and the most recent versions of SQL Server Express (2008 R2) support databases of 10GB. A 64-bit version of Access running on top of a 10GB database on a upscale laptop is a potent analytical engine. Not a bad platform for crunching some numbers on an overseas plane trip.

If you have any questions, you may eMail us at Info@ResourceLogic.Net