Introduction to SQL
Structured Query Language (SQL) is a special language for structuring commands that can communicate with databases. Various dialects of SQL statements include Jet SQL, ANSI SQL, and the vendor specific implementations of ANSI SQL such as T-SQL for SQL Server. The syntax of SQL you use varies slightly depending on underlying database. In this section, I cover SQL that is common to most databases.
SQL statements can be used with and without ADO. For example, a SQL statement can be specified as the RecordSource property to which a form can be bound. The examples in this section will illustrate using SQL statements with ADO.
Note that you can also view SQL statements in the Query Designer and have the Query Designer help you create SQL. If you select View 
SQL View from within a Query window, you can see the SQL statement that Access created for your statement. You’ll learn how to write SQL statements without using a designer.
Retrieving Data Using SQL Select Statements
The Select statement allows you to retrieve records from one or more tables in a database and has a basic syntax as follows:
SELECT columns FROM tables WHERE where-clause ORDER BY columns
Select Clause
The Select clause specifies what columns in the underlying table or tables you want to retrieve. To retrieve all records in a certain table, use:
SELECT * To select particular columns in a table, such as txtLastName and txtFirstName, list each name separated by commas:
SELECT txtLastName, txtFirstName Predicates can also be used as part of the Select statement to further restrict what records get returned. For example, you can use the DISTINCT predicate to limit the results to unique records:
SELECT DISTINCT txtLastName The TOP predicate allows you to restrict the number of records returned to a certain number or percent. For example, the following statement limits the number of records to be returned to five:
SELECT TOP 5 intSalesAmt from tblOrders
From Clause
The FROM clause specifies the tables from which the records should be selected. A basic FROM clause is:
FROM tblContacts You can also specify a new name for the table, or a table alias, using the AS predicate, as shown here:
FROM tblContacts As Contacts To retrieve data from more than one table, you use an Inner, Left, or Right Join to join the tables together. Here is the basic join syntax:
FROM <left table name> <join type> <right table name> ON <left join field name> = <right join field name> The following shows the differences in each type of join:
-
INNER JOIN – Combines the records from the two tables where the joined values in both tables match.
-
LEFT JOIN – Includes all records from the table on the left side of the clause even if there are no matching records from the table on the right side.
-
RIGHT JOIN – Includes all records from the table on the right side of the clause even if there are no matching records from the table on the left side.
An example of an inner join is:
FROM tblContacts INNER JOIN tblSuppliers ON tblContacts.LastName = tblSuppliers.LastName The previous example returns records from the tblContacts table and from the tblSuppliers table where a match exists on the joined values in both tables.
An example of a left join is:
FROM tblContacts LEFT JOIN tblSuppliers ON tblContacts.LastName = tblSuppliers.LastName The left join example above returns all records from the tblContacts table, even if there are no matching records in the tblSuppliers table.
Where Clause
The Where clause is used to limit the rows the SQL Statement will retrieve. The Where clause is optional. If the Where clause is included, it can be followed by comparison operators, such as =, >, <, BETWEEN, IN, and string expressions such as Is Null. What follows is an example of a Where clause that uses comparison operators:
WHERE LastName = 'Doe' AND FirstName = 'John' Expressions in a Where clause are separated by And statements. If the Where clause is omitted, you will get all records in the select without restriction. There are times when this is exactly what you want.
Order By Clause
The Order By clause allows you to specify in what order the records should be sorted when they are retrieved. For example, suppose that you want to sort the results by last name and then first name. You could use the following Order By clause:
ORDER BY txtLastName, txtFirstName The previous example will return records in ascending order, which is the default. To return the records in descending order, use the DESC keyword:
ORDER BY txtLastName, txtFirstName DESC
Subqueries
Subqueries enable you to nest one Select statement within another Select statement. The following example selects those records in tblContacts that do not have the same last name in the tblContactsArchive table:
SELECT txtLastName, txtFirstName FROM tblContacts WHERE txtLastName Not In (SELECT txtLastName FROM tblContactsArchive)
Union
The Union statement allows you to combine data from two tables that have similar structures into the output. For example, suppose that you have a tblContacts table containing active contacts and a tblContactsArchive table that contains archived contacts. To retrieve a set of records that combines data from both of these tables, you use the Union statement:
SELECT txtLastName, txtFirstName FROM tblContacts UNION ALL SELECT txtLastName, txtFirstName FROM tblContactsArchive
Using SQL and ADO to Populate a Recordset
Now that you have learned the basic syntax for Select statements, let’s look at an example that uses a SQL statement along with ADO. The following example opens a new recordset and uses a SQL statement as the source for the Open method:
Sub TestSQLSelect() Dim rsContacts As ADODB.Recordset 'create a new instance of a recordset Set rsContacts = New ADODB.Recordset 'set various properties of the recordset With rsContacts .CursorType = adOpenStatic 'open the recordset based on SQL statement using the existing connection .Open "SELECT * FROM tblContacts", CurrentProject.Connection End With 'loop through the recordset and display some info for 'each record Do Until rsContacts.EOF Debug.Print "Contact Id: " & rsContacts!intContactId & _ " Last Name: " & rsContacts!txtLastName & _ " First Name: " & rsContacts!txtFirstName rsContacts.MoveNext Loop 'close the recordset rsContacts.Close 'set the recordset and connection to nothing Set rsContacts = Nothing End Sub A portion of each record that is returned in the recordset is then printed in the Immediate Window, as shown in Figure 5-14 to demonstrate that the SQL statement selected what you expected it to select.
Figure 5-14
The prior example retrieves data from a common data source: a database table. However, ADO enables you to connect to various data sources, and it can treat those data sources as virtual tables. For example, you can connect to Microsoft Outlook using a SQL statement and ADO to populate a recordset. Here is an example of how you can retrieve data from an Outlook Calendar on Microsoft Exchange and print the results to the Immediate Window:
Sub RetrieveCalendar() Dim strConnection As String Dim cnConn As ADODB.Connection Dim rsCalendar As ADODB.Recordset Set cnConn = New ADODB.Connection Set rsCalendar = New ADODB.Recordset With cnConn .Provider = "Microsoft.ACE.OLEDB.12.0" .ConnectionString = "Exchange 4.0;" _ & "MAPILEVEL=Mailbox - Gosnell, Denise|;" _ & "PROFILE=DMG;" _ & "TABLETYPE=0;DATABASE=C:\WINDOWS\TEMP\;" .Open End With With rsCalendar .Open "Select * from Calendar", cnConn, adOpenStatic, _ adLockReadOnly 'print all records in calendar Do While Not rsCalendar.EOF Debug.Print rsCalendar(3).Name & ": " & rsCalendar(3).Value Debug.Print rsCalendar(10).Name & ": " & rsCalendar(10).Value Debug.Print vbCrLf rsCalendar.MoveNext Loop .Close End With Set rsCalendar = Nothing cnConn.Close Set cnConn = Nothing End Sub In the preceding example, the Mailbox and Profile as used by your Outlook database should be specified. When you run the preceding code with a valid Mailbox and Profile, you see results similar to the following in the Immediate Window:
Subject: Doctor's Appointment Received: 5/24/2004 2:30:18 PM Subject: Meet Benita for lunch Received: 5/25/2004 10:06:52 AM Subject: Jazz Fest Training Meeting Received: 6/10/2004 2:28:51 PM
Inserting Data Using SQL Insert Statements
The Insert statement is used to insert data into a table. The syntax for an Insert statement is:
INSERT INTO tablename (fieldname1, fieldname2, ... fieldnameN) VALUES (value1, value2, ... valueN) The Insert clause can be used to insert all or part of a record into the database. For example, suppose that the tblContacts table only has txtLastName, txtFirstName, txtWorkPhone, and txtHome Phone fields. To insert all of the record into the database in such a scenario, you could use the following:
INSERT INTO tblContacts (txtLastName, txtFirstName, txtWorkPhone, txtHomePhone) VALUES ('Doe', 'Jane', '317-123-4567', '317-987-6543') When adding a partial row for a table, you must specify at least the required fields in the table. Otherwise, the syntax is the same as the example shown previously.
As I will illustrate in detail in a later example, you can use SQL statements as part of a Command object that gets executed against the database to insert new records.
Inserting Results of a Select into a Table
Sometimes you must insert the results from a Select statement into an existing table. As long as the data types correspond to each other, data from one or more tables can easily be inserted into another table. The basic syntax is shown here:
INSERT INTO destination (fieldname1, fieldname2, ... fieldnameN) SELECT fieldname1, fieldname2, ... fieldnameN FROM source [WHERE criteria] If you wanted to insert the entire contents of the tblContacts table into a tblContactsArchive table, you could use the following statement:
INSERT INTO tblContactsArchive SELECT * FROM tblContacts Alternatively, you could list each field.
Updating Data Using SQL Statements
Update statements can be used to update existing records in the database. The basic syntax for Update statements is:
UPDATE tablename SET column = value WHERE criteria Suppose, for example, that Jane Doe got married again and must now update her last name to Dawson. You could issue the following Update statement to update her existing record:
UPDATE tblContacts SET txtLastName = 'Dawson' WHERE txtLastName = 'Doe' AND txtFirstName = 'Jane' As I will illustrate in detail in a later example, you can use SQL statements as part of a Command object that gets executed against the database to update existing records.
Deleting Data Using SQL Statements
Delete statements can be used to delete existing records from the database. The basic syntax for Delete statements is:
DELETE FROM tablename WHERE criteria To delete Jane Doe’s record from the database altogether, you could use the following statement:
DELETE from tblContacts WHERE txtLastName = 'Doe' AND txtFirstName = 'Jane' As I will illustrate in the following example, you can use SQL statements as part of a Command object that gets executed against the database to delete existing records.
Try It Out-Modify the Unbound Contacts Form to Use SQL
 |
