Access is a data-centric rapid integrated development environment (IDE) featuring a file-based database engine. It is a part of Microsoft Office and the current release is at version 14 or Access 2010.
Access as a database engine
Since the inception, Access integrates with Jet, which is a Windows component. Since Access 2007, Access team has taken the ownership of Jet, since depreciated from MDAC, and renamed it to ACE, expanding the engine with new features. ACE is a file-based, serverless relational database engine. The engine will run locally on the clients and use Windows' filesystem to access the data files either locally or remotely. When multiple clients connects to a single data file, the engines along clients will use sharing locks to cooperate the access to the data. ACE supports transactions and is ACID-compliant.
A suitable comparison to make with ACE would be SQLite and/or SQL Server Compact. It is common to try and compare Jet with other server-based RDBMS, commonly SQL Server Express but this is a mistake because of the architectural differences. File-based RDBMS are designed primarily for portability and simplicity in mind. When working on Access, the users do not have to administer a Windows Service (which is also known as daemon in different contexts), they are able to work on the data just as much as they would do so with any other Office programs - open a file, edit it and close it.
While file-based database engine does supports the goal of portability and simplicity very well, it is a challenge to protect data integrity due to the nature of several clients sharing a common files. A common technique among professional developer is to split the data file, separating the table structure and data within from other objects such as queries, forms, reports and modules. The file containing only table structures and data are usually referred to as back-end while copies of the file containing forms, queries, reports and so on are then distributed among users and termed front-end.
ACE is also used in different contexts such as driving some functionality behind other Office product such as Word's mail merge or Excel's data functions. It also is an excellent choice for working on heterogeneous source - it is possible to link to Outlook mails, Excel spreadsheets, CSV, among other number of possibilities. ACE also works well as an ODBC client, interacting with any ODBC-compliant data sources such as SQL Server, Oracle, MySQL, PostgreSQL, to name a few.
Access as a development platform
Access also provides a IDE to support creation of forms and reports which in turn can contains controls and either macros or VBA to drive the logic. Controls provided by Access are especially data-centric, featuring many events and properties that help facilitate rapid development, enabling the developer to focus solely on the presentation without getting blogged down by the tedious plumbing tasks. Access also supports automating via OLE or COM, meaning it can either automate other software such as Microsoft Excel or be automated by other software. VBA also provides support for API calls and/or referencing libraries such as ADO to extend Access's functionality so there is very little limitation to what a developer can do on the Access.
Many Access objects supports binding, which enables the developer to specify a table or query and allow the engine work out the logic required for updating, inserting and deleting to the source. Likewise, many objects supports using expressions to perform calculations or lookups from different data sources. On average, it requires less time and money to develop a business solution within Access than within VB6, Visual Studio or other general purpose IDE.
Access files extensions
Access uses several files to work with.
.mdb / .accdb
This is where the Access objects, including tables, forms, reports, macros, and VBA code are contained. .accdb files was introduced in Access 2007, which continues to support .mdb natively. .accdb bought many changes to the internal changes, omitting ULS, Replication while introducing new data-types such as attachment and multi-valued fields.
.mde / .accde
This is the compiled version of .mdb / .accdb, respectively with VBA source code stripped out. Design changes cannot be made to the forms and reports. Tables, queries and macros, however, still are editable. This is not reversible.
This is a special kind of file where ACE is not loaded at all, but rather uses SQL Server as its backend. Some SQL Server object management are also enabled within ADP.
.ldb / .acclb
This is the locking file which all clients read and updates to keep other clients notified of locks they may hold on various records.
.mdw / .mda
This is the workgroup file used for User Level Security. Note that ULS is available only within .mdb.
This is .accdb file altered to work as if it was opened by a runtime version of Access. This is not a secure manner, though it may be desirable for trivial corralling.
|This page has been accessed 3,743 times. This page was last modified 11:35, 21 July 2012 by Walter Niesz. Contributions by Jack Leach, Glenn Lloyd, Dragyn and BananaRepublic Disclaimers|