Upgrading your skills with Access E-Book

Rémy Lentzner

• Herausgeber: Remylent
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch
• Veröffentlichungsjahr: 2023

Seitenzahl: 70

Beschreibung

Welcome to Microsoft Access.
This book is intended for all those who want to increase their knowledge about the database management and its structure. 
You will study the tables, the fields, the primary and foreign keys, the queries, the underlying SQL language, the forms, the macros and the Visual Basic language that can help you to develop applications adapted to your needs. 
The strong point of Access is its ability to link tables together, thanks to the relational model. Once the tables are joined, you can display information with the queries that bring a great liberty for extracting multiple data. 
I hope this book will improve your skills in order to manipulate any data.
Enjoy your reading.


ABOUT THE AUTHOR

Rémy Lentzner has been an IT trainer since 1985. Specialized in mastering office automation tools, he supports companies in the professional training of their employees. Self-taught, he has several computer books to his credit.

Leseprobe

REMY LENTZNER

Upgrading your skills with Access

French original title : Mieux maîtriser Access

EDITIONS REMYLENT, Paris, 1ère édition, 2023

R.C.S. 399 397 892 Paris

25 rue de la Tour d’Auvergne - 75009 Paris

[email protected]

www.REMYLENT.FR

ISBN EPUB : 978-2-38518-039-3

Access is a registered trademark of Microsoft

The Intellectual Property Code prohibits copies or reproductions intended for collective use. Any representation or reproduction in whole or in part by any means whatsoever, without the consent of the author or his successors in title or cause, is unlawful and constitutes an infringement, pursuant to articles L.335-2 and following of Intellectual Property Code.

This book is dedicated to Bruno and Hélène

I could not have written it without their support, advice, encouragements and proofreading.

Graphic illustration : Bruno Conquet

In the same collection

Improve your PivotTables with Excel

Upgrading your skills with Excel

Improve your skills with Google Sheets

Programming macros with Google Sheets

Getting started with HTML

Getting started with JavaScript

Getting started with PHP & MySQL

Google Docs

Google Slides

Google Gmail

Macros & VBA with Excel

Getting started with WordPress

Getting started with Programming

Getting started with Numbers

Getting started with Pages

Getting started with Keynotes

Upgrading your skills with Word

Upgrading your skills with PowerPoint

Upgrading your skills with Outlook

Getting started with OpenOffice Calc

Getting started with OpenOffice Writer

Getting started with OpenOffice Impress

Getting started with OpenOffice Base

Macros & Basic with OpenOffice Calc

Getting started with Adobe Acrobat Pro

Getting started with Sparkle

Getting started with SQL

TABLE OF CONTENTS

Chapter 1 Table and objects

1.1 The table

1.1.1 Creating a database

A) The File menu

B) The Home menu

C) The Create menu

D) The External Data menu

E) The Database Tools menu

1.1.2 The table structure

1.1.3 Designing a table

1.2 Handling the data inside a table

1.2.1 The columns

1.2.2 The filters

1.3 The table properties

1.3.1 The properties of text type

1.3.2 The properties of number type

1.3.3 The properties of type date

1.3.4 The property of AutoNumber type

1.3.5 The replication ID property

1.4 The lookup properties

1.5 The Total row in a table

Chapter 2 Relationships

2.1 Defining relationships

2.2 The relational model

2.2.1 Selecting tables

2.2.2 Creating the relationship

2.2.3 Modifying the relationship

2.2.4 Enforcing the relationship

2.2.5 The join properties

2.2.6 Viewing links in the table

Chapter 3 Queries

3.1 Creating a query

3.1.1 The Query wizard

3.1.2 Creating a query manually

3.1.3 Selecting fields

3.1.4 The filter criteria

A) Filtering with two words

B) Filtering with the wildcard * and the Like operator

C) Filtering with a simple date

D) The Null operator

3.1.5 Sorting the data

3.2 Grouping the data and calculating

3.3 Elaborated queries

3.3.1 Creating an Update query

3.3.2 Creating a Delete query

3.3.3 The Append query

3.3.4 The Make table query

3.3.5 The Crosstab query

3.3.6 The Union query

3.3.7 Using the function DatePart in a query

3.3.8 The parameterized query

