Visual Basic 6 (VB6)

On this page, I'll focus on Jet's SQL dialect and show you what you can do with SQL (almost everything), what you can't do with Jet SQL (there are several key things that can't be done), and where SQL works better than Jet Data Access Objects for working with a database. If you need a little background on building databases, you may wish to pay a visit to my Introduction to the Relational Database Model page or my Fundamentals of Database Design page.

• Introduction to Jet SQL
• Constructing a Database with SQL
• Querying the Database with SQL
• Managing Data with SQL
• Limitations of Jet SQL
• Summary

The Visual Basic and Access help files provide fairly complete instructions for using SQL statements. (Assuming you know what statements you want to use.) In fact, most of the content on this page is based directly on information available in the help files. Hopefully, I've organized it in a way that makes it a little easier to understand.

• Construct a Database
  Use CREATE TABLE, ALTER TABLE, and CREATE INDEX.
• Create a Relationship
  Use CONSTRAINT with CREATE TABLE or ALTER TABLE.
• Copy a Table
  Use SELECT ... INTO.
• Insert records
  Use INSERT ... VALUES or INSERT ... SELECT.
• Update Records
  Use UPDATE ... SET.
• Delete Records
  Use DELETE.

If you desperately want to avoid dealing with DAO code, you can "cheat" and just use the Database property of the lowly data control. I don't recommend this for any real production code however, since you'll be replacing about five lines of DAO code with a hefty custom control.

In all of the code examples, I'll be using a database object - db - that's assumed to be created elsewhere. Keep this in mind when you see the statement db.Execute. I'm also going to operate under the assumption that you have the good sense to name all of your database objects with names that don't require you to use square brackets everywhere. An easy rule is to give objects names that would be valid identifiers in Basic code. Jet limits the length of most identifiers to 64 characters, but you should try to use much less. (If you think you might ever upsize to SQL Server, use identifiers of 30 characters or less).

Constructing a Database with SQL

• CREATE TABLE
• ALTER TABLE
• CREATE INDEX
• DROP Statement

CREATE TABLE

CREATE TABLE tablename
  (
    fieldname type [size] [CONSTRAINT],
    fieldname type [size],
    [CONSTRAINT]
  )

CREATE TABLE tablename

Creating the Fields

Let's look at a simple example before examining how to create each type of field:

CREATE TABLE Customers
  (
    CustID COUNTER
      CONSTRAINT pkCustomers PRIMARY KEY,
    CustFirstName TEXT 25,
    CustLastName TEXT 25,
    CustAddress TEXT 100,
    CustCity TEXT 50,
    CustState TEXT 2,
    CustZIP TEXT 10,
    CustPhone TEXT 14
  )

  On Error Resume Next
  db.Execute sSQLCreateTable, dbFailOnError
  If Err Then
    ' failed - handle error
  Else
    ' succeeded
  End If

IMHO, using a SQL statement and the Execute method is a considerably simpler method of creating a table than using the DAO library. Using straight DAO code, you would need to call CreateTableDef to get a reference to a TableDef object, then repeatedly call CreateField for each field, using the Append method to add the fields to the table and finally using the Append method to add the TableDef to the database's TableDefs collection. However, there are some things (most unfortunately) that you can't create using the SQL CREATE TABLE statement, so you might need the DAO code anyway. If you need to set properties such as the default value, validation rule, etc., some DAO code will be necessary, but you might find it easier to create the basic table structure using a SQL statement then just set the extra properties with DAO since at that point the TableDef object and all the Field objects will already exist.

Field Datatypes and Sizes

Perhaps you noticed that this table is nearly identical to the help file. If you didn't, searh the VB help file for the topic "Microsoft Jet Database Engine SQL Data Types".

Creating Constraints

Single Column Constraint

CREATE TABLE Customers
  (
    CustID COUNTER
      CONSTRAINT pkCustomers PRIMARY KEY,
    CustName TEXT 25,
    ...
  )

CREATE TABLE BookAuthors
  (
    BABookID LONG,
    BAAuthorID LONG,
    CONSTRAINT pkBookAuthors PRIMARY KEY
      (BABookID, BAAuthorID)
  )

• Primary Key
• Unique Index
• Foreign Key

Let's take a look at creating a foreign key constraint by expanding on the previous definition of the BookAuthors table. The foreign key is specified by using the REFERENCES keyword. In this case, we are enforcing the BABookID key against the primary key of the Books table and enforcing the BAAuthorID key against the primary key of the Authors table.

Single Column Foreign Key Constraint

