SQL for Access

If you just want to jump to the syntax for a specific item then use the following bookmarks: Select, From, Where, Order By, Join, Filter, Distinct, Aliases.

Databases and Recordsets

In a new project, references must be made to the ‘Microsoft DAO 3.51 Object Library (or later)’. This then gives you Database and Recordset objects to play with.

The following code defines variables for a Database and Recordset Object, and also creates SQL as a String variable.

Dim WorkBase as Database
Dim WorkRS1 as Recordset
Dim SQL as String

You can, of course, declare more than one Database and Recordset and have them open at the same time.

The first practical step would be to open the database

Set WorkBase = OpenDataBase(“C:\DBPath\DBFileName.MDB”)

You could use a string variable to hold the database path for the life of the application and this could be set from an Environment Variable, the Windows Registry or from an INI file.

I am going to suggest that you routinely store your SQL statements in a string variable. This helps keep your code readable - particularly when the statements are long or complex. You can also construct SQL statements programatically when they are stored in a string variable. So the next step is to enter seme SQL.

SQL = “Select * from Employee”

Then set the Recordset object

Set WorkRS1 = WorkBase.OpenRecordset(SQL)

The Recordset should now hold the information requested in the SQL query statement. Please remember to close both the Recordset and Database objects before terminating your program.

WorkRS1.Close
WorkBase.Close

SQL Statements

SQL statements are used to create virtual tables (Recordsets) from information held within one or more tables from a database.

SQL is not case sensitive but in the examples below TABLE and FIELD names are in upper case.

The first part of the SQL Statement is “Select”, you must then specify what you want to select. This selects the columns you require from a table, for example, you may only require a list of Employee Names stored in a table called EMPLOYEE.

Your SQL statement would look like

 “Select EMPLOYEENAME From EMPLOYEE”

If you wish to select ALL details about an employee held within a row of a given table then you can substitute the Column Name with ‘*’.

 “Select * From EMPLOYEE”

FROM Clause

The FROM Clause shows which table(s) from the access database are to be used.

WHERE Clause

An SQL statement with a WHERE clause could look like

SQL = “Select * from EMPLOYEE Where EMPLOYEENO = 6”

The Where clause specifies constraints on which records you require in your Recordset. The above example would Select All columns From the Employee Table where the row had an EmployeeNo = 6.

Assuming each row holds a single employee’s details and that EmployeeNo was a unique identifier in the table ie every EmployeeNo was different, then you would only expect one row to be returned to the Recordset.

It is not very often that you can ‘hard code’ an SQL statement like the one above, unless you only ever want the details of employee no 6. So you may want to use a variable which has either been declared and set in the same procedure or has been passed to the function as an argument.

Variable Types

String

When specifying a string qualifier in an SQL statement then you need single quotes around the string. This is still true when the string is being held within a variable. In the example below I have also introduced a Trim$() function to strip off any leading or trailing spaces but this is not strictly necessary as SQL ignores them in most instances.

SQL = “Select * from Employee Where EmployeeName = ‘” & Trim$(MyName) “’”
Look carefully for the single quotes within the litteral strings contained within the double quote (“) marks.

Numeric

A numeric variable can be included by using CStr to convert the numeric variable into a string.

“Select * from Employee Where EmployeeNo = “ & CStr(EmpNo”)

Date

Dates in SQL statements must be in US (United States) short format, and surrounded by the # symbol. For example:

 SQL = “Select * from Employee where StartDate >= #” & _

             Format(StrtDate, “MM/DD/YY”) & “#”

Program Control

If you used an SQL statement similar to the one above then you could expect many employee details to be returned. In a case where you expect more than one ‘Row’ to be returned then you need to loop through the Recordset. This can be done by using:

Do Until WorkRS1.EOF

‘Carry out some processing then
WorkRS1.MoveNext

Loop

The most common beginners mistake is to forget the MoveNext and to send their program into an endless loop.

If you are only after one employee or you are checking a value in the first ‘Row’ in the Recordset then you may wish to use:

If not WorkRS1.EOF then

… ‘Do some processing

End if

The check on WorkRs1.EOF (or WorkRS1.BOF) ensures that you do not create an error condition by trying to read values form an empty recordset.

Joins

At some stage you may need to access more than one table at a time. For example you may have an employee training table where details of employee’s course attendances are held.

It may have the following column headings, in a Table called ‘TRAINING’.

The EmployeeNo links to the EmployeeNo in the EMPLOYEE Table.

