Basically, Microsoft Access is an information management tool which helps in storing information for reference, reporting, and analysis. This great tool also helps in analyzing large amounts of information, and manage related data more efficiently than Microsoft Excel or other spreadsheet applications.

Here at Ibmi Media, we regularly help our Customers to solve SQL related queries as part of our Server Management Services.

In this context, we shall look into using SQL with Microsoft Access.

More about the potential of Microsoft Access?

By design, Access is made to be a rapid application development (RAD) tool which does not need any form of programming for it to work effectively.

It allows you to write an run SQL statements when done unconventionally.

Now, we will discuss about how to use SQL with Microsoft Access below.

Steps on using SQL with Microsoft Access?

To run SQL statements on Microsoft Access, you need to open a basic editor to allow you enter SQL code.

TO do this, follow the steps below;

i. Start by opening your database and once opened, click on the "Create" tab. The create tab will display the ribbon across the top of the window.

ii. Next, click on "Query Design" in the "Queries Section" to open the Show Table dialog box.

iii. Now, Select the "Power" tab. In here, click on the "Add" button and then click the "Close" button to close the dialog box. This will display the "POWER" table as well as its attributes. Here you can enter queries via the "QBE" grid. 

iv. The next step is to click on the "Home" tab followed by the "View" icon located in the left corner of the Ribbon. This will display a menu drop down showing different views in query mode. Here you can select "SQL" view.

v. Next, click on the SQL View to show 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 does not know exactly what you want to retrieve, so it displays only the part it feels confident about.

To use the POWER table, run the following statement:

SELECT
FROM POWER ;

vi. You can enter in an asterisk (*) in the blank area in the first line and add a WHERE clause after the FROM line. Let's say you enter a data such as "test1" in the POWER table, you can retrieve it by simply running the following statement:

SELECT *
FROM POWER
 WHERE LastName = 'test1' ;

Ensure that 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.

vii. Finally, you can click the floppy-diskette Save icon which you prompt you to give the newly created query a name. To complete this process, give it a name and then click on "OK" to save the statement which can be executed as a query at a later time.

Need support in executing SQL queries? We are available to help you today.