Chapter 4 Forms

4.1 Creating a form

4 .1.1 A table-based form

4.1.2 The controls

4.1.3 The Form Wizard

4.1.4 Field tools

4.2 A Form with a subform

4.3 Creating a blank form

Chapter 5 Macros

5.1 Creating a macro

5.1.1 A macro to filter

5.1.2 Running a macro from a Command Button

5.2 A field, an event and a macro

5.3 Actions and VBA keyword

5.4 A conditional macro

Chapter 6 The VBA language

6.1 The development environment

6.2 Acting with DoCmd

6.2.1 Opening and searching

6.2.2 Exporting a table to Excel

6.2.3 Importing an Excel spreadsheet

6.2.4 Running a query with DoCmd

6.2.5 Running queries automatically

6.3. Opening a database with VBA

INTRODUCTION

This book is intended to anyone who is interested in the Microsoft Access database. The versions have evolved over time and the latest one is Access with Office 365. This book is written with the version 16.0.14326.20450 64 bits and a licence under Windows 11.

Access is a very elaborated application whose objective is the management of data stored in tables. It has a comprehensive set of tools that enables you to query numerous information. Forms and reports improve the layouts. The great advantage of Access versus Excel is the ability to store a large amount of data. In addition, indexes (primary and foreign keys) enable you to link tables with relationships and relational model.

With Excel you can enter any type of information in a column, for instance dates, numbers or text. Working with a database requires several constraints that must be respected. For instance in a numeric field type, you are not allowed to enter any text. When designing the table, very strong rules are set up and must be respected.

To make your manual actions automatic, it is possible to perform macros in order to repeat tasks in relation with database objects. Moreover, Access offers a VBA development platform that will help you to create customized applications. With this language, you will be able to develop forms with buttons that trigger actions.

This book is divided in 6 chapters.

Chapter 1 details the database fundamentals and the different menus of the application. You will learn how to create a table with the fields, the types and the properties. All these features are very important to produce a good data organization.

Chapter 2 focuses on the relationships with the primary and the foreign keys as well as the data integrity. You will discover the joins and the SQL language that are always in the background.

Chapter 3 deals with queries that are very powerful tools for visual interrogation of data. Based on the SQL language (Structured Query Language), queries enable tables to be linked together with joins. You will study the different kind of queries such as Select, Update, Delete, Append, Union, etc.

Chapter 4 focuses on forms which are commonly used to present data more attractively. When you change information via a form, it is immediately reflected in the table. A form can be used to create dialogs without interaction with tables.

Chapter 5 details the mechanism of macros which allow manual tasks to be automatic. It allows you to manage the controls for data entry.

Chapter 6 shows how to develop customized application with the VBA language. You will learn how to write procedure in a programming environment.

I hope this book will enable you to progress in the database management.

Do not hesitate to contact me at [email protected] if you have any comments or questions about this book.

Enjoy your reading.

The author

Chapter 1 Table and objects

To manage information in a database you must design the tables and the relationships between them correctly. Once these two operations are completed, you can enter the data, then query it using visual queries. Creating a table is a delicate operation because it defines the fields and their properties. This chapter details the many features that organize the setting up of tables.

1.1 The table

A table is the physical support of information. You can store millions of rows (or records) in it, unlike Excel where the limit is 1048576 rows (for now). A table is structured in columns (or fields), each of them has a specific type. If a column has a numerical type, you will not be able to enter alphabetic values. If a column has a date type, you can only enter dates. This organization seems rigid but it is safer for the information coherence. The most important thing when creating a table is the structure of the fields and their properties. Before defining the tables you must first create a database. This operation is performed only once and saves the object storage environment.

1.1.1 Creating a database

Start Access, choose New on the left panel, click on Blank Database, then give a name in the File name area. Finish with the Create button, as shown in figure 1.1.

Figure 1.1 : Creating a database

The file extension will be .accdb. The older version was .mdb.

Figure 1.2 shows the working environment.

Figure 1.2 : Access starting point

A) The File menu

It shows several actions you can perform thanks to the left panel.

Figure 1.3 : The left panel from the menu File

The white arrow in the left corner of the window enables you to return to the working environment.The New tab is used to create a new database.The Open tab enables you to open an existing database.The Info