Using SQL in Access

This post is for teachers mastering Microsoft Access for the DBE CAPS CAT, IEB IT, and Cambridge International IGCSE & A-level. CAPS & IEB IT students should be able to complete this work as well. You will learn how to execute SQL statements in the Microsoft Access interface or from within VBA code.

1. Run SQL in Access

The trick is simple: the Q in SQL stands for Query and that is what we will use to execute our SQL statements.

1. Activate the Create menu
2. Select Query Design
3. Left-click the SQL View option
4. Type/paste the SQL statement in the window
5. Save the query
6. Left-click the Run button

As always, save your queries with meaningful names, such as qryCreateCustomerTable.

Note that once you have created these queries you can only open them in SQL View. As usual, beware of running action queries as they can modify or delete data and/or fields and there is no “undo”.

2. Create

Data Definition Language (“DDL”) is part of SQL. ^[1]

2.1 Create a table

CREATE TABLE tblMyTableName
(
	fieldName1 datatype,
	fieldName2 datatype,
	fieldName3 datatype,
	fieldName4 datatype
);

CREATE TABLE tblCustomers
(
	customerID INT,
	customerSurname TEXT(100),
	customerCellnumber TEXT(10),
	customerCreditApproved YesNo
);

2.2 Create a table with a Primary Key

CREATE TABLE tblMyTableName
(
	fieldName1 datatype,
	fieldName2 datatype,
	fieldName3 datatype,
	fieldName4 datatype,
	PRIMARY KEY (fieldName1)
);

CREATE TABLE tblCustomers
(
	customerID INT NOT NULL,
	customerSurname TEXT(100) NOT NULL,
	customerCellnumber TEXT(10),
	customerCreditApproved YesNo,
	PRIMARY KEY (customerID)
);

3. Alter

You cannot add or delete more than one field or index at a time. ^[2]

3.1 Add a field

Add a field to a table: ^[1]

ALTER TABLE tblCustomers ADD COLUMN customerCompany TEXT(50);

3.2 Add a Primary Key

You can create your Primary Key (“PK”) or other Indexes on an existing field in a table.

CREATE INDEX customerID
    ON tblCustomers (CustomerID) 
    WITH PRIMARY;

Note that you will be prevented from creating this Index if a PK already exists for tblCustomers.

4. Drop

DROP a table and all the data it contains. DROP can only be used in Access from within VBA code.

See the post TRUNCATE and Access.

5. Next steps

• Analysing a query in an Access database
• Advanced SQL for Access

References:

1. Microsoft Support (no date) Create or modify tables or indexes by using a data-definition query. Available at: https://support.microsoft.com/en-us/office/create-or-modify-tables-or-indexes-by-using-a-data-definition-query-d935e129-229b-48d8-9f2d-1d4ee87f418e (Accessed: 24 May 2024).
2. o365devx (no date) ALTER TABLE statement (Microsoft Access SQL), Microsoft Learn. Available at: https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/alter-table-statement-microsoft-access-sql (Accessed: 21 April 2024).