Page 493 - Microsoft Office Excel 2003 Programming Inside Out

Chapter 22

Excel and the Structured

Query Language

Comparing Spreadsheets The Insert Statement . . . . . . . . . . . . . . 478
and Databases . . . . . . . . . . . . . . . . . . . 467 The Update Statement . . . . . . . . . . . . . . 479
Manipulating Databases with SQL . . . . 471 The Delete Statement . . . . . . . . . . . . . . 479
The Select Statement . . . . . . . . . . . . . . 471

One of the many useful features of Microsoft Excel is its ability to access information in data-
bases. You can copy information from a database table to a worksheet or from a worksheet to
a database table. You can even combine information from a Microsoft Access database or a
Microsoft SQL Server database with a user form.
This chapter serves as a brief introduction to using databases such as Microsoft Access and
Microsoft SQL Server with Microsoft Excel. Because designing an effective database is beyond
the scope of this book, design is not discussed at all. But there are a number of good books
that cover Microsoft Visual Basic and database programming. However, in the context of
programming Excel 2003, you should look for a book that covers database programming for
Visual Basic 6, not for Visual Basic .NET. Visual Basic .NET is not compatible with the Visual
Basic for Applications (VBA) facilities included with Excel. On the other hand, Visual Basic
6 is fairly close to VBA, and it uses the same tools (specifically ADO) to access a database.
So the database programming techniques for Visual Basic 6 can be carried over to Excel and
VBA without change.

Comparing Spreadsheets and Databases

In many respects, a worksheet, consisting of a series of rows and columns, is very much like a
table in a relational database. This similarity can make a worksheet a powerful tool for
manipulating database information.

Fundamental Database Concepts

A relational database consists of a series of tables. Each table roughly corresponds to a
single worksheet. A table is structured as a series of rows and columns of data, as shown in
Figure 22-1.

467
Part 6: Excel and the Outside World: Collaborating Made Easy