If you needed to find out what training a particular employee had covered, then you would need to find the EmployeeNo from the EMPLOYEE Table Where EmployeeName = the specofied employee name, then open the TRAINING Table Where EmployeeNo = the particular Employee’s EmployeeNo looked up in the first table.

An easier way of opening two tables, that have a common column eg. EmployeeNo, is to Join them.

NOTE: When joining tables it is not recommended to use ‘*’ to select all the data, you should specify which columns you require using the table name as a prefix. Eg “Select EMPLOYEE.EMPLOYEENAME From” etc.

There are different types of Joins

Inner Join

An inner join is the more common method of joining two tables. The Recordset returns only rows where there are matching rows found in both tables.

“Select EMPLOYEE.EMPLOYEENAME, TRAINING.DATE, TRAINING.DESCRIPTION
From EMPLOYEE, TRAINING where EMPLOYEE.EMPLOYEENO = TRAINING.EMPLOYEENO”

This would list only the employees that have had training recorded in the TRAINING table.

Outer Join

An Outer Join returns all Rows, from either the “Left” table or “Right” table, even if the other table has no values. For example you could return all employee details with or without any recorded training.

“Select EMPLOYEE.EMPLOYEENAME, TRAINING.DATE, TRAINING.DESCRIPTION From EMPLOYEE Left Outer Join TRAINING On EMPLOYEE.EMPLOYEENO = TRAINING.EMPLOYEENO”

NOTE: Instead of using the ‘where EMPLOYEE.EMPLOYEENO = TRAINING.EMPLOYEENO’ syntax you replace Where with On

This example shows a left outer join so this may show a Recordset like:-

Order By Clause

When you need more than one record to be found in a table, you may want to sort the rows returned in your Recordset by one or more column. Records can be ordered in ascending or descending order. For example, to order all employees in EmployeeNo order use

“Select * From EMPLOYEE Order By EMPLOYEENO Asc“

Asc (stads for Ascending) is the default for the order clause and does not strictly need to be specified. Replace ‘Asc’ with ‘Desc’ to return the rows in descending order.

When ordering by more than one column, separate the column names with comma’s.

“Select ... Order By EmployeeNo Asc, EmployeeName Asc”

When Ordering a column that may contain Null values that value is treated as the lowest value. So if ordered is ascending order Null values will be returned first in your Recordset.

When dealing with tables with large amounts of data in them it is usually a good idea to only use Order By on indexed fields. If you decide to Order By on non-indexed fields then it will take noticably longer to return the Recordset.

You can sort on non-indexed (or any other) columns, once the Recordset has been returned. You will first need to declare a second Recordset object.

Dim WorkBase as Database
Dim WorkRS1 as Recordset, WorkRS2 as Recordset

WorkRS1.Sort = “EMPLOYEENAME asc”
Set WorkRS2 = WorkRS1.OpenRecordset()
WorkRS1.Close

Do until WorkRS2.EOF

… ‘start working with the second recordset that is now sorted in Name order

Filter

From the examples shown you can imagine that a complicated SQL statement could get very long. The problem with this is an SQL statement has a limit of 256 bytes.

You can use the sort method on the Recordset to take out any Order By clauses, but this still may not give you enough space. You could use the Filter method on a recordset, which will (as the name implies), filter the data held in the recordset using the constraints that you specify.

The filter method looks similar to the sort method and is applied in the same way:

Dim WorkBase as Database
Dim WorkRS1 as Recordset, WorkRS2 as Recordset

WorkRS1.Filter = “EMPLOYEESTART >= #04/30/90#" (remember US Dates)
Set WorkRS2 = WorkRS1.OpenRecordset()
WorkRS1.Close

Do until WorkRS2.EOF

… ‘and so on

Distinct

A table might contain many rows for any given selectio criteria. If you only need to know about any one of them then you can use the Distinct keyword in your SQL.

For example if you wanted to list all the Job Descriptions from the Employee table, given that some employees will have the same job description, use

“Select Dinstinct JOB From EMPLOYEE”

Aliases

When joining Tables you can give a table an alias name which you can then use throughout the SQL statement. This is another way of reducing the size on an SQL statement that a join.

When joining the Employee and Training Table instead of typing the whole table name in front of every column, you could alias Employee as E and Training as T Which would give you:

“Select E.EMPLOYEENAME, T.DATE, T.DESCRIPTION From EMPLOYEE E, TRAINING T where E.EMPLOYEENO = T.EMPLOYEENO”

which is the equivalent SQL to the inner join example given above.