This is an intermediate-level quiz and tutorial on integrating Excel macros (VBA) with Access for databases, a major spreadsheet software that runs on Windows.
This is the final part of a trilogy of intermediate courses. (Part 1: Data Collection, Part 2: User Forms)
This course covers Excel 365, versions 2024-2007, a major spreadsheet software that runs on Windows.
(Trademark Information)
Microsoft Excel and Microsoft Access are registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
VBA (Visual Basic for Applications) and Visual Basic are registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
(Disclaimer)
The examples in this course will work even if Access is not installed, but since they will be used during the learning process, this lecture assumes that Access is already installed.
(Reference)
Access is available with some subscription plans (Personal, Family, Business, etc.) of Microsoft Corporation's Microsoft 365. (Please check before subscribing.)
■Question Scope and Course Content■
In the Basics section, you will learn the fundamental knowledge and know-how for database integration.
In the Practical section, as a case study, we will convert the Excel list-format sheets from "Intermediate Course I: Data Calculation" and "Intermediate Course II: User Forms" into database (Access) files and use them as database integration versions.
Finally, we will attempt multi-user support research such as file sharing.
■Quiz Questions■
Evaluation will be based on the following four levels:
100 points: Very good.
80 points or less: Good.
60 points or less: Keep trying.
0 points or less: Keep trying.
If you achieve a perfect score of 100 points on all subjects, you will be issued a certificate.
Only the certificate displayed in the app is official.
Try the quiz questions to earn your [certificate]!
■Course Overview■
(Reference)
This course assumes that you have already taken the following courses offered by our school.
・Intermediate Course I: Data Aggregation
・Intermediate Course II: User Forms
1. Database Routine Processing Patterns
Databases are often described as being intimidating, difficult to understand, and challenging.
We'll cover the technical details of databases in a separate chapter later.
First, let's begin by gaining an overview of what a database is through "Database Routine Processing Patterns."
2. User Forms: Address Book (Database Version)
We will modify the code for "Intermediate Course II: User Forms: Address Book" to support a database version that connects to a database file based on the "Database Routine Processing Patterns" above.
3. Table Creation and Integrity Constraints
We will explain how to create tables and the associated integrity constraints.
4. Queries and SQL
We will introduce commonly used queries and SQL statements.
5. Data Aggregation (Database Version)
We will modify the code for the example from "Intermediate Course I: Data Aggregation" to support a database version that connects to a database file based on the "Queries and SQL" above.
6. Multi-User Support - Research
This course introduces multi-user support using database files.
(Disclaimer)
The multi-user support examples presented in this course do not reproduce all possible scenarios, nor have they been 100% tested for compatibility. Test patterns vary widely depending on the PC environment, program specifications, and operational conditions. Please consider the examples presented here as reference only. This is why the chapter title includes the word "Research." If you intend to tackle this as a real project, please conduct your own research, using this course as a reference.