Home / Data

Introduction to database management system Part 1 - 1


UNIVERSITY OF TRANSPORT

FACULTY OF INFORMATION TECHNOLOGY


Maybe you are interested!

DANG THI THU HIEN DO THANH THUY


Introduction to database management system Part 1 - 1

LESSON

INTRODUCTION TO ACCESS DATABASE MANAGEMENT SYSTEM

UNIVERSITY OF TRANSPORT

FACULTY OF INFORMATION TECHNOLOGY


Dr. DANG THI THU HIEN, MSc. DO THANH THUY, MSc. NGUYEN KIM SAO


COURSE BOOK

INTRODUCTION TO DATABASE MANAGEMENT SYSTEM


Hanoi, January 2015

INDEX

FOREWORD 5

CHAPTER 1: OVERVIEW OF MICROSOFT ACCESS 7

1. Introduction to Microsoft Office 7

2. Working with Microsoft Access 7

2.1 Start Access 7

2.2 Create a new database 8

2.3 Working with existing databases 10

CHAPTER 2: TABLES AND RELATIONSHIPS 13

1. Structure of a table 13

2. Design table 13

2.1 Create table 13

2.2. Set primary key 17

2.3 Field properties 18

2.4 Properties of table 19

2.5 Update data into table 20

2.6 Delete and rename table 20

3. Establish relationships between tables 21

3.1 Relationship between tables 21

3.2 How to create relationships 22

3.3 Editing relationships 23

3.4 Creating referential integrity 24

CHAPTER 2 EXERCISES 26

CHAPTER 3: QUERY 29

1. Overview of query 29

2. Operations on query 30

2.1 Steps to create a new query 30

2.2 Set conditions in query 34

2.3 Create calculated fields in query 35

2.4 Field and query properties 36

2.5 Math operations and some functions in Access 37

3. Other query types 43

3.1 Totals query 43

3.2 Parameter Query 45

3.3 Crosstab Query 47

3.4 Make Table Query 51

3.5 Append Query 53

3.6 Delete Query 54

3.7 Update Query 56

CHAPTER 3 EXERCISES 59

CHAPTER 4: FORM 63

1. Create a form using Autoform and Wizard 63

1.1 Create a form using AutoForm 63

1.2 Create a form using Form Wizard 64

2. Design Form in Design mode 66

2.1 Components of a Form 66

2.2 Form 68 design steps

3. Toolbox 73

3.1 Text box control 74

3.2 Controlling Combo Box 75

3.3 Command Button Control 83

3.4 Main – Sub Form Design 84

CHAPTER 4 EXERCISES 91

CHAPTER 5: REPORT 93

1. Create reports using Autoreport 93

2. Create a report using Report Wizard 94

3. Create a report using Design View 98

4. Create labels using Label Wizard 102

CHAPTER 5 EXERCISES 105

CHAPTER 6: MACROS AND MENUS 107

1. Macro 107

1.1 Creating and Using Macros 107

1.2 Commonly used Macro actions 109

1.3 Attach Macros to Events on Form/Report 109

1.4 Macro AutoExec 111

2. Menu Bar 112

CHAPTER 6 EXERCISES 115

COMBINED EXERCISES 116

CHAPTER 7: DATABASE PROGRAMMING IN MS ACCESS 131

1. Introduction to Visual Basic Application Programming 131

2. Module 132

2.1 Standard Module 133

2.2 Class Module 148

2.3 Form/report Module 158

3. Data types, constants and variables 168

3.1 Data type 168

3.2 Constant 170

3.3 Variable 171

4. Control structures 175

4.1 Sequential structure 175

4.2 Branching structure 176

4.3 Repetition structure 179

5. Functions and procedures 182

5.1 Function 182

5.2 Procedure 184

6. Database Access Models 184

6.1 MS Access application program architecture 184

6.2 DAO (Data Access Objects) Architecture 185

6.3 ADO (ActiveX Data Objects) Architecture 214

CHAPTER 7 EXERCISES 241

REFERENCES 242

PREFACE

In the development of today's knowledge economy, the role of information technology cannot be denied. Especially the database field has been and is being interested by many people. Many database management systems have been applied such as Foxpro, Access, SQL Server, Informix, Oracle, ... But the simplest and most friendly is the Access database management system, although it does not have strong support tools like Informix or Oracle, but it also supports relatively well in terms of small and medium data management, and it also provides tools that allow us to program. That is why we often call it the Access language. Microsoft Access provides a very powerful application program system, helping users quickly and easily create applications from any data source through Query, Form, Report combined with the use of some Access Basic commands or also known as VBA programming.

Microsoft Access has gone through many versions, from the first version of Microsoft Access released in 1992 to now it has gone through many releases with over 130 million copies. Therefore, it can be said that Access is one of the most popular database programs in the world. Currently, some widely used versions are Microsoft Access 2007, Microsoft Access 2010, Microsoft Access 2013. In this document, we use Access 2013 version for illustration.

Based on the actual learning needs of IT students in general and students of the University of Transport in particular. We wrote the textbook closely following the school's course outline, to serve first-year students studying the subject "Introduction to Database Management Systems".

The course consists of 7 chapters:

Chapter 1: Introduction to the Basic Concepts of Microsoft Access

Chapter 2: Presents how to create tables and how to use tables to organize data for problems.

Chapter 3: Describes how to create and use queries to aggregate, organize, and search data.

Chapter 4: Describes how to design and use forms to organize data entry for a table, query, or related tables.

Chapter 5: Describes how to use reports to organize and print data from a table or query.

Chapter 6: Introduction to Macros and Menus used to design program interfaces. Chapter 7: Presentation on programming in Access.

In the process of compiling this textbook, although we have tried our best, errors are inevitable. We look forward to receiving comments from readers to improve the book.

Hanoi, January 12, 2015 Authors

Dr. Dang Thi Thu Hien, MSc. Do Thanh Thuy, MSc. Nguyen Kim Sao

CHAPTER 1: OVERVIEW OF MICROSOFT ACCESS

1. Introduction to Microsoft Office

Microsoft Access is a component of the Microsoft Office suite, a database management system in the Windows environment, which has available effective and convenient tools to easily build programs for some common problems in management, statistics, accounting...

Microsoft Access is a means of exchanging information on the Internet and Intranet, helping users quickly create and use application programs on the network. Access not only easily links related information but can also work with many other data sources: dBASE, Paradox, SQL on many types of computers: office computers (PC), servers (Server), minicomputers and mainframes. Access takes on two roles: software on the client or server and easily accesses other application programs such as Microsoft Word, Excel, PowerPoint.

Each program file usually has a specified extension, for example, Microsoft Word files have the extension .doc, Pascal program files have the extension .pas ... A program file created by Access 2003 has the extension .MDB, and one created by Access 2013 has the extension .ACCDB

A program system created by Access is called a Database. In languages ​​such as C, Pascal, Foxpro,... a program system consists of program files and data files organized separately. But in Access 2013, the entire program and data are contained in a single file with the extension .

.ACCDB. Thus, the term program system or database is understood as a combination of both programs and data. For brevity, we often call the program instead of the term program system. Thus, from now on, when talking about a program or program system or database, it also means that it is a software system including both programs and data created by Access.

2. Working with Microsoft Access

2.1 Start Access

To start (run) Microsoft Access you must make sure that Microsoft Office (which includes the Microsoft Access component) is properly installed on your computer.

Comment


Agree Privacy Policy *