Now turn your attention to the final example of working with disconnected recordsets. In this case, you will modify our existing Contacts form to use SQL statements.
-
Select frmContactsUnbound and right-click and select Copy. Select the Paste option, and name the new copy of the form frmContactsUnboundSQL.
-
Modify the cmdSaveAll Caption property to Save Current Record. The form should look similar to the one shown in Figure 5-15.
Figure 5-15 -
Add an additional variable declaration to the General Declarations section of the form, as shown here:
Dim rsContacts As ADODB.Recordset Dim cnCh5 As ADODB.Connection Dim strConnection As String Dim blnAddMode As Boolean -
Delete the existing SaveAllRecords procedure.
-
Delete the existing Form_Unload procedure.
-
Replace the existing AddRecord procedure with the following:
Sub AddRecord() 'set add mode to true blnAddMode = True 'clear the current controls on the form so the 'user can fill in values for the new record Call ClearControlsOnForm End Sub -
Replace the existing DeleteRecord procedure with the following:
Sub DeleteRecord() 'don't let the user issue a delete command if in add mode If blnAddMode = True Then Exit Sub End If Dim intResponse As Integer 'confirm that user really wants to delete record intResponse = MsgBox("Are you sure you want to delete this record?", vbYesNo) 'if the user cancels delete, then exit this procedure If intResponse = vbNo Then Exit Sub End If 'declare and create new command object Dim cmdCommand As ADODB.Command Set cmdCommand = New ADODB.Command 'create a new connection instance and open it using the connection string Set cnCh5 = New ADODB.Connection cnCh5.Open strConnection 'declare variable to store current contact Dim intCurContact As Integer intCurContact = 0 'generate SQL command to delete current record Dim strSQL As String strSQL = "DELETE FROM tblContacts WHERE intContactId = " & rsContacts!intContactId 'set the command to the current connection Set cmdCommand.ActiveConnection = cnCh5 'set the delete SQL statement to the command text cmdCommand.CommandText = strSQL 'execute the delete command against the database cmdCommand.Execute 'move to the next record in the local recordset since the 'current one is being deleted If Not rsContacts.EOF Then rsContacts.MoveNext 'save the id of the current (next) record intCurContact = rsContacts!intContactId End If 'while connected to the database, go ahead and 'repopulate the recordset to make sure it contains 'the most current values from the database. Set rsContacts.ActiveConnection = cnCh5 rsContacts.Requery Set rsContacts.ActiveConnection = Nothing 'move back to the contact that was current before the 'requery rsContacts.Find "[intContactId] = " & intCurContact 'populate the controls on the form Call PopulateControlsOnForm End Sub
-
Replace the existing SaveCurrentRecord procedure with the following:
Sub SaveCurrentRecord() Dim cmdCommand As ADODB.Command Set cmdCommand = New ADODB.Command Dim strSQL As String If Not rsContacts.BOF And Not rsContacts.EOF Then 'create a new connection instance and open it using the connection string Set cnCh5 = New ADODB.Connection cnCh5.Open strConnection Dim intCurContact As Integer intCurContact = 0 'if adding a new record If blnAddMode = True Then 'create SQL to insert a new record into the database 'containing the values on the form strSQL = "INSERT INTO tblContacts(" & _ "txtLastName, txtFirstName, txtMiddleName, " & _ "txtTitle, txtAddress1, txtAddress2, " & _ "txtCity, txtState, txtZip, " & _ "txtWorkPhone, txtHomePhone, txtCellPhone) " & _ "VALUES (" & _ "'" & Me.txtLastName & "', " & _ "'" & Me.txtFirstName & "', " & _ "'" & Me.txtMiddleName & "', " & _ "'" & Me.txtTitle & "', " & _ "'" & Me.txtAddress1 & "', " & _ "'" & Me.txtAddress2 & "', " & _ "'" & Me.txtCity & "', " & _ "'" & Me.txtState & "', " & _ "'" & Me.txtZip & "', " & _ "'" & Me.txtWorkPhone & "', " & _ "'" & Me.txtHomePhone & "', " & _ "'" & Me.txtCellPhone & "') " Else 'create SQL to update the existing record in the 'database with the values on the form strSQL = "UPDATE tblContacts SET " & _ "txtLastName = '" & Me.txtLastName & "', " & _ "txtFirstName = '" & Me.txtFirstName & "', " & _ "txtMiddleName = '" & Me.txtMiddleName & "', " & _ "txtTitle = '" & Me.txtTitle & "', " & _ "txtAddress1 = '" & Me.txtAddress1 & "', " & _ "txtAddress2 = '" & Me.txtAddress2 & "', " & _ "txtCity = '" & Me.txtCity & "', " & _ "txtState = '" & Me.txtState & "', " & _ "txtZip = '" & Me.txtZip & "', " & _ "txtWorkPhone = '" & Me.txtWorkPhone & "', " & _ "txtHomePhone = '" & Me.txtHomePhone & "', " & _ "txtCellPhone = '" & Me.txtCellPhone & "' " & _ "WHERE intContactId = " & rsContacts!intContactId 'save the id of the current record intCurContact = rsContacts!intContactId End If 'set the command to the current connection Set cmdCommand.ActiveConnection = cnCh5 'set the insert or update SQL statement to the command text cmdCommand.CommandText = strSQL 'execute the delete command against the database cmdCommand.Execute 'while connected to the database, go ahead and 'repopulate the recordset to make sure it contains 'the most current values from the database. Set rsContacts.ActiveConnection = cnCh5 rsContacts.Requery Set rsContacts.ActiveConnection = Nothing 'move back to the contact that was current before the 'requery If intCurContact > 0 Then 'move back to the contact that was just updated rsContacts.Find "[intContactId] = " & intCurContact Else 'if just added new record, move to the beginning of 'the recordset rsContacts.MoveFirst End If 'reset add mode flag to false blnAddMode = False 'populate the controls on the form Call PopulateControlsOnForm End If End Sub
-
Replace the existing MoveToFirstRecord, MoveToNextRecord, MoveToPreviousRecord, and MoveToLastRecord procedures with the following:
Sub MoveToFirstRecord() 'move to the first record in the local disconnected recordset If Not rsContacts.BOF And Not rsContacts.EOF Then rsContacts.MoveFirst 'populate the controls on the form with the current record Call PopulateControlsOnForm blnAddMode = False End If End Sub Sub MoveToLastRecord() 'move to the last record in the local disconnected recordset If Not rsContacts.BOF And Not rsContacts.EOF Then rsContacts.MoveLast 'populate the controls on the form with the current record Call PopulateControlsOnForm blnAddMode = False End If End Sub Sub MoveToPreviousRecord() 'move to the previous record in the local disconnected recordset 'if not already at the beginning If Not rsContacts.BOF Then rsContacts.MovePrevious 'populate the controls on the form with the current record Call PopulateControlsOnForm blnAddMode = False End If End Sub Sub MoveToNextRecord() 'move to the next record in the local disconnected recordset 'if not already at the end If Not rsContacts.EOF Then rsContacts.MoveNext 'populate the controls on the form with the current record Call PopulateControlsOnForm blnAddMode = False End If End Sub
-
Run the form and you should see a screen similar to Figure 5-16.
Figure 5-16 -
Select the Delete button on the form, and you should see a screen similar to Figure 5-17.
Figure 5-17 -
Select the Yes option to delete the record.
How It Works
First, you created a copy of the frmContactsUnbound and named it frmContactsUnboundSQL. You can now make changes to this version and still retain the prior one as an example to review as well. Next, you modified the Caption property of the cmdSaveAll button and deleted a few procedures that are no longer needed for the current example. Then, you replaced a few procedures, starting with AddRecord. The AddRecord procedure now sets a Boolean value to True to indicate that you’re in Add mode. The controls on the form are then cleared so the user can input values into the empty controls.
Sub AddRecord() 'set add mode to true blnAddMode = True 'clear the current controls on the form so the 'user can fill in values for the new record Call ClearControlsOnForm End Sub
Next, you replaced the existing DeleteRecord procedure with code that starts with confirming that the user really wants to delete the record:
Sub DeleteRecord() 'don't let the user issue a delete command if in add mode If blnAddMode = True Then Exit Sub End If Dim intResponse As Integer 'confirm that user really wants to delete record intResponse = MsgBox("Are you sure you want to delete this record?", vbYesNo) 'if the user cancels delete, then exit this procedure If intResponse = vbNo Then Exit Sub End If Assuming that the user did not cancel the delete operation, you declared a new Command object and opened a new connection:
'declare and create new command object Dim cmdCommand As ADODB.Command Set cmdCommand = New ADODB.Command 'create a new connection instance and open it using the connection string Set cnCh5 = New ADODB.Connection cnCh5.Open strConnection
The current contact location was stored to be used later:
'declare variable to store current contact Dim intCurContact As Integer intCurContact = 0
You then created the SQL statement for the Delete command to delete the record based on the intContactId value of the current record:
'generate SQL command to delete current record Dim strSQL As String strSQL = "DELETE FROM tblContacts WHERE intContactId = " & rsContacts!intContactId
You then set the Command object to the existing connection, and the CommandText property was assigned to the SQL statement. When the Execute method executed, the SQL statement ran against the database:
'set the command to the current connection Set cmdCommand.ActiveConnection = cnCh5 'set the delete SQL statement to the command text cmdCommand.CommandText = strSQL 'execute the delete command against the database cmdCommand.Execute
Next, the code moved to the next record because the prior one was just deleted:
'move to the next record in the local recordset since the 'current one is being deleted If Not rsContacts.EOF Then rsContacts.MoveNext 'save the id of the current (next) record intCurContact = rsContacts!intContactId End If
Although connected to the database, the Requery method of the recordset was executed to refresh the recordset with the current values in the database:
'while connected to the database, go ahead and 'repopulate the recordset to make sure it contains 'the most current values from the database. Set rsContacts.ActiveConnection = cnCh5 rsContacts.Requery Set rsContacts.ActiveConnection = Nothing
The contact that was current before the requery was then located:
'move back to the contact that was current before the 'requery rsContacts.Find "[intContactId] = " & intCurContact
At the end of the DeleteRecord procedure, the controls on the form were populated with the current record:
'populate the controls on the form Call PopulateControlsOnForm End Sub
After replacing the DeleteRecord procedure, you next replaced the SaveCurrentRecord procedure. The procedure began by declaring a Command object and a variable for the SQL statement:
Sub SaveCurrentRecord() Dim cmdCommand As ADODB.Command Set cmdCommand = New ADODB.Command Dim strSQL As String
If records existed in the recordset, the connection was opened:
If Not rsContacts.BOF And Not rsContacts.EOF Then 'create a new connection instance and open it using the connection string Set cnCh5 = New ADODB.Connection cnCh5.Open strConnection Dim intCurContact As Integer intCurContact = 0
If the SaveAll procedure was being called because you’re in Add New Record mode, you created a SQL statement that inserted the current values on the form into the database:
'if adding a new record If blnAddMode = True Then 'create SQL to insert a new record into the database 'containing the values on the form strSQL = "INSERT INTO tblContacts(" & _ "txtLastName, txtFirstName, txtMiddleName, " & _ "txtTitle, txtAddress1, txtAddress2, " & _ "txtCity, txtState, txtZip, " & _ "txtWorkPhone, txtHomePhone, txtCellPhone) " & _ "VALUES (" & _ "'" & Me.txtLastName & "', " & _ "'" & Me.txtFirstName & "', " & _ "'" & Me.txtMiddleName & "', " & _ "'" & Me.txtTitle & "', " & _ "'" & Me.txtAddress1 & "', " & _ "'" & Me.txtAddress2 & "', " & _ "'" & Me.txtCity & "', " & _ "'" & Me.txtState & "', " & _ "'" & Me.txtZip & "', " & _ "'" & Me.txtWorkPhone & "', " & _ "'" & Me.txtHomePhone & "', " & _ "'" & Me.txtCellPhone & "') " If, however, you were in update mode in order to save changes to an existing record, you must create the SQL statement that will update the existing record in the database with the values in the controls on the form:
Else 'create SQL to update the existing record in the 'database with the values on the form strSQL = "UPDATE tblContacts SET " & _ "txtLastName = '" & Me.txtLastName & "', " & _ "txtFirstName = '" & Me.txtFirstName & "', " & _ "txtMiddleName = '" & Me.txtMiddleName & "', " & _ "txtTitle = '" & Me.txtTitle & "', " & _ "txtAddress1 = '" & Me.txtAddress1 & "', " & _ "txtAddress2 = '" & Me.txtAddress2 & "', " & _ "txtCity = '" & Me.txtCity & "', " & _ "txtState = '" & Me.txtState & "', " & _ "txtZip = '" & Me.txtZip & "', " & _ "txtWorkPhone = '" & Me.txtWorkPhone & "', " & _ "txtHomePhone = '" & Me.txtHomePhone & "', " & _ "txtCellPhone = '" & Me.txtCellPhone & "' " & _ "WHERE intContactId = " & rsContacts!intContactId 'save the id of the current record intCurContact = rsContacts!intContactId End If
Whether you were in Add New or Update mode, you were now ready to execute the SQL statement against the database by assigning the CommandText property to the SQL statement and executing the Execute method:
'set the command to the current connection Set cmdCommand.ActiveConnection = cnCh5 'set the insert or update SQL statement to the command text cmdCommand.CommandText = strSQL 'execute the delete command against the database cmdCommand.Execute
While still connected to the database, you repopulated the recordset with the most current values and then disconnected the recordset again:
'while connected to the database, go ahead and 'repopulate the recordset to make sure it contains 'the most current values from the database. Set rsContacts.ActiveConnection = cnCh5 rsContacts.Requery Set rsContacts.ActiveConnection = Nothing
Next, you moved back to the current record if you were in Update mode:
'move back to the contact that was current before the 'requery If intCurContact > 0 Then 'move back to the contact that was just updated rsContacts.Find "[intContactId] = " & intCurContact Else 'if just added new record, move to the beginning of 'the recordset rsContacts.MoveFirst End If
Finally, you reset the Add mode flag to False because you were no longer in Add mode under any circumstance. You set the controls on the form to display the values in the current record of the recordset.
'reset add mode flag to false blnAddMode = False 'populate the controls on the form Call PopulateControlsOnForm End If End Sub
You made a few minor changes to the MoveToFirstRecord, MoveToNextRecord, MoveToPreviousRecord, and MoveToLastRecord procedures to remove calls to SaveCurrentRecord and to add a statement setting the blnAddMode flag to False. These procedures were just replaced again for simplicity, but not a lot changed.
Sub MoveToFirstRecord() 'move to the first record in the local disconnected recordset If Not rsContacts.BOF And Not rsContacts.EOF Then rsContacts.MoveFirst 'populate the controls on the form with the current record Call PopulateControlsOnForm blnAddMode = False End If End Sub
You then ran the form and manipulated the database, including deleting an existing record. These procedures ran to issue SQL statements against the database to insert, update, and delete records, and then the disconnected recordset was repopulated with the current values in the database. This is yet another way to write code that allows your forms to interact with data in underlying data sources in a disconnected fashion.
|
EAN: 2147483647
Pages: 143