Updating and Deleting Records from Parameter Values

The following VBA procedure illustrates one approach to creating a stored procedure that revises a shipper's name in the Shippers table. To perform the revision, the update needs two pieces of information. The Update_a_shipper stored procedure represents these two pieces of information with parameters. One parameter is the ShipperID value for the shipper that will receive the new value. The second parameter represents the new company name for the shipper associated with the ShipperID value. The VBA procedure shows the syntax using these two parameters with the UPDATE statement in T-SQL within a stored procedure.

The Create_Update_a_shipperProcedure procedure that appears next shows the correct syntax for using a trusted connection to connect to a server. The Server and Database terms in the str1 string should reflect the name of the server and the database in which you want to create the stored procedure. When running this sample on your computer, you will probably need to revise the server name. If you adapt this sample for your custom projects, you'll also need to update the database name. This general approach is very flexible because it works with any server and database for which the current user has permission to create new database objects.

Sub CreateUpdate_a_shipperProcedure() 
Dim str1 As String
Dim cnn1 As ADODB.Connection
Dim ProcedureName As String
   
'Point a connection object at the Chapter11SQL
'database on the CabSony1 server with a trusted connection
Set cnn1 = New ADODB.Connection
cnn1.Provider = "sqloledb"
str1 = "Server=CabSony1;Database=Chapter11SQL;" & _
    "Trusted_Connection=yes"
cnn1.Open str1
   
'Delete the table if it exists already
ProcedureName = "Update_a_shipper"
Drop_a_procedure cnn1, ProcedureName
   
'Create the procedure
str1 = "CREATE PROCEDURE " & ProcedureName & " " & vbLf & _
    " " & vbLf & _
    "@id_for_update int, " & vbLf & _
    "@new_name varchar(40) " & vbLf & _
    " " & vbLf & _
    "AS " & vbLf & _
    "UPDATE Shippers " & vbLf & _
    "SET CompanyName = @new_name " & vbLf & _
    "WHERE ShipperID = @id_for_update "
cnn1.Execute str1
   
'Refresh Database window to show new procedure
RefreshDatabaseWindow
   
End Sub

The next sample applies the Update_a_shipper stored procedure created by the preceding VBA sample. The UpdateAShipper VBA procedure reverts to referring to the current project's connection. This syntax for specifying a connection is less general than the preceding one, which used a trusted connection. However, designating the current project's connection is more straightforward. The sample procedure revises the name of the shipper that has a shipper ID of 4. If you ran the InsertANewShipper VBA procedure from the preceding sample, the Shippers table likely has a record with a ShipperID value of 4. If not, update the prm1.Value assignment so that it points to a row that you want to revise in the Shippers table.

Sub UpdateAShipper()
Dim cmd1 As ADODB.Command
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
   
'Point a Connection object at the stored procedure
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "Update_a_shipper"
   
'Create and append parameters
Set prm1 = cmd1.CreateParameter("@id_for_update", _
    adInteger, adParamInput)
prm1.Value = 4
cmd1.Parameters.Append prm1
   
Set prm2 = cmd1.CreateParameter("@new_name", adVarChar, _
    adParamInput, 40)
prm2.Value = "CAB Shipping Co."
cmd1.Parameters.Append prm2
   
'Invoke a stored procedure by executing a command
cmd1.Execute
   
End Sub

The next pair of procedures illustrates the VBA syntax for creating a stored procedure that deletes a record from the Shippers table and then invoking that stored procedure. This stored procedure follows the same basic design as the samples for inserting and updating records with parameters. In this case, the procedure for creating the Delete_a_shipper stored procedure reveals the T-SQL syntax to remove a single record from a table based on its ShipperID column value. The @id_to_delete parameter points to this column value.

Sub CreateDelete_a_shipperProcedure() 
Dim str1 As String
Dim cnn1 As ADODB.Connection
Dim ProcedureName As String
   
'Point a Connection object at the current project
Set cnn1 = CurrentProject.Connection
   
'Delete the procedure if it exists already
ProcedureName = "Delete_a_shipper"
Drop_a_procedure cnn1, ProcedureName
   
'Create the procedure
str1 = "CREATE PROCEDURE " & ProcedureName & " " & vbLf & _
    " " & vbLf & _
    "@id_to_delete int " & vbLf & _
    " " & vbLf & _
    "AS " & vbLf & _
    "DELETE FROM Shippers " & vbLf & _
    "WHERE ShipperID = @id_to_delete "
cnn1.Execute str1
   
'Refresh Database window to show new procedure
RefreshDatabaseWindow
   
End Sub
   
Sub DeleteAShipper() 
Dim cmd1 As ADODB.Command
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
   
'Point a Connection object at the stored procedure
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "Delete_a_shipper"
   
'Create and append parameter
Set prm1 = cmd1.CreateParameter("@id_to_delete", _
    adInteger, adParamInput)
prm1.Value = 4
cmd1.Parameters.Append prm1
   
'Invoke a stored procedure by executing a command
cmd1.Execute
   
End Sub