CREATE TABLE BookAuthors
  (
    BABookID LONG
      CONSTRAINT fkBABookID
      REFERENCES Books (BookID),
    BAAuthorID LONG
      CONSTRAINT fkBAAuthorID
      REFERENCES Authors (AuthorID),
    CONSTRAINT pkBookAuthors PRIMARY KEY
      (BABookID, BAAuthorID)
  )

Multiple Column Foreign Key Constraint

CREATE TABLE PublishedAuthors
  (
    PABookID LONG,
    PAAuthorID LONG,
    PAPubID LONG
      CONSTRAINT fkPAPubID
      REFERENCES Publishers (PubID),
    CONSTRAINT pkPublishedAuthors PRIMARY KEY
      (PABookID, PAAuthorID, PAPubID),
    CONSTRAINT fkPABookAuthors
      FOREIGN KEY (PABookID, PAAuthorID)
      REFERENCES BookAuthors (BABookID, BAAuthorID)
  )

One of the things I like about using SQL (or even DAO) to create relationships is that you can give the foreign key constraint a meaningful name. Although you may never need to refer to the name of the constraint in code, if you do its nice not to have to deal with a name like "Reference86" that Access creates when you use the Relationships window.

ALTER TABLE

ALTER TABLE tablename 
  {ADD {COLUMN field type[(size)] [CONSTRAINT index]
  CONSTRAINT multifieldindex} |
  DROP {COLUMN field | CONSTRAINT indexname} }

I hate to have to put it this way, but if you can't figure the rest of adding a field or constraint on your own, either reread the CREATE TABLE section, consult the help files, or give up in disgrace. :-)

ALTER TABLE Customers
  DROP COLUMN CustName

ALTER TABLE BookAuthors
  DROP CONSTRAINT fkBAAuthorID

Just in case you didn't read the little note above, I'll be thorough in my examples and restore the column and constraint I just dropped.

ALTER TABLE Customers
  ADD COLUMN CustName TEXT 25

ALTER TABLE BookAuthors
  ADD CONSTRAINT fkBAAuthorID
  FOREIGN KEY (BAAuthorID) REFERENCES Authors (AuthorID)

CREATE INDEX

Here's the general syntax:

CREATE [ UNIQUE ] INDEX indexname
  ON tablename
  (fieldname [ASC|DESC][, fieldname [ASC|DESC], ...])
  [WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

• Create an index which is not a constraint.
  A constraint resticts the data which can be entered in the table. You may (in fact should) create indexes strictly for performance reasons which are not constraints on the table data.
• Create a descending index.
  The CONSTRAINT clause creates only ascending indexes.
• Specify the handling of null values in the index.
  Using the WITH clause, you can specify if the index will not allow nulls or simply ignore nulls.

CREATE TABLE Customers
  (
    CustID LONG,
    CustCompanyID LONG
      CONSTRAINT fkCustCompanyID
      REFERENCES Companies (CompanyID),
    CustFirst TEXT 25,
    CustMiddle TEXT 25,
    CustLast TEXT 25,
    CustCity TEXT 50,
    CustState TEXT 2,
    CustZIPCode TEXT 10,
    CustPhone TEXT 14,
    CustSSN TEXT 11
  )

CREATE INDEX pkCustomers
  ON Customers (CustID)
  WITH PRIMARY

CREATE INDEX idxCustCompanyID
  ON Customers (CustCompanyID)
  WITH DISALLOW NULL

CREATE INDEX idxCustName
  ON Customers (CustLast, CustFirst, CustMiddle)
  WITH INGORE NULL

Database theory purists would probably take issue with the statement that the social security number is guaranteed to be unique. I'll leave that discussion to the newsgroups and chat rooms. For now, I'll only state that, IMHO, it's always best to use an arbitrary value for a primary key column.

CREATE UNIQUE INDEX idxCustSSN
  ON Customers (CustSSN)
  WITH DISALLOW NULL

A column such as this which is both a required entry and required to be unique, but not the primary key for the table, is sometimes referred to as an alternate key.

DROP Statement

Here's the general syntax for using DROP:

DROP {TABLE table | INDEX index ON table}

DROP TABLE Customers

DROP INDEX idxCustName ON Customers

SELECT [predicate] { * | table.* | [table.]field1 [, [table.]field2[, ...]]}
[AS alias1 [, alias2 [, ...]]]
FROM tableexpression [, ...] [IN externaldatabase]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]

• Field List
• FROM Clause
• WHERE Clause
• GROUP BY Clause
• HAVING Clause
• ORDER BY Clause
• Other Options

