MS Access and Structured Query Language (SQL)

Robert Austin | November 18, 2018

Structured Query Language (SQL)

In this post, we will be looking at how to use Structured Query Language (SQL for short) to create, read, upate and delete data in our MS Access applications.

Note: SQL is usually pronounced SeQueL.

What is Structured Query Language?

The fundamental job of an MS Access database (or indeed any database) is to provide a home (base) for data (data). Hence database. Data is stored in entities which are referred to in MS Access as tables and this ability to store data provides us with an opportunity to read, update, insert and delete said data at a later date. In order to work with the data we have stored, we need to be able to communicate with the database. And in order to communicate with the database, we need to use Structured Query Language (SQL).

SQL provides us with the opportunity to read data from single or even multiple tables. We can use SQL to sum, count or average values stored in the database. SQL can also be used to insert new records, update existing ones and delete unwanted ones. Let’s examine what an SQL statement might look like.

The most basic statement you can write with SQL is called a SELECT statement.

SELECT Invoices.ID, Invoices.InvoiceNo, Invoices.Date, Invoices.OrganisationID FROM Invoices 

In the above SQL statement we are retrieving the fields ID, InvoiceNo, Date and OrganisationID from an Invoices table.

Where will I find SQL?

In MS Access, SQL can be found in queries (view a query in SQL View). It can also be used in VBA to construct custom queries to manipulate data (this will be covered later in the post).

Note: In MS Access, every communication with the database uses SQL to some degree although this is not always obvious. When you create a form based off a table, the form is using SQL to communicate with the table but these interactions are buried inside MS Access.

SQL Keywords

When you use SQL to retrieve data, you are creating a query. If you take another look at the Select statement above, you will see that some of the words are in UPPER case (SELECT and FROM). These are called keywords and are fundamental to creating SQL queries.

Note: When writing SQL, it doesn’t matter if you use UPPER or lower case for the keywords. SELECT and select are treated exactly the same. There is, however, a convention to use UPPER case as it is easier to read.

There are many keywords in Structured Query Language. Here is a brief description of some of the more popular ones:

Table 1

The CRUD Operations

It is worth remembering that, the fundamental purpose of a database, is to store and manipulate data. At the very basic level there are four actions that a database is required to perform. These activities are often referred to as CRUD operations. CRUD stands for Create, Read, Update and Delete. In SQL terms, these 4 operations translate to:

• Create = INSERT
• Read = SELECT
• Update = UPDATE
• Delete = DELETE

Let’s expand on the SELECT statement.

SELECT Query

In MS Access, you can create a SQL query by either writing the code directly (SELECT field FROM table) or you can use a special interface to help construct the code for you. This special interface method is known as Query-By-Example. It can be an excellent way to generate SQL code without having to be too adept at writing it. We will begin by showing you how to use the QBE editor.

Open the Teaching Institute Software System Database if you wish to practice creating and modifying queries.

TeachingInstituteSoftwareSystem 

In the ribbon, click Create and then select Query Design, the Query By Example editor will open (double-click on the image to enlarge it).

Figure 1

The three areas that have been highlighted are:

1. The Query Type button group.
2. The Show Table Dialog Box.
3. The Query Grid.

We are going to create a simple query that will retrieve the StudentID, First Name, Last Name and Email Address from the tblStudents table.

• In the Show Table Dialog Box, double click tblStudents. You will see tblStudentsappear in the query pane (item 1 in Figure 2).
• Close the Show Table Dialog Box.
• Double Click on StudentID, LastName, FirstName and EmailAddress in tblStudents in the newly added tblStudents table. You will see that these fields have been added to the query grid in the bottom half of the window (Item 2 in Figure 2). You can also click and drag the fields from the table to the query pane.

Your screen should now look something like this:

Figure 2

• In the ribbon select Run (Item 3 in Figure 2).

The results of the query are displayed in tabular form.

Figure 3

Скачано с www.znanio.ru