How to Use SQL with Microsoft Access - dummies

How to Use SQL with Microsoft Access

By Allen G. Taylor

Access is designed as a rapid application development (RAD) tool that does not require programming. You can write and execute SQL statements in Access, but you have to use a back-door method to do it. To open a basic editor where you can enter SQL code, follow these steps:

1Open your database and click the CREATE tab.

This will display the ribbon across the top of the window.

2Click Query Design in the Queries section.

The Show Table dialog box appears.

3Select the POWER table. Click the Add button and then click the Close button to close the dialog box.

A picture of the POWER table and its attributes appears in the upper part of the work area and a Query By Example (QBE) grid appears below it. Access expects you to enter a query now by using the QBE grid. (You could do that, sure, but it wouldn’t tell you anything about how to use SQL in the Access environment.)

4Click the Home tab and then the View icon in the left corner of the Ribbon.

A menu drops down, displaying the different views available to you in query mode.

One of those views is SQL View.

5Click SQL View to display the SQL View Object tab.

The SQL View Object tab has made the (very rational) assumption that you want to retrieve some information from the POWER table, so it has written the first part for you. It doesn’t know exactly what you want to retrieve, so it displays only the part it feels confident about.

Here’s what it’s written so far:


6Fill in an asterisk (*) in the blank area in the first line and add a WHERE clause after the FROM line.

If you had already entered some data into the POWER table, you could make a retrieval with something like:

 WHERE LastName = 'Marx' ;

Be sure the semicolon (;) is the last thing in the SQL statement. You need to move it down from just after POWER to the end of the next line down.

7When you’re finished, click the floppy-diskette Save icon.

Access asks you for a name for the query you have just created.

8Enter a name and then click OK.

Your statement is saved and can be executed as a query later.