Getting started with ms-access
Remarks#
Apologies for the wall of text. Future edits will likely add screenshots and other visual elements.
Queries in Microsoft Access can be created by any one of four methods
- Using a step-by-step Query Wizard builder through the GUI which will ask you a series of questions about what data you wish to display and how various bits of data are related to each other.
- By using the GUI in Design View, in which you select tables and specific fields with your mouse. The visual ordering of the various fields can be specified by dragging the relevant columns in the bottom panel and additional properties for each field can be specified in the Properties panel (right).
- By switching from Design View to SQL View and specifying the raw SQL query code. Under most circumstances you can freely toggle between Design View and SQL View - Access will incorporate changes that you made in one view to the other. The syntax of Access SQL is similar to, but not identical to, that used in MySQL, PostgreSQL, Oracle, or MS SQL Server (tSQL).
- Using Visual Basic for Applications programming which can be accessed via the Macro group of the Database Tools ribbon (Access 2007+). Database manipulation occurs via the ADO or DAO libraries and uses the same syntax as SQL View in the main application, with the exception that various special characters have to be “escaped.” Queries created via this method are not accessible directly from the Navigation Pane but must be placed in a function or subprocedure and either triggered by other elements (e.g. by a button in a Form) via Macros or directly executed in the VBA GUI interface.
Editing a record value from a query in datasheet view will result in a change in the underlying record value, assuming the query field is not an aggregation or concatenation of multiple sources of information.
Forms and Reports can be used to display information from queries in a form alternative to a simple “Datasheet” view which appears similar to an Excel-style spreadsheet. Forms are targeted to on-screen display, whereas Reports are targeted to those printed on paper.
Installation or Setup
Microsoft Access is one of the Microsoft Office suite of programs. However, it is only available in some packages of MS Office.
If you wish to obtain Access, please make sure to carefully examine the box or download specifications for each version of Microsoft Office. MS Access is only available for Windows PCs, it is not available on Macintosh systems in the native environment, even through other MS Office programs may be available. Similarly, it is not available for linux operating systems.
In Office 365, Access can be found in the Home, Personal, ProPlus, Enterprise E3 or E5 versions, but not Enterprise E1 nor Business (or B. Essentials, B. Premium).
In Office 2016 it is not included in the Home & Student or Home & Buisness packages, but it is in Professional. It does not appear to be in any versions for the Macintosh.
What is MS-Access and what do we use it for ?
Microsoft Access is an Application Generator for developing databases and data-driven applications, primarily for local use. Microsoft Access consists of two main elements:
- A Relational Database Management System (RDBMS) that combines the Microsoft Jet Database Engine (Access 2003 and earler) or the Access Database Engine (Access 2007 and later; see below) with graphical management tools. A unique Linked Tables system allows remote tables to be treated as local.
- Graphical User Interface (GUI), and software development tools, supported by Visual Basic for Applications (VBA) that can reference a variety of objects.
It is a member of the Microsoft Office suite of applications, included in the Professional and higher editions or sold separately. Database applications that have been created with a full version of Microsoft Access can be compiled for distribution and run via a free Microsoft Access Runtime.
The two elements allow Microsoft Access to be used in various ways:
- As a Database: A Microsoft Access database does not call for a Data-Server, and is often used as a database for local applications, such as a web-site database, located on the web-server.
- As a Data Application Generator: Tools for creating GUIs containing Forms and Controls bound to (local or linked) tables allow developers to create local applications for accessing and managing local or remote data. VBA modules allow developers to create capabilities not supported by GUI tools.
- As a full Application Generator: The above abilities allow developers to create full local data applications in one or more Access files.
Microsoft Access Database Engines
Through Access 2003 (11.0), the built-in database engine was Microsoft Jet. With Access 2007 (12.0), Microsoft introduced a new descendant of the Jet engine, the Access Database Engine (originally called the Access Connectivity Engine and still commonly known as the ACE Engine), and made it the default for new databases. Its feature set and behavior overlaps incompletely with the last version of Jet (4.0). Versions of Access released since have been able to create and work with databases in either Jet (.mdb) or ACE (.accdb) format, even though Jet has been officially deprecated as a technology.
Versions
Microsoft Access has existed since 1992, and older versions continue to see regular use when business-critical database applications have been built on them. A very comprehensive resource summarizing the release history (with links to release notes, where available) is: