Microsoft Access

Source: WIKIPEDIA

Microsoft Office Access, previously known as Microsoft Access, is a database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of applications, included in the Professional and higher editions or sold separately. On May 12 2010, the current version of Microsoft Access 2010 was released by Microsoft in Office 2010; Microsoft Office Access 2007 was the prior version.

MS Access stores data in its own format based on the Access Jet Database Engine. It can also import or link directly to data stored in other applications and databases.^[1]

Software developers and data architects can use Microsoft Access to develop application software, and "power users" can use it to build software applications. Like other Office applications, Access is supported by Visual Basic for Applications, an object-oriented programming language that can reference a variety of objects including DAO (Data Access Objects), ActiveX Data Objects, and many other ActiveX components. Visual objects used in forms and reports expose their methods and properties in the VBA programming environment, and VBA code modules may declare and call Windows operating-system functions.

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 the SQL programming 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.^[23] 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.^[24] 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.^[25] 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).^[26] 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^[update] 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^{[citation needed]} 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. ^[27] 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). ^[27] 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. ^[27]

Microsoft Access offers several ways to secure the application while allowing users to remain productive.

The most basic is a database password. Once entered, the user has full control of all the database objects. This is a relatively weak form of protection which can be easily cracked.

A higher level of protection is the use of workgroup security requiring a user name and password. Users and groups can be specified along with their rights at the object type or individual object level. This can be used to specify people with read-only or data entry rights but may be challenging to specify. A separate workgroup security file contains the settings which can be used to manage multiple databases. Workgroup security is not supported in the Access 2007 and Access 2010 ACCDB database format, although Access 2007 and Access 2010 still support it for MDB databases.

Databases can also be encrypted. The ACCDB format offers significantly advanced encryption from previous versions.

Additionally, if the database design needs to be secured to prevent changes, Access databases can be locked/protected (and the source code compiled) by converting the database to a .MDE file. All changes to the VBA project (modules, forms, or reports) need to be made to the original MDB and then reconverted to MDE. In Access 2007 and Access 2010, the ACCDB database is converted to an ACCDE file. Some tools are available for unlocking and "decompiling", although certain elements including original VBA comments and formatting are normally irretrievable.