Microsoft Access - Development

Development

Access stores all database tables, queries, forms, reports, macros, and modules in the Access Jet database as a single file.

For query development, Access offers a "Query Designer", a graphical user interface that allows users to build queries without knowledge of structured query language. In the Query Designer, users can "show" the datasources of the query (which can be tables or queries) and select the fields they want returned by clicking and dragging them into the grid. One can set up joins by clicking and dragging fields in tables to fields in other tables. Access allows users to view and manipulate the SQL code if desired. Any Access table, including linked tables from different data sources, can be used in a query.

Access also supports the creation of "pass-through queries". These snippets of SQL code can address external data sources through the use of ODBC connections on the local machine. This enables users to interact with data stored outside the Access program without using linked tables or Jet. Users construct the pass-through queries using the SQL syntax supported by the external data source.

When developing reports that are linked to queries placing or moving items in the design view of the report, Access runs the linked query in the background on any placement or movement of an item in that Report. If the report is linked to a query that takes a long time to return records this means having to wait until the query has run before you can add/edit or move the next item in the report (this feature cannot be turned off).

Non-programmers can use the macro feature to automate simple tasks through a series of drop-down selections. Macros allow users to easily chain commands together such as running queries, importing or exporting data, opening and closing forms, previewing and printing reports, etc. Macros support basic logic (IF-conditions) and the ability to call other macros. Macros can also contain sub-macros which are similar to subroutines. In Access 2007 enhanced macros with the inclusion of error-handling and of support for temporary variables. Access 2007 also introduced embedded macros that are essentially properties of an object's event. This eliminated the need to store macros as individual objects. Macros however, are limited in their functionality by a lack of programming loops and of advanced coding logic. Most professional Access developers use the VBA programming language for a richer and more powerful development environment.

The programming language available in Access is, as in other products of the Microsoft Office suite, Microsoft Visual Basic for Applications, which is nearly identical to Visual Basic 6.0 (VB6). VBA code can be stored in modules and code behind forms and reports. Modules can also be classes.

To manipulate data in tables and queries in VBA, Microsoft provides two database access libraries of COM components:

1. Data Access Objects (DAO) (32-bit only), which is included in Access and Windows and evolved to ACE in Microsoft Access 2007 for the ACCDE database format
2. ActiveX Data Objects ActiveX Data Objects (ADO) (both 32-bit and 64-bit versions)

Beside DAO and ADO, developers can also use OLE DB and ODBC for developing native C/C++ programs for Access. For ADPs and the direct manipulation of SQL Server data, ADO is required. DAO is most appropriate for managing data in Access/Jet databases, and the only way to manipulate the complex field types in ACCDB tables.

In the database container or navigation pane of Access 2007, the system automatically categorizes each object by type. Many Access developers use the Leszynski naming convention, though this is not universal; it is a programming convention, not a DBMS-enforced rule. It is particularly helpful in VBA where references to object names may not indicate its data type (e.g. tbl for tables, qry for queries).

Developers deploy Microsoft Access most often for individual and workgroup projects (the Access 97 speed characterization was done for 32 users). Since Access 97, and with Access 2003 and 2007, Microsoft Access and hardware have evolved significantly. Databases under 1 GB in size (which can now fit entirely in RAM) and 50 simultaneous users are well within the capabilities of Microsoft Access. Of course, performance depends on the database design and tasks. Disk-intensive work such as complex searching and querying take the most time.

As data from a Microsoft Access database can be cached in RAM, processing speed may substantially improve when there is only a single user or if the data are not changing. In the past, the effect of packet latency on the record-locking system caused Access databases to run slowly on a Virtual Private Network (VPN) or a Wide Area Network (WAN) against a Jet database. As of 2010 broadband connections have mitigated this issue. Performance can also be enhanced if a continuous connection is maintained to the back-end database throughout the session rather than opening and closing it for each table access. If Access Database performance over VPN or WAN suffers, then a client using Remote Desktop Protocol (such as Microsoft Terminal Services) can provide an effective solution. Access databases linked to SQL Server or to Access Data Projects work well over VPNs and WANs.

In July 2011, Microsoft acknowledged an intermittent query performance problem with all versions of Access and Windows 7 and Windows Server 2008 R2 due to the nature of resource management being vastly different in newer operating systems. This issue affects query performance severely on both Access 2003 and earlier with the Jet Database Engine code as well as Access 2007 and later with the Access Database Engine (ACE). Microsoft has issued hotfixes KB2553029 for Access 2007 and KB2553116 for Access 2010, but will not fix the issue with Jet 4.0 as it is out of mainstream support.

In previous versions of Microsoft Access the ability to distribute applications required the purchase of the Developer Toolkit, from Access 2010 the Runtime Only version is offered as a free download, making the distribution of free applications possible on Windows 7.