Introduction
To get the most out of a book, it is usually a good idea to discover immediately what the authors plan to discuss in the book, how they plan to present the material, and how much knowledge of the subject the reader needs to have. So, to put things in a nutshell, this is a book about basic SQL and how to build SQL database queries in Microsoft Access. As an added plus, the final chapters of this book discuss the integration of SQL script in Visual Basic and ASP.
The primary targets are those people who have done some work in Access or a comparable program and who can build queries and tables using the tools, wizards, or query grids, but who are now ready to take the next big step into the underlying programming of SQL itself.
So, how do we go about presenting a topic like SQL in a simple, easy-to-understand format? Well, we have to start somewhere, so each chapter begins with a short introduction to highlight what we plan to discuss in that chapter. The next section of each chapter is a list of important definitions. Here you will find the keywords and terms that are to be used, explained, and expanded upon. This is also the perfect place to scan if you want to find where a specific keyword is introduced and how it is used in an SQL statement. This is not a replacement for the index or table of contents! Instead, it is for those who want a quick, concise answer.
The bulk of the chapters will contain all the little bits of facts and examples that are used to impart wisdom and fill up the rest of the pages in the book. We will be taking a two-pronged approach to the SQL language. First, we will present it from a “blank slate” approach. Here we will build on one reserved word at a time until we cover the ins and outs of the language. Since we expect everyone to know a bit of Access, we will also be flipping between the three major layouts of the Access query screen. We expect the user to be familiar with both the query grid of the Design view and the results screen of the Datasheet view. We suspect that you have at least accidentally selected SQL view once or twice. We will use the power of Access to show the results of Design view queries in SQL view and illustrate both the good and bad of the Access interpreter. The power of SQL view extends the capabilities of Access tremendously. It also presents to the programmer what is really happening in the case of complex queries.
The Importance of SQL in Microsoft Access
Some people will say that they do not need SQL to program in Access. They are correct. But to use an analogy (you are hereby warned that one of the authors loves analogies), not using SQL is like not using any gear but first to drive a car. It can be done, but the car has to work a lot harder and you waste a lot of energy.
Let’s begin with one of the more mundane uses of the SQL format of a query. You need to send a copy of a query to a friend who is using one of your databases. He can get around in Access. You have this great new wonderful query you want him to use but you don’t want to have to send him the entire database. You have two options. You can create a new database that only contains your one query and the needed tables to keep it from blowing up if he accidentally tries to edit it in place. You can then e-mail the new database, and he can copy the query into his database.
The other method is to use SQL, which makes the entire process much simpler. First, change the view of the query to SQL view. This produces a block of text that is the SQL statement. Copy it to the clipboard and paste it in the text field of an e-mail message. Send it. Have your friend open up Access and build a new query and then change to SQL view, paste the contents of the e-mail message you sent as the SQL value of the query, then change to Design view. Voilà! You have just sent a query without the overhead or hassles of an Access file.
SQL will prove to be as useful in lots of other ways as you will see in later chapters.
Code Interpretations
Throughout the chapters of this book you will also come across many syntax (a series of rules that state how SQL script must be scripted) models that show you the proper format to follow when creating a specific query. When interpreting SQL syntax models, note the following:
Keywords are typed in all uppercase.
Items enclosed in brackets [ ] represent optional items.
A | symbol means or.
Parentheses should be included in the actual query.
Companion Files
The companion files can be downloaded from www.wordware.com/files/sql-access2ed. There are two files: database.zip and wordwarebook.zip.
Database.zip contains the database used in the examples, and wordwarebook.zip includes the files used in the ASP examples in Chapter 15. The wordwarebook.zip files must be installed on a web server (see Chapter 15 for instructions).