Learning SQL with Microsoft Access

Microsoft Access is an interesting product and is worth learning for both personal and business use. I do all my personal accounting in Microsoft Access. Since Microsoft includes Access with different versions of Microsoft Office, it is likely that you have it on your computer right now. If not, Microsoft sells the product for a reasonable price, I remember the price being about $99 for a seat.

The two primary uses for Microsoft Access are: creating personal databases, and accessing data from the larger databases using ODBC (Open Database Connectivity). Access has a simple user friendly interface. With Microsoft Access's visual tools, you can quickly create tables and simple forms. If you know Visual Basic, you can even extend the database with modules. 


My personal opinion is that, by the time you start thinking about writing mission critical programs with Access and Visual Basic, you would do better to move to a more robust platform such as SQL Server, ORACLE, DB2 or perhaps the popular Linux shareware program mySQL

When learning Access, keep in mind that it is not the ideal program for creating mission critical or multi-user applications. It does not work well as a database server. It works best as either a personal database, or a database client.

One of the most interesting things about Access is that the program evolved in conjunction with the Microsoft operating system. The Jet Database Engine used in Access was used in the development of the Microsoft's database connectivity tools including ODBC, OLE, ADO and more. Because of the close ties between the Microsoft operating system and Access, the skills learned while studying Access can be used for other programs that use a Windows interface.

Sun Microsystems designed JDBC (Java Database Connectivity) to use the ODBC connections. This means that Access an ideal tool for Java programmers who want to learn JDBC. Many Java development shops actually use Access in the prototype and development stage, then port their application to an industrial strength database for release.

When developing a program using JDBC, it is often a good idea to test the program with different databases. This will help keep your program platform independent.

So, even if your intention is to use a more powerful databases in your career, Access is a great starting place for learning SQL and basic database design skills. You will be able to use these skills when programming with Java, Visual Basic, or other programming languages.

I recommend for serious programming students to learn Access. However, when learning Access, serious students need to avoid the seduction of the user interface. Access has simple visual tools for creating tables, queries and forms. However, these tools are not good for learning the actual theory. Learning to "paint" a form in Access does not give you the skills you can port to other databases. Painting  forms and tables in Access simply teaches you how to paint forms in Access.

To develop portable skills, you need to get behind the scenes and learn SQL (Structured Query Language). SQL is a language for creating database tables and writing queries. It is not difficult to learn. SQL was designed for businessmen--not engineers. The goal of the language is to give ordinary people access to data without having to learn how to program. There are few commands in the language, and it should only take a few minutes to figure out what you are doing with the language. 

The most important command you need to know is the SELECT statement. You use the SELECT statement to retrieve information from the table. The basic SELECT statement has the form:

SELECT column1, column2
  FROM TableName
 WHERE columnXXX = 'criteria'

In this statement, you will select the two columns with the names column1 and column2 from a table called TableName. In the WHERE clause you can give a criteria that limits the number of rows returned. If you don't have a WHERE clause, you will get all the rows in the table.

The hardest part in using SQL is remembering the names of the columns, tables and the relations between tables. There are many good books on SQL programming. I will get more into the details of SQL in another article. 

Now, the problem with Microsoft Access is that it does not include a regular command line for learning SQL queries. Apparently, the Microsoft programmers assumed that everyone would be so overwhelmed with their user interface, that no-one would never want to behind the scenes and learn the nitty-gritty of  SQL.

It is a little bit of a hassle, but you can get to a screen that lets you write SQL. To do so, you need to create a blank Select Insert Query from the main menu [Alt-SQ]. You should see the following screen. Highlight "Design View" and hit OK. This will create a new blank query.

 

The program will ask you to select a table. So select one of the tables you will use in the query. You should now be in the query design view. From the main menu, select View, SQL View from the main menu, and you will have a command line where you can write SQL queries. You can practice writing SELECT, UPDATE, INSERT and CREATE TABLE statements from this view. To execute the query, press the "!" icon.

 

This query window works like a command line, and you can learn basic SQL skills.

The visual tools are great for creating quick applications, but to learn database theory, it is worth taking a few extra minutes to learn to write SQL statements. The skills you learn writing SQL queries is transferable to almost all major databases in production today. 

index -- next
descmath
, plusroot, search, rgreetings, sponsors