Using the Access query designer isn't really cheating - I do it all the time. You can save yourself a considerable amount of development effort by using this tool. However, you need to understand that the query designer is really just a nice user interface for building a SELECT statement.

Field List

SELECT CustID, CustFirst, CustLast FROM Customers

• The asterisk (*) wildcard.
  You can use SELECT * to retrieve all fields from all tables in the query, or use SELECT table.* to retrieve all fields from a single table.
  SELECT * FROM Customers
  or...
  SELECT CustID, CustCompanyID, Companies.*
  FROM Customers INNER JOIN Companies
  ON Customers.CustCompanyID = Companies.CompanyID
  Note: Use SELECT * with caution. Generally, you should specify only the fields you need for the best performance in your application. Don't make the Jet engine do more work than it needs to do.
• AS clause.
  You can alias the actual field names using AS alias. Additionally, if you are using an expression as a field, you must use an AS clause. Remember that if you alias a field to a name that includes spaces, you must enclose the name in square brackets ([ and ]).
  SELECT CustFirst AS [First Name] FROM Customers
• Expressions as fields.
  You can enter a valid expression as a column using aggregate functions or other simple expressions. Remember that you must alias the expression using AS.
  SELECT CustFirst & " " & CustLast AS [Customer Name] from Customers
• Table.Field Syntax.
  If two or more fields from all available tables in the query have the same name, you must qualify the field names with the table names. This only applies if you are joining tables in the query.
  SELECT CustID, Customers.CompanyID, CompanyName
  FROM Customers INNER JOIN Companies ON
  Customers.CompanyID = Companies.CompanyID

FROM Clause

You can create inner joins using the where clause of a SQL statement, but you must use the RIGHT JOIN or LEFT JOIN syntax to create outer joins.

Joining Tables

Inner Joins

table1 INNER JOIN table2 ON table1.field = table2.field

Jet requires that you fully qualify the field names in the ON clause of inner and outer joins even if both field names are unique in the query. If you don't use the full table.field form, you'll get a syntax error from Jet.

SELECT CustID, CustFirst, CustLast, CompanyID, CompanyName
FROM Customers, Companies
WHERE
CustCompanyID = CompanyID

Oddly enough, you do not need to use the fully qualified field names when creating a join in a where clause. Additionally, if you should happen to forget the WHERE clause with this syntax, you'll get a Cartesian product. If the tables are large, the query could run endlessly.

Outer Joins

Return all records from Companies and only matching records from Employees:

SELECT * 
FROM Companies LEFT JOIN Employees
ON Companies.CompanyID = Employees.EmpCompanyID
...or...
SELECT * 
FROM Employees RIGHT JOIN Companies
ON Employees.EmpCompanyID = Companies.CompanyID

Outer Join Constructions

There may be other outer joins which are not supported, but these are the only ones I've encountered. Don't feel too frustrated by this limitation, however - Jet is much more flexible with outer joins than most database engines.

IN Clause

SELECT * FROM Customers IN "F:\dbfiles\custdb.mdb"

If you're using Access, you can make data from tables available without having the tables appear in the database window by creating a query and using an IN clause to specify another database file. However, if you do this you should keep in mind the fact that you're distributing the path to the external database throughout your SQL instead of consolidating it in the Connect property of attached tables. In a large database, this could be a maintenance nightmare.

WHERE Clause

field comparisonoperator value

Note: A WHERE clause can contain up to 40 expressions linked by logical operators, such as And and Or.

• Field
  Any valid field in the SELECT statement, even a calculated field (although you must use the HAVING clause if the expression is an aggregate function).
• Comparison Operator
  Any valid SQL comparison operator, such as =, >, <, etc. You can also use BETWEEN ... AND for ranges and the LIKE operator to compare the field to an expression containing wildcards. If you are testing for a null, you must use IS NULL or IS NOT NULL.
• Value
  The value you are comparing to. The key to expressing the value component correctly is to be aware of the datatype of the field. (See below.)

Data Types in WHERE Expressions

Comparison Operators

• =
  Values are equal.
• <
  Field is less than value.
• >
  Field is greater than value.
• <=
  Field is less than or equal to value.
• >=
  Field is greater than or equal to value.
• <>
  Field is not equal to value.

• BETWEEN...AND
  This is equivalent to Field >= Lowerbound And Field <= Upperbound.
