MS Access: A Brief History

Brief History of MS Access

• Microsoft introduced the first Access version 1.0 in late 1992 as a database application for Windows.
• Version 1.1 quickly followed in mid-1993, improving compatibility with other Microsoft Office products.
• Version 1.1 introduced the ability to code using Access BASIC.
• Version 1.1 had bugs and performance issues, alongside Microsoft's release of Windows 3.1 and Office 4.3 Pro.
• MS Access was a desktop database application for small to medium-sized businesses needing a low-cost database.
• Disk hard drive capacity was less than 100 MB, with typical file sizes in the 100s of bytes.
• Databases in MS Access could grow very large, and files exceeding 10MB could cause performance problems due to low RAM.
• Access (version 2.0) sometimes caused data loss and corruption.
• MS Access 2.0 was feature-rich and popular among programmers, developers, and general users.
• Designing databases required patience and determination because it was not intuitive at the time.
• Microsoft launched Windows 95 in 1995, which provided a 32-bit processing system and a new GUI.
• Office 95 was released, including MS Access 95.
• MS Access 95 introduced a more robust and flexible programming language- Visual Basic for Applications (VBA).
• MS Access 95 was the least successful version because the 32-bit application was still new and the JET database engine was slow.
• MS Access 95 was the beta version for MS Access 97 (released in 1997).

Access 2000 and Beyond

• The release of operating systems like Windows 98, Windows ME, and Windows 2000/NT led to Access 2000.
• Older Access databases (MDB) needed modification for the upgraded JET engine.
• A one-way procedure would create a backup copy during the modification.
• Access 2000 introduced web integration, enabling HTML publishing.
• Security, replication, and database connectivity enhancements boosted developer popularity.
• Access 2002 and 2003 offered minor improvements and better integration with other Microsoft Office tools.
• Access 2007 was launched with a new file format (ACCDB).
• The ACCDB format supports new data types like multi-value and attachment fields.
• Access 2007 used the Microsoft Office Ribbon Bars for the GUI layout, which improved object handling.
• The new look, feel, and templates made MS Access more user-friendly.
• Features like security, replication, and Access pages were dropped in the 2007 version.
• MS Access 2007 still supports previous file formats (MDB) without those specific features.
• Database developers favored Oracle and SQL server over MS Access due to their higher power and robustness.
• Access 2000 onwards allowed connection to client/server databases and use of SQL with VBA.
• SQL and VBA enabled the use of ADO/DOA objects for better processes and results.
• Access 2010 was released in the summer of 2010.
• The enhancements in Access 2010 focused on better integration with Microsoft SharePoint Server.
• MS Access 2010 has cosmetic modifications and still uses a modified Ribbon Bar.
• Data Macros with new extension provide enhanced macro functionality.

MS Access as a Database Management System

• MS Access is a database management system (DBMS) by Microsoft that has a GUI and application development tools.
• It uses the relational Microsoft Jet Database Engine.
• MS Access is part of the Microsoft Office suite.
• Users can design tables, forms, and queries without programming knowledge.
• Software developers and data analysts can build software applications using MS Access as the backend database.
• MS Access applications are developed using VBA and other Microsoft applications.
• MS Access provides database functionality and programming capabilities.
• It allows users to create easy-to-navigate screens with forms.
• MS Access aids in analyzing large data sets and efficient data management.

Important Terms and Basic Objects

• Database File stores the entire database and is saved to a hard drive or other storage device.
• Datatypes are properties of each field, such as text, number, or date.
• A table is an object storing data in rows and columns related to other tables in the database file.
• Each column in a table must have a unique name, and you can define a Primary Key.
• Queries answer questions through selection, sorting, and filtering based on search criteria.
• Queries show a selection of data based on imposed criteria or limitations.
• Queries pull data from one or more related tables and other queries.
• Types of queries include SELECT, INSERT, UPDATE, and DELETE.
• A form is a database object used to create a user interface for an application.
• Forms allow to display live data from the table and simplify data entry or editing.
• A report is an object used for desktop databases, primarily for formatting, calculating, printing, and summarizing data.
• Reports can have customized look and feel.
• Macros are mini programming constructs that set up commands and processes in forms.
• Commands can be used for searching, moving to another record, or running formulas.
• Modules are procedures/functions that can be written using VBA.

Microsoft Access Data Types

• Short Text: Stores text, including numbers not requiring calculation, up to 255 characters.
• Long Text: Stores lengthy text or alphanumeric data, up to 63,999 characters.
• Number: Stores numeric data for mathematical calculations; sizes are 1, 2, 4, 8, and 16 bytes.
• Date/Time: Stores date/time for the years 100 through 9999 and takes up 8 bytes.
• Currency: Stores currency values and numeric data with one to four decimal places and takes up 8 bytes.
• Auto Number: Assigns a unique number when a new record is created (used as the primary key); 4 bytes (16 bytes if set as a Replication ID).
• Yes/No: Stores logical values Yes and No using 1 bit.
• Attachment: Stores files, such as digital photos, with multiple files per record (up to 2 GB).
• OLE Objects: Stores audio, video, and other Binary Large Objects (up to 2 GB).
• Hyperlink: Stores text/numbers as hyperlink addresses; each part can store up to 2048 characters.
• Calculated: Creates an expression using data from one or more fields.

Differences Between Access and Excel

• Access manages text, numbers, and files, while Excel generally works with numerical data.
• Data in Access is stored once in one place, Excel has separate worksheets or documents with similar/repeated data.
• Access can build data entry forms and report templates; Excel only has the primary data entry screen.
• Data entry in Access can be more efficient and accurate, and Excel may have format limitations.

Advantages of MS Access

• Provides a fully functional relational database management system quickly.
• Imports data from multiple sources.
• Offers easy customization.
• Functions with other Windows-based development languages.
• Performs challenging office/industrial database tasks.
• Allows linking, viewing, updating, querying, and reporting of data in its existing location.
• Facilitates the creation of tables, queries, forms, and reports and connects with Macros.
• Macros: simple programming construct that adds functionality to databases.
• Performs heterogeneous joins between data sets stored across different platforms.

Disadvantages of MS Access

• Suitable for small-to-medium-sized businesses, not larger organizations.
• Lacks robustness versus DBMS systems.
• Saves entire database into a single file, slowing down reports, queries, and forms.
• The technical limit is 255 concurrent users, but the real-world limit is 10-80.
• Requires more learning and training.