Programming Queries with SQL and ADO
SQL is a widely accepted language for manipulating relational data. Most remote relational database manager engines rely on SQL, and just about every query you create in Design view can also be created programmatically using SQL. With a working knowledge of generic SQL (often called SQL-92), you can design more efficient query statements for your queries in the natural language of those database engines.
The following sections will explore common uses for SQL in Access applications. First, we'll examine the SQL SELECT statements. Then we'll look at data definition functions. SQL statements that implement data definition functions can build directly on the SELECT statement syntax and add a new class of functionality. Finally, we'll review SQL techniques for generating and running views and stored procedures.
SELECT Statements
A SQL SELECT statement returns a set of rows from one or more database tables. You can use related clauses such as WHERE and ORDER BY to dramatically extend how the SELECT statement selects and displays rows in its return set. SQL aggregate functions offer additional options for managing the return set.
A basic SELECT statement designates one or more columns from a record source. It has this general format:
SELECT FieldList FROM RecordSource |
Field lists
You select a subset of the fields in a record source by specifying a comma-separated list for the FieldList column. You select all the fields in the record source by using an asterisk. If one or more of the fields in FieldList have the same name but are in different tables, you must precede the field names with their table name and a period. If the table name contains one or more blanks, you must embed the table name in brackets.
Joins
To specify how two tables relate to one another as record sources for a SELECT statement's return set, you use joins. The SELECT statement's FROM clause specifies any joins for combining two or more tables. A join must at least specify on which columns to combine tables and how to determine a match between the tables. (You nest joins within one another to specify relationships between more than two tables.) Typical join types are inner joins, which select matching records from each of two tables; left joins, which include all records from the first table and only matching records from the second table; and right joins, which include all records from the second table and only matching records from the first table.
WHERE clauses
You use the SELECT statement's WHERE clause to specify criteria for selecting rows from a record source. Unlike the FROM clause, the WHERE clause is optional. You use it only to restrict the records in a return set.
LIKE operators
You use the optional LIKE operator to find values in fields that match a specified pattern. This operator supports pattern matching across the Jet and SQL Server database engines, but you must use caution because wildcard characters vary between the Jet file-server engine and many client/server engines. For example, Microsoft SQL Server uses % and ^ as wildcard characters instead of the * and ? characters that Jet uses.
ORDER BY clauses
You use the optional ORDER BY clause with the SELECT statement to return a recordset in an order other than by primary key. You list the first field on which you want to sort immediately after the ORDER BY keyword. The default sort order is ascending (ASC), but you can specify a descending order (DESC). If you want to sort on more than one field, you delimit the field name and sort order pairs with commas.
GROUP BY and HAVING clauses
The optional GROUP BY and HAVING clauses complement one another. They work with SQL aggregate functions such as COUNT and SUM. The GROUP BY clause specifies fields over which to compute aggregates. The HAVING clause is similar to the WHERE clause; it restricts the recordset returned by the GROUP BY clause to records that match given criteria.
DISTINCT keywords
One way to limit the number of records that a SELECT statement returns is with the DISTINCT keyword, which eliminates duplicate data in the selected fields. You place the DISTINCT keyword between the SELECT keyword and the field list. The general format for this keyword is:
Select Distinct FieldList from RecordSource |
Using the Command object to execute SQL
You use the ADODB Command object to execute a SQL command against a data source by following this procedure:
- Set the Command object's CommandText property to the SQL statement you want to execute.
- Set the object's CommandType property to adCmdText to optimize performance.
- Invoke the Command object's Execute method to generate the SQL return set.
- Open a Recordset object based on the Command object so that you can manipulate the SQL return set in your application.
The following code uses a Command object to execute a simple SQL statement:
Sub MySelect() Dim cnn1 As New ADODB.Connection Dim cmd1 As ADODB.Command Dim rst1 As ADODB.Recordset 'Create the connection to another database. cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Files\Microsoft Office\Office\" & _ "Samples\Northwind.mdb;" 'Define and execute command to select all ProductID field 'values from a single table. Set cmd1 = New ADODB.Command With cmd1 .ActiveConnection = cnn1 .CommandText = "Select ProductID from [Order Details]" .CommandType = adCmdText .Execute End With 'Assign the return set to a recordset. Set rst1 = New ADODB.Recordset rst1.CursorType = adOpenStatic rst1.LockType = adLockReadOnly rst1.Open cmd1 Debug.Print rst1.RecordCount End Sub |
MySelect prints the number of records in the return set (2155) to the Immediate window. The return set from the SQL statement includes each record in the Order Details table. By default, the SELECT statement selects all the records in the underlying record source, and MySelect's SQL statement contains no restrictions on the records to return.
Example using INNER JOIN
The following SELECT statement illustrates a number of new features. First, it shows the syntax for an inner join between the Order Details and the Products tables. Second, it enumerates the rows in the return set and prints them to the Immediate window.
Sub MySelect3() Dim cnn1 As New ADODB.Connection Dim cmd1 As ADODB.Command Dim rst1 As ADODB.Recordset, int1 As Integer 'Create the connection to another database. cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Files\Microsoft Office\Office\" & _ "Samples\Northwind.mdb;" 'Define and execute command to select distinct ProductName field 'values from a pair of joined tables. Set cmd1 = New ADODB.Command With cmd1 .ActiveConnection = cnn1 .CommandText = "Select Distinct ProductName from " & _ "[Order Details] Inner Join Products on " & _ "[Order Details].ProductID = Products.ProductID" .CommandType = adCmdText .Execute End With 'Assign the return set to a recordset 'and print the results to the Immediate window. Set rst1 = New ADODB.Recordset rst1.CursorType = adOpenStatic rst1.LockType = adLockReadOnly rst1.Open cmd1 Debug.Print rst1.RecordCount For int1 = 1 To rst1.RecordCount Debug.Print rst1("ProductName") rst1.MoveNext Next int1 End Sub |
MySelect3's SELECT statement draws on fields from two tables—not just one, as in the preceding sample. The statement performs an inner join on the Products table and the Order Details table to match the ProductID fields from both tables. Because the SQL statement uses the DISTINCT keyword, no duplicate ProductName field values are returned. Finally, the inner join enables the procedure to print the descriptive product name that corresponds to each product ID code.
Example using SUM and ORDER BY
The following procedure uses the SQL aggregate function SUM and the joining and sorting options. It computes the sum of the extended price for each product in the Order Details table. The return set for the statement orders the outcome by how much revenue the products generate (from most to least).
Sub MySelect4() Dim cnn1 As New ADODB.Connection Dim cmd1 As ADODB.Command Dim rst1 As ADODB.Recordset, int1 As Integer 'Create the connection to another database. cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Files\Microsoft Office\Office\" & _ "Samples\Northwind.mdb;" 'Define and execute command to select distinct ProductName field 'values from a pair of joined tables; compute extended price. Set cmd1 = New ADODB.Command With cmd1 .ActiveConnection = cnn1 .CommandText = "Select Distinct Products.ProductName, " & _ "Sum([Order Details].[UnitPrice]*" & _ "[Order Details].[Quantity]*" & _ "(1-[Order Details].[Discount])) As [Extended Price] " & _ "From Products Inner Join [Order Details] On " & _ "Products.ProductID = [Order Details].ProductID " & _ "Group By Products.ProductName " & _ "Order By Sum([Order Details].[UnitPrice]*" & _ "[Order Details].[Quantity]*" & _ "(1-[Order Details].[Discount])) Desc" .CommandType = adCmdText .Execute End With 'Assign Select statement return set to a recordset 'and print the results to the Immediate window. Set rst1 = New ADODB.Recordset rst1.CursorType = adOpenKeyset rst1.Open cmd1 Debug.Print rst1.RecordCount For int1 = 1 To rst1.RecordCount Debug.Print rst1("ProductName"), _ rst1.Fields("Extended Price") rst1.MoveNext Next int1 End Sub |
MySelect4 creates a calculated field in code. The procedure uses the SUM function with a GROUP BY clause to calculate the revenue generated by each product. Without the GROUP BY clause, the code would create the calculated field for each row in the original table but would not provide summary results by group. (It would calculate the total revenue of all products instead of the total revenue of each individual product.)
The ORDER BY clause controls the sort order of the return set. Although you could write this code more succinctly (it duplicates the code that computes the extended price), as written it is easy to understand and is similar to code produced by the query design grid.
Data Definition Functions
There are several ways to approach data definition with SQL statements. This section examines how to create a table with make-table queries. Make-table queries are action queries that create a new table based on the result set of an existing query. This is a data definition function.
This section also demonstrates how to modify autoincrement fields with the ALTER TABLE and ALTER COLUMN SQL keywords. Jet 4 SQL introduces a new identity field data type to facilitate this objective. Jet 4 supports setting step and start values for these counter fields. (You can reset them at any time.) The sample procedures in this section are complete by themselves, but they are even more valuable if you examine them in light of the discussion in Chapter 3. ALTER TABLE and ALTER COLUMN are part of a whole set of keywords (for example, CREATE TABLE, CREATE INDEX, and DROP) that directly support data definition functions through SQL.
SELECT...INTO
The syntax for a make-table query in SQL is as follows:
SELECT FieldList INTO NewTableName FROM RecordSource |
If you create a new table in a database file other than the current one, add an IN clause after the INTO clause and before the FROM clause. You use the IN clause to designate the path and filename of the database that will hold the output from the SELECT...INTO statement.
RecordSource can consist of one table, one query, or several of either, and it should contain all the fields that you want in your new table. The SELECT...INTO statement copies the design of the specified fields and their data to the new table. You cannot create any new fields with the SELECT...INTO statement. You can easily prototype this type of query by creating it in Design view and then switching to SQL view and copying the SQL statement into a procedure that uses an ADO object. With this approach, you don't have to design the SQL statement.
The following procedure uses a simple SELECT...INTO statement. It backs up the FamilyMembers table in the current database to a new table named FMBackup.
Sub MyMakeTable() Dim cnn1 As ADODB.Connection 'Reference connection. Set cnn1 = CurrentProject.Connection 'Execute SQL for maketable query. cnn1.Execute "SELECT FamilyMembers.* INTO " & _ "FMBackup FROM FamilyMembers" 'This procedure fails if FMBackup already exists. End Sub |
Unfortunately, there is generally more to creating and running a SELECT...INTO statement than these two ADO statements. For example, this procedure can fail if the FMBackup table already exists. It can also fail if another user opens either table. These and other complications demand error trapping. The following sample illustrates how you might start error processing for an application that uses a procedure with a SELECT...INTO statement.
Sub MyMakeTable2() On Error GoTo Make2Err: Dim cnn1 As ADODB.Connection 'Reference connection. Set cnn1 = CurrentProject.Connection 'Test for unanticipated errors. ' Err.Raise 1 'Execute SQL for make-table query. cnn1.Execute "SELECT FamilyMembers.* INTO " & _ "FMBackup FROM FamilyMembers" Make2Exit: 'Close the connection and set it equal to nothing 'and exit the sub. cnn1.Close Set cnn1 = Nothing Exit Sub Make2Err: 'Trap for table already exists. If Err.Number = -2147217900 Then cnn1.Execute "Drop Table FMBackup" Resume Else MsgBox "The program generated an unanticipated " & _ "error. Its number and description are " & _ Err.Number & ": " & Err.Description, vbCritical, _ "Programming Microsoft Access 2000" End If Resume Make2Exit End Sub |
This procedure accomplishes the same task as the original procedure, but it does not fail if the FMBackup table already exists. The error handler recognizes this type of failure and deletes the existing table before executing the command again. If another error occurs, the application presents the essential information in a message box so that the user can inform the developer of the problem.
The procedure includes one more error-trapping feature. Immediately after setting the connection, the code can call the Raise method of the Err object. (The feature is commented out, but I removed the comment to evaluate the performance of my error trapping.) This use of the Err object creates an artificial error that helps indicate how the procedure should be designed to respond to unanticipated errors.
The sample also closes the connection and sets the Connection object to Nothing. These two actions have similar and complementary purposes. When you close a connection, the resources used for the connection become available to the rest of the system. The object remains in memory, however, and its properties are still intact. Later, you can open the same Connection object with its former property settings or new ones. You must set an object equal to Nothing to remove it from memory.
Using ALTER TABLE and ALTER COLUMN to reset Autoincrement fields
Many Access developers will be thrilled by the new control that they gain over Counter data types that serve as identity fields for records. Chapter 3 already showed how to set the start and step values for a counter field. While this is impressive, you have more complete control over these fields than just the initial setting of values. The following procedure resets the start and step values of the Counter data type.
Sub ResetCounter(intStart, intStep) Dim cnn1 As ADODB.Connection Dim strSQL 'Reference connection and execute SQL for view. Set cnn1 = CurrentProject.Connection 'Create SQL string that references passed arguments. strSQL = "ALTER TABLE FamilyMemberNames " & _ "ALTER COLUMN FamID Identity " & _ "(" & intStart & "," & intStep & ")" 'Execute the SQL statement to update the counter. cnn1.Execute strSQL End Sub |
The ResetCounter procedure above demonstrates how to make a SQL command dynamic by appending arguments to a SQL statement. This process is analogous to passing values to a parameter query. The ResetCounter procedure alters the FamID field in the FamilyMemberNames table and takes two arguments. The first one is the new counter field value for the next record added to the table; the second is the step size for subsequent new records. You can run the procedure by typing in the Immediate window a line similar to the following:
ResetCounter 2,2 |
These two arguments force the next record added to the table to have a FamID field value of 2; each additional record will have a FamID field value that is larger by a step value of 2. Be careful how you set the start and step counter values because you can create key violations (such as duplicate values) that deny users the right to enter new records into a table. Happily, Access 2000 lets you survey the existing counter field values programmatically. You can therefore construct code that guarantees that this problem will not occur, as shown in the next procedure.
The heart of ResetCounter is the SQL statement. As you can see, it has three critical elements. The ALTER TABLE keywords change the design of the specified table. The ALTER COLUMN keywords change the design of the specified field. The IDENTITY keyword updates the counter field with arguments that specify the start and step values for the field.
The following procedure, SetResetCounter, is long but not very complicated. It uses a method of manipulating counter field values that avoids key violations. Figure 4-26 shows the table just after the last successful run of SetResetCounter. Notice that the first record has a FamID value of 2, and the FamID value of the following record is increased by a step value of 2. The third record, with a FamID value of 8, is out of line with the preceding step value. In addition, subsequent records increase the FamID field by a step value of 4 with each new record. The change in start and step values for the Counter field results from calling ResetCounter. A text string in MyMemoField marks the change in these values.
Figure 4-26. The FamilyMemberNames table after the SetResetCounter procedure executes.
Sub SetResetCounter() Dim cnn1 As New ADODB.Connection Dim cmd1 As ADODB.Command Dim rst1 As ADODB.Recordset, rst2 As New ADODB.Recordset Dim int1 As Integer 'Reference connection. Set cnn1 = CurrentProject.Connection 'Clear FamilyMemberNames table and reset table counter. DoCmd.SetWarnings False DoCmd.OpenQuery "qdlAllFamilyMemberNames" DoCmd.SetWarnings True ResetCounter 2, 2 'Add a couple of records to the FamilyMemberNames 'table from the FamilyMembers table with the 'initial start and step values. Set rst1 = New ADODB.Recordset rst1.Open "FamilyMemberNames", cnn1, adOpenKeyset, _ adLockOptimistic, adCmdTable rst2.Open "FamilyMembers", cnn1, adOpenForwardOnly, _ adLockReadOnly, adCmdTable For int1 = 1 To 2 rst1.AddNew rst1(1) = rst2(1) & " " & rst2(2) If int1 = 1 Then rst1("MyMemoField") = "start, step = 2" End If rst2.MoveNext rst1.Update Next int1 rst1.Close 'Define and execute command to select all FamID field values 'with highest FamID value first --> last counter value. Set cmd1 = New ADODB.Command With cmd1 .ActiveConnection = cnn1 .CommandText = "Select FamID from FamilyMemberNames " & _ "Order By FamID Desc" .CommandType = adCmdText .Execute End With 'Save last counter value, and use it to 'reset start and step to new values. Set rst1 = New ADODB.Recordset rst1.CursorType = adOpenForwardOnly rst1.LockType = adLockReadOnly rst1.Open cmd1 int1 = rst1(0) rst1.Close ResetCounter int1 + int1, int1 'Add remaining records from the FamilyMembers table 'to the FamilyMemberNames table with the 'new start and step values. rst1.Open "FamilyMemberNames", cnn1, adOpenKeyset, _ adLockOptimistic, adCmdTable ' rst1(2) = "see my new start & step" int1 = 3 Do Until rst2.EOF rst1.AddNew rst1(1) = rst2(1) & " " & rst2(2) If int1 = 3 Then rst1("MyMemoField") = _ "see my new start & step" End If rst2.MoveNext int1 = int1 + 1 rst1.Update Loop End Sub |
SetResetCounter has six parts separated by comments. The first part establishes a connection to the current database. The second part runs a delete query that removes all the rows from the FamilyMemberNames table, enables system warnings by calling the SetWarnings method, and then resets the start and step values of the counter for the FamilyMemberNames table by calling the ResetCounter procedure.
The third part uses a For...Next loop to copy the first two records of the FamilyMembers table to the FamilyMemberNames table. The Jet database engine automatically assigns FamID values for these two records using the start and step values set by the preceding part.
The fourth part uses a Command object on the FamilyMemberNames table to find the largest value of the FamID field. When the command executes, it sorts the table's records in descending FamID order, which places the largest FamID field—the table's primary key—at the top of the return set. Knowing this value lets your application set a new start value for additional records that does not duplicate the primary key value of any other records in the table.
The fifth part opens a recordset on the command's return set and stores the value of the FamID field of the recordset's first record. This part then invokes the ResetCounter procedure to set a new start value that is twice as large as the highest FamID value and a new step size equal to the highest FamID field value.
The final part adds the remaining records of the FamilyMembers table to the FamilyMemberNames table. To reinforce the flexible control over counter primary key fields, the Memo column contains a message indicating how the SetRetCounter procedure freely manipulates the counter field.
Views and Stored Procedures
Views and stored procedures, previously available only in high-end database managers, are available in Access 2000. Access 2000 implements views, which do not support parameters, as stored queries that return rows. Access 2000 offers stored procedures as both saved action queries and parameter queries that work as either action or select queries. As you'll see, you can achieve the functionality of a parameter query by passing string constants to a procedure that merges the passed values into a SQL statement and then executes the statement.
Views
The SQL CREATE VIEW statement creates a view by adding a stored select query to the Database window under the Queries object category. (In an Access .adp project, your views are saved under the Views object category of the Database window.) The process for creating a stored select query with CREATE VIEW is more direct than with ADO, which requires that you first construct a Command object and then append it to the Views collection.
The following procedure demonstrates the syntax and operation of SQL CREATE VIEW statements in ADO code.
Sub CreateView() Dim cnn1 As ADODB.Connection 'Reference connection. Set cnn1 = CurrentProject.Connection 'Execute SQL for view. cnn1.Execute "Create View CategoryView as " & _ "SELECT * From Categories" RefreshDatabaseWindow 'This simple routine fails if CategoryView already exists. End Sub |
The procedure invokes the Execute method of the connection and passes it a SQL CREATE VIEW statement. The name of the select query immediately follows the CREATE VIEW keywords, and is followed by the AS keyword and the SQL SELECT statement. The SELECT statement can be any standard SELECT statement. Finally, the procedure calls RefreshDatabaseWindow to update the Database window so that a user can view the new query without having to manually refresh the window.
One deficiency of CreateView is that it fails if the view it attempts to create already exists. You can, of course, manually remove the old query. However, your code can also automatically delete the old query. The following two procedures illustrate one approach to this problem:
Sub CreateReplaceView(ViewName As String) Dim cnn1 As ADODB.Connection Dim cat1 As ADOX.Catalog Dim vew1 As ADOX.View 'Reference objects for view Set cnn1 = CurrentProject.Connection Set cat1 = New Catalog cat1.ActiveConnection = cnn1 'Enumerate views. For Each vew1 In cat1.Views 'Delete named view and replace it with a new version. If vew1.Name = ViewName Then cat1.Views.Delete ViewName CreateCustomView ViewName Exit Sub End If Next vew1 'If the view is not there, create from scratch. CreateCustomView ViewName RefreshDatabaseWindow End Sub Sub CreateCustomView(MyViewName) Dim cnn1 As ADODB.Connection, strSQL 'Reference connection and execute SQL for view. Set cnn1 = CurrentProject.Connection 'Create SQL string for view. strSQL = "Create View " & MyViewName & _ " as Select * From Categories" 'Create the custom view. cnn1.Execute strSQL RefreshDatabaseWindow End Sub |
The CreateReplaceView procedure does not directly execute the CREATE VIEW statement; it leaves that task to the second procedure, CreateCustomView. CreateReplaceView enumerates the views to assess whether it is necessary to delete the old view before attempting to create a new one with the same name. Then it calls CreateCustomView and passes the name of the view to create. CreateCustomView is identical in design to the previous sample, except it uses string concatenation with the passed argument and a string constant to devise a SQL statement for the connection's Execute method.
You create a new view by typing in the Immediate window a simple one-line command similar to the following:
CreateReplaceView "NameOfView" |
You must enclose the name of the view in quotes. You can easily revise CreateCustomView to allow a dynamic SELECT statement.
Instead of iterating through the elements in the Views collection, you can simply attempt to create a new view and trap the error that occurs when a view with that name already exists. The error number for a duplicate view is 2147467259. Trap for this number and delete the old view before reexecuting the CREATE VIEW statement. The FieldedView procedure on this book's companion CD illustrates this approach.
Stored procedures
A stored procedure simplifies the reuse of SQL code. Stored procedures perform bulk operations, such as delete, update, or append queries, and they accept run-time parameters for both bulk operations and row-returning queries.
You use the SQL CREATE PROC statement to create a stored procedure. This statement has the same general syntax as the CREATE VIEW statement. You follow CREATE PROC with the name of your stored procedure and the AS keyword, which serves a marker for the beginning of the SQL code that defines the behavior of the stored procedure. You can design your action queries in the Access query Design view, copy the SQL code in SQL view, and then paste it in after the AS keyword.
Stored procedures do not appear in the Database window. However, they exist as items in the Procedures collection of the Catalog object. Your code can reference individual procedures through this hierarchical object schema. For example, if your application generates a stored procedure that deletes records, it exists in the Procedures collection (along with the action queries in a project).
The following procedure illustrates the syntax for applying the CREATE PROC statement. It invokes the Connection object's Execute method and passes a SQL statement as a string. The CREATE PROC statement attempts to create a query named DeleteThese. The query deletes all records in the FamilyMemberNames table with a FamID value of 10 or greater. Without any special measure, the procedure fails if DeleteThese already exists. To make it possible to run the procedure, even when it exists already, the code resumes from the next line in the event of an error.
Sub CreateProcToDelete() On Error Resume Next Dim cnn1 As ADODB.Connection Dim cat1 As Catalog Dim proc1 As Procedure Dim cmd1 As Command 'Reference connection. Set cnn1 = CurrentProject.Connection Set cat1 = New Catalog cat1.ActiveConnection = cnn1 Set cmd1 = New Command cmd1.ActiveConnection = cnn1 'Execute SQL to make the procedure. cnn1.Execute "Create Procedure DeleteThese As " & _ "Delete From FamilyMemberNames Where FamID>=10" 'Enumerate Procedures collection members. 'For DeleteThese procedure, set command properties 'and execute it. For Each proc1 In cat1.Procedures If proc1.Name = "DeleteThese" Then cmd1.CommandText = proc1.Name cmd1.CommandType = adCmdStoredProc cmd1.Execute End If Next proc1 End Sub |
The CreateProcToDelete procedure commences with a series of declarations and assignments to support the logic for creating and then running the procedure. The Connection object's Execute method makes the procedure by running a SQL statement. At this point an error will occur if the procedure already exists. The Execute method actually adds the DeleteThese procedure to the Procedures collection in the connection's catalog.
Because procedures reside in a collection, you can enumerate the collection's members with a For...Each loop. The sample uses such a loop to locate DeleteThese. When it finds the procedure, it assigns the procedure's Name property to the CommandText property of a Command object. Then, it sets the Command object's CommandType property to adCmdStoredProc. These two assignments generate the SQL string Execute DeleteThese. Invoking the Execute method for the Command object launches the procedure in Jet and removes the records from the FamilyMemberNames table.
You can make the preceding sample more flexible and smarter in a couple of ways. First, a more elaborate error trap can specifically isolate an error resulting from the procedure from other types of errors. Second, you can make the procedure accept parameters for the FamID field. The following sample uses the CREATE PROC statement to define a parameter query that deletes all records in the FamilyMemberNames table with a FamID value greater than or equal to a specific value designated at run time. The code reminds you of the steps for setting a command parameter value. Recall that you have to create the parameter, append it to the Parameters collection, and then assign a value to it. The sample also traps for the error that results when you attempt to create a procedure that has the same name as an existing procedure.
Sub CreateProcToDelete2() On Error GoTo Delete2Err Dim cnn1 As ADODB.Connection Dim cmd1 As ADODB.Command Dim prm1 As Parameter 'Reference connection. Set cnn1 = CurrentProject.Connection 'Test for unanticipated errors. ' Err.Raise 1 'Execute SQL to make the procedure. cnn1.Execute "Create Proc DeleteThese " & _ "(Parameter1 Long) As " & _ "Delete From FamilyMemberNames " & _ "Where FamID>=Parameter1" 'Assign SQL from procedure to command. Set cmd1 = New ADODB.Command With cmd1 .ActiveConnection = cnn1 .CommandText = "DeleteThese" .CommandType = adCmdStoredProc End With 'Set the procedure's parameter. Set prm1 = cmd1.CreateParameter("Parameter1", _ adInteger) cmd1.Parameters.Append prm1 prm1.Value = 10 'Invoke the procedure's SQL statement in the command. cmd1.Execute Delete2Exit: Exit Sub Delete2Err: If Err.Number = -2147217900 Then 'Trap for procedure already exists. cnn1.Execute "Drop Proc DeleteThese" Resume Else MsgBox "The program generated an unanticipated " & _ "error. Its number and description are " & _ Err.Number & ": " & Err.Description, vbCritical, _ "Programming Microsoft Access 2000" End If Resume Delete2Exit End Sub |
When you specify a parameter in a CREATE PROC statement, include the parameter designation after the procedure name but before the AS keyword. You can declare its name and data type in parentheses, as shown above. You refer to the parameter again in the WHERE clause to restrict the behavior of the procedure. This syntax is the same as for parameter queries that you create in Design view.
Notice that you do not actually have to loop through the Procedures collection to invoke a specific procedure. You can reference its name so long as you specify adCmdStoredProc as the value for the command's CommandType property. The sample assigns the constant 10 to the parameter (Parameter1) for consistency with the prior sample, but you could use an InputBox function statement or a form to collect a value for the parameter.
This sample also includes basic error trapping for the situation in which a procedure of the same name already exists (error number -2147217900). CreateProcToDelete2 simply drops the old procedure when the error occurs by invoking the SQL DROP PROC statement. The syntax of the DROP PROC statement is "Drop Proc ProcedureName."
EAN: 2147483647
Pages: 97