Building Access  «Prev  Next»

Lesson 1

How to build an Access Database

This course is the first of a two-course series that introduces you to the basic features of Microsoft Access. The two courses together give a hands-on overview of Access, Microsoft's popular database program. This course is designed to provide an understanding of how to begin creating an Access database. You will learn about organizing data into tables and relating those tables so that the data in your database can be used for analysis. This course assumes no first-hand knowledge of Access, so if you are new to the Access application you will be able to tackle the modules without frustration. The next lesson covers the prerequisites for this course.
  • Course Objectives
    After completing the course, you will be able to use Access to:
    1. View and use the database windows
    2. Organize related data into multiple tables
    3. Create a blank database
    4. Create a table
    5. Enter and modify data
    6. Import data from Microsoft Excel®
    7. Import an Access table from another database
    8. Edit field properties
    9. Open and close Access database objects
    10. Get online help in Access
    11. Define relationships between tables

What is Microsoft Access?

Microsoft Access is a unique platform that enables rapid development of database applications. It has been a long-time favorite of both developers and end users for creating single-user and small-scale office automation solutions. Recent improvements have made it a viable choice for enterprise-class applications as well. In this course, I will show you how to take advantage of all the great features that Access 2010 has to offer. I think you will find it surprisingly easy to build full-featured applications.
  • MS Access is Good Choice for designing Small Accounting System
    With 5 or 10 concurrent users you can design an accounting information system. Keep in mind however that you have to implement your own multi-user conflict avoidance code. Insure that two users do not concurrently write an invoice in the same book or statement for the same bank account (because of invoice or statement number assignment). It is not challenging and pays in the long run to write your own high level locking mechanism. For the multi user part you can place MS Office on a dedicated workstation PC running a remote desktop app where every user gets a user account on that PC and connects to it using their PC’s as terminals. This works from abroad and avoids that users alter your settings.
    For an extended management and special purpose bookkeeping app for a non profit organization in MS Office 365 with automatic interacting with Outlook for mail, Word for letters and group mailings, Adobe acrobat for pdfs, multi-user, multi currency, payroll, budgets, balance sheets, membership, project, and investment portfolio management with automatic online shares and currency updates, usage log-file and error logging.
    Front end size 17 MB + 1 MB library around 60,000 lines of code, 113 forms, 61 reports, 29 classes/modules and numerous queries and it still works daily for two non profit and a few private sites (for Portfolio management). the biggest back end weights 40 MB of excluding the documents. The application stores the data in separate folders and keeps only a link to them in the database as the total size of the documentation files amounts to 1GB on one of them and would be too heavy to include into an Access database.

Working with Data

The passage you've shared provides an insightful overview of the Data Access Objects (DAO) and ActiveX Data Objects (ADO) models in the context of Microsoft Access. Here's a structured summary to highlight the main points:
  1. Distinction Between Object Models
    • The DAO and ADO object models are separate from the Access object model.
    • DAO and ADO represent objects that belong to the Access database engine (ACE or Jet), which is part of the software installed with Office.
    • Access Object Model pertains to the user interface and functionality specific to Microsoft Access.
  2. Background and Usage
    • In the past, applications like Excel (with MSQuery) and Visual Basic could directly use the Jet database engine via:
      • ODBC (Open Database Connectivity) or
      • Microsoft Query.
    • Access VBA (Visual Basic for Applications) provides a way to manipulate database objects programmatically, offering extensive flexibility
  3. Object Models for Data Access in Access
    • Microsoft Access supports two distinct object models for working with data:
      • ADO (ActiveX Data Objects)
        • Newer technology compared to DAO.
        • Based on Microsoft’s ActiveX technology, which allows objects to perform tasks independently of their hosts.
        • ADO’s object model is sparse but highly powerful, requiring only a few objects to accomplish most data access tasks.
        • Strengths:
          • Can perform a wide variety of tasks.
          • Does not interfere with Access’s functionality.
      • DAO (Data Access Objects)
        • Older model, predating ADO.
        • Built into the Access Database Engine in recent versions, which shows its continued relevance.
        • DAO objects are simple and direct but part of a more complex hierarchy.
        • Often used for direct manipulation of database elements such as tables, queries, and recordsets.
  4. Key Differences Between DAO and ADO
    Feature DAO ADO
    Age Older (predates ADO). Newer (based on ActiveX).
    Complexity Part of a complex hierarchy of objects. Sparse object model (simpler).
    Flexibility Focused on database engine tasks. Can handle diverse data access tasks.
    Integration Reintroduced with the Access Database Engine. Works independently of Access.
  5. Blurring the Lines Between Access and DAO
    • Access’s user interface can make it unclear which objects belong to Access itself and which belong to the database engine.
    • In VBA code, developers must explicitly distinguish between Access and database engine features.
    • Example: Certain properties and methods that appear to belong to DAO or ADO might actually be Access features (and vice versa).
  6. Development Considerations
    • When working with VBA, you should:
      • Understand the hierarchies of ADO and DAO.
      • Be mindful of overlapping features provided by the Access object model and the database engine.

Conclusion: Both ADO and DAO provide powerful tools for data manipulation in Microsoft Access. Choosing between the two often depends on the specific requirements of your application:
  • Use ADO for lightweight, versatile, and modern data access tasks.
  • Use DAO when working closely with Access’s database engine for direct manipulation of database structures.

In the next lesson, we will examine the course prerequisites.

SEMrush Software TargetSEMrush Software Banner