Advanced Visual Basic Techniques
(Publisher: John Wiley & Sons, Inc.)
Author(s): Rod Stephens
ISBN: 0471188816
Publication Date: 06/01/97

ConnectToDatabase finishes by performing some user interface chores. It adds the database name to the program’s caption and the list of recently accessed databases. It also enables commands, such as the Disconnect command, that are appropriate while a database is open.

Private Function ConnectToDatabase(db_name As String) As Boolean
Const DATABASE_NOT_FOUND = 3024

Dim i As Integer
Dim err_num As Long
Dim err_descr As String

    ‘ Start waiting.
     WaitStart

    ‘ Connect to the database.
     On Error Resume Next
    Set TheDB = TheWS.OpenDatabase(db_name)
    err_num = Err.Number
    err_descr = Err.Description
    On Error GoTo DbOpenError

    If err_num = DATABASE_NOT_FOUND Then
        ‘ The database file does not exist.
     ‘ See if the user wants to create it.
     Beep
     If MsgBox(“This database does not exist.” & _
         vbCrLf & vbCrLf & _
         “Do you want to create it?”, _
         vbYesNo + vbQuestion, _
         “Create Database?”) = vbNo _
     Then
         ConnectToDatabase = True
         WaitEnd
         Exit Function
     End If

        ‘ Create the database.
         Set TheDB = TheWS.CreateDatabase(db_name, _
         dbLangGeneral)
    ElseIf err_num > 0 Then
        Err.Number = err_num
        Err.Description = err_descr
        GoTo DbOpenError
    End If

    ‘ Create a QueryDef object to query with.
     Set TheQD = TheDB.CreateQueryDef(“”)

    DBName = db_name
    AddRecentDB DBName
    Caption = APPNAME & “[“ & DBName & ”]”
    For i = 1 To 4
     mnuDBList(i).Enabled = False
    Next i
    mnuDbDisconnect.Enabled = True
    mnuDbConnect.Enabled = False
    CmdRun.Enabled = True
    ConnectToDatabase = False
    WaitEnd
    Exit Function

DbOpenError:
    ‘ Do not leave the cursor as an hourglass.
     WaitEnd
    Beep
    MsgBox “Error” & Str$(Err.Number) & _
     “ connecting to database ” & db_name & _
     “.” & vbCrLf & Err.Description, _
     vbOKOnly + vbExclamation, “Error”
    ConnectToDatabase = True
    Exit Function
End Function

Composing SQL Commands

Even though there is no room in this section to explain the entire SQL language, there is room to describe many of the most important SQL commands.

Query processes a series of standard SQL commands separated by semi-colons. It ignores carriage returns and spaces within a command.

Query can also interpret two kinds of comments. If it encounters two dashes in a row, it ignores the rest of the command text up to the end of the line. For instance, Query will execute the following SELECT statement correctly:

SELECT LastName, FirstName      -- Find the employees who
    FROM Employees              --   earn more than $30k.
    WHERE Salary > 30000;

Query also ignores comments enclosed in braces. It looks for this kind of comment before it looks for double dash comments. This can cause trouble if comments overlap. For example, consider the following statement:

{ Here is a comment. {Here is a nested comment.} Here is some more. }

In this case Query will match the first open brace with the first close brace. This produces the following code that Query will try to interpret as an SQL statement. Of course, this code is part of a comment, not an SQL statement, so Query will display an error message.

Here is some more. }

The following sections describe some of the most important SQL statements the Query application can execute. These sections describe the commands as they are most frequently used. You can search the Visual Basic help for more information about a command. For example, to learn more about the CREATE TABLE command, search for “CREATE TABLE.” As you read through these sections, you may want to use the Query application to test the commands.

Sections later in this chapter explain how Query extracts the separate SQL statements from the command text, how it removes comments from the statements, and how it executes them.

CREATE

The CREATE statement builds tables, fields, and indexes. The syntax for creating a table is as follows:

CREATE TABLE table (field_name type [(size)] [constraint_clause], ... [, multi-
field_index_name [, ...]])

For example, the following statement creates a table named Appointments. The table contains three fields: a 30-character text field named WhoWith, a date/time field called DateAndTime, and a currency field named Cost.

CREATE TABLE Appointments (
    WhoWith      TEXT (30),
    DateAndTime  DATETIME,
    Cost         CURRENCY
);

The Query application ignores carriage returns and spaces; an SQL statement can use these characters to improve legibility. SQL is also case insensitive so a statement can use capitalization to make itself clearer. The examples presented here set all SQL commands and keywords in uppercase, and the names of tables, fields, and indexes in mixed uppercase and lowercase.

The following SQL statement creates a table named Books to store information about books. The ID field has the counter data type. When a new record is created in this table and no value for ID is specified, the database automatically assigns the next sequential value to the ID field. The first record will receive the value 1, the next 2, and so forth.

The ID field is also the table’s primary key. The key is named BooksIDKey. Primary key values must always be unique. In this case, that means that no two Books records can have the same value for the ID field. Primary keys are useful because searching for a record using the primary key is faster than searching with other fields.

The table also contains a unique multifield key named BooksAuthorTitleKey. Because this key is unique and includes the Author and Title fields, no two records in the table can have the same combination of Author and Title. Two records could have the same Author or the same Title, but not both. This key makes searching for an author, or for an author and title, faster. It does not make a search for a title alone faster.

CREATE TABLE Books (
    ID        COUNTER CONSTRAINT BooksIDKey PRIMARY KEY,
    Title     TEXT (30),
    Author    TEXT (30),
    Length    INTEGER,
    CONSTRAINT BooksAuthorTitleKey UNIQUE (Author, Title)
);

Once a table has been created, the CREATE INDEX command can build a new index for it. The syntax for the CREATE INDEX command is as follows:

CREATE [UNIQUE] INDEX index_name ON table_name  (field [ASC|DESC], ...) [WITH { PRIMARY |
DISALLOW NULL | IGNORE NULL }]