• LIKE
  LIKE is used to make pattern matching comparisons with or without wildcards. In Jet SQL, the asterisk (*) represents many characters and the question mark (?) represents a single character. There are a variety of other pattern matching methods available. For a complete description of the use of the LIKE operator, search the Visual Basic help files.
  Note: Why the authors of Jet used these wildcard characters instead of the pound sign (#) and underscore (_) used by every other SQL dialect I've ever seen is beyond me, but there it is.
• IS NULL
  Use IS NULL to determine if the field contains data.
• IS NOT NULL
  Use IS NOT NULL to determine if the field does not contain data.

GROUP BY Clause

If, for example, you have a table of orders, and you want to get the total number of orders for each particular item, a SELECT query with a GROUP BY clause might look like this:

SELECT Count(*) AS TotalOrders, OrdItem 
FROM Orders 
GROUP BY OrdItem;

HAVING Clause

Continuing with the previous example, the HAVING clause can be used to restrict the results in a manner different from what can be done with a WHERE clause. If the list of possible values in the OrdItem column are Wheel, Tire, Axle, Bearing, and Spoke, you could use a WHERE clause to restrict the query to a subset of all possible values:

SELECT Count(*) AS TotalOrders, OrdItem
FROM Orders 
WHERE OrdItem = 'Wheel' Or OrdItem = 'Axel' 
GROUP BY OrdItem;

SELECT Count(*) AS TotalOrders, OrdItem
FROM Orders
GROUP BY OrdItem
HAVING Count(*) > 100;

ORDER BY Clause

ORDER BY field1 [ASC | DESC ][, field2 [ASC | DESC ][, ...]]

Remember that if no ORDER BY clause is present, records are unsorted. Normally, they will appear in the order they are entered, but you cannot rely on a specific sort order unless you include an ORDER BY clause.

The ORDER BY clause is normally the last clause in a SELECT query.

Other Options

Predicate

    SELECT DISTINCT CustLast FROM Customers;
		

    SELECT DISTINCTROW CustName 
    FROM Customers INNER JOIN Orders ON Customers.CustID = Orders.OrdCustID
    WHERE OrdTotal > 100
    ORDER BY CustName;
		

    SELECT TOP 10 StateName FROM States ORDER BY StatePopulation;
		

    SELECT TOP 25 PERCENT StateName FROM States ORDER BY StatePopulation;
		

    SELECT TOP 25 PERCENT StateName FROM States ORDER BY StatePopulation DESC;
		

WITH clause

If, for example, you have a table of employee information that includes name, phone, and salary information, you may wish to restrict access to the table so that only managers can see the salary data. Using the security system, you can restrict the read data permission to only the managers. However, you may wish to allow all users to see the name and phone information. By using the WITH OWNERACCESS OPTION clause, you can allow all users to see a subset of the data by building a query as follows:

SELECT EmpName, EmpPhone FROM Employees 
WITH OWNERACCESS OPTION;

WITH OWNERACCESS OPTION is only useful when working with a saved query definition (as opposed to a dynamically generated SQL statement) in a secured, multiuser environment.

Note: Access adds a little confusion to these SQL operations by referring to an INSERT statement as an Append query and a SELECT INTO statement as a Make-Table query. Maybe its just me, but I don't find the word "append" any more obvious than "insert". At least they left update and delete intact. All of these query types are known collectively as action queries.

All of the action queries are run in code using the Execute method of a database object. If you're building database applications using the data control, you probably won't be using these query types often. However, if you're using unbound data, INSERT, UPDATE, and DELETE queries will be as common as SELECT queries in your application.

• INSERT Statement
• UPDATE Statement
• DELETE Statement
• SELECT...INTO

INSERT Statement

Insert records into a destination table from a source table (or tables):

INSERT INTO destination [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM source

INSERT INTO destination [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

Copying Records

Let's say we have two tables, Customers and Prospects which have identical field lists. Instead of working with two tables we decide to combine them into a single table with a Type field that identifies the type of person as a customer or a prospect. Here's the SQL statement:

INSERT INTO Customers (CustID, CustType, CustName, CustPhone)
SELECT ProspectID, "Prospect" As CustType, ProspectName, ProspectPhone
FROM Prospects;

You may want to do this in a situation where you have a relationship defined with the source table and you would like to preserve the links to records in other tables. Here's one way to do this (this assumes that there are values in the source table that would violate the foreign key in the destination table and that both keys are counter fields - note also that this will not necessarily work if you're using random instead of incrementing counters with Jet 3.x):

1. Open the source table and change the data type of the counter field to Long Integer.
2. Using an UPDATE query, add an arbitrary value to the counter field so that the updated keys will no longer violate the primary key in the destination table. If the destination table has a counter field who's largest value is 4000, you could simply add 5000 to each counter value in the source table. In our example, the UPDATE query would be:
   UPDATE Customers SET CustID = CustID + 5000;

Inserting a Single Record

INSERT INTO Customers (CustFirst, CustLast)
VALUES ("Joe", "Garrick");

1. In comparison to using DAO methods for a single record insert, building the SQL statement can get rather complicated - although it is a fairly straightforward coding task.
2. If you are using a counter as the primary key for the table, there is no means of obtaining the new counter value.

UPDATE Statement

UPDATE table
SET field = value
WHERE criteria

Note: It's particularly important to make sure you get the WHERE clause correct in an UPDATE statement. If, for example, you accidentally omitted the WHERE clause you would modify all of the records in the table.

Here we'll update a single record (assume that PgmrID is the primary key):

UPDATE Programmers
SET PgmrSkill = 'Intermediate'
WHERE PgmrID = 10;

UPDATE Programmers
SET PgmrSkill = 'Intermediate'
WHERE PgmrSkill = 'Newbie'

UPDATE Programmers
SET PgmrSkill = 'Guru'

UPDATE Programmers
SET
  PgmrSkill = 'Veteran',
  PgmrStyle = 'Clear'
WHERE
  PgmrID = 10;
	

UPDATE Programmers
SET
  PgmrSkill = 'Newbie'
  PgmrStyle = 'Cryptic'
WHERE
  PgmrYearsExperience = 0;
	

DELETE Statement

DELETE table.*
FROM tableexpression
WHERE criteria

DELETE FROM table
WHERE criteria

DELETE FROM Programmers
WHERE
  PgmrSkill = 'Newbie';

DELETE Programmers.*
FROM
  Programmers INNER JOIN ProjectProgrammers
  ON Programmers.PgmrID = ProjectProgrammers.PPPgmrID
WHERE
  ProjectProgrammers.PPProjectID = 10;

There are a few additional things to keep in mind when using a DELETE statement:

• There is no "undelete" function, so use DELETE with caution.
• If you want to remove all the records from a table, it may be more efficient to simply delete the table using a DROP TABLE statement. However, if you use a DELETE statement, the table structure is left intact. To delete all records from a table, just omit the WHERE clause.
• Deleting records from the one side of a one to many relationship will also delete records from the many side if the relationship was established with cascading deletes.

SELECT...INTO Statement

SELECT field0, field1 
INTO newtable IN externaldatabase
FROM tableexpression

Here is a simple example that will make a new table from a subset of a Customers table:

SELECT CustID, CustFirst, CustLast 
INTO CustomersWithOrders 
FROM 
Customers INNER JOIN Orders 
ON Customers.CustID = Orders.OrdCustID;

• When you create a new table using SELECT...INTO, the new table inherits the field data types and sizes of the source tables, but no other field or table properties are transferred. This includes indexes, rules, relationships, and so on.
• To add data to an existing table, use INSERT INTO rather than SELECT...INTO.
• You can use the ALTER TABLE statement to recreate any indexes, etc., that were lost in creating the new table.

• Rules and Defaults
  While relationships can be established with SQL, you'll need to use DAO to assign some of the extended properties for tables and fields.
• Creating a Database
  There's no Jet equivalent to the SQL CREATE DATABASE statement. You'll need to use DAO to create a new database.
• Manage Security
  Jet SQL does not provide the GRANT and REVOKE statements. Use DAO to manage security.
• Non-Jet Databases
  Jet SQL can only create Jet objects. You need to use DAO to create objects in other databases.
• Creating Queries
  Jet SQL has no CREATE VIEW statement. Use the CreateQueryDef method of the database object and set the SQL property to a valid SQL statement.
• Other Access Objects
  SQL cannot create any of the other Access objects, such as Forms, Reports, Macros, Modules, etc. However, these can be created created programmatically in Access using some of the extended methods available in Access, such as CreateForm, CreateReport, etc. Using some of the macro actions available in code, you can even create a module and write code for it programmatically.

Let's review some of the key points:

• Use SQL Data Definition Language to manipulate the structure of a database. The most commonly used statements are CREATE TABLE, ALTER TABLE, and DROP.
• Use the SELECT statement to retrieve data in queries.
• SQL can change data as well as retrieve it. Use INSERT INTO, UPDATE, DELETE, and SELECT...INTO to create, change, and remove records.
• Jet SQL can't do everything so you'll still need to know your way around the DAO hierarchy to perform some tasks. Notable omissions from Jet SQL are building queries and managing security.
• Use the Execute method of the DAO Database object to execute a SQL statement. In most cases you will want to use the dbFailOnError option so that errors in the execution of the SQL statement can be trapped.

Originally written by Joe Garrick