Programming Microsoft Office Access 2003 (Core Reference) [Electronic resources] نسخه متنی

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.

SubCreateUpdate_a_shipperProcedure() 
Dimstr1AsString
Dimcnn1AsADODB.Connection
DimProcedureNameAsString
'PointaconnectionobjectattheChapter11SQL
'databaseontheCabSony1serverwithatrustedconnection
Setcnn1=NewADODB.Connection
cnn1.Provider= "sqloledb"
str1= "Server=CabSony1;Database=Chapter11SQL;" &_
"Trusted_Connection=yes"
cnn1.Openstr1
'Deletethetableifitexistsalready
ProcedureName= "Update_a_shipper"
Drop_a_procedurecnn1,ProcedureName
'Createtheprocedure
str1= "CREATEPROCEDURE " &ProcedureName& " " &vbLf&_
" " &vbLf&_
"@id_for_updateint, " &vbLf&_
"@new_namevarchar(40) " &vbLf&_
" " &vbLf&_
"AS " &vbLf&_
"UPDATEShippers " &vbLf&_
"SETCompanyName=@new_name " &vbLf&_
"WHEREShipperID=@id_for_update "
cnn1.Executestr1
'RefreshDatabasewindowtoshownewprocedure
RefreshDatabaseWindow
EndSub

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.

SubUpdateAShipper()
Dimcmd1AsADODB.Command
Dimprm1AsADODB.Parameter
Dimprm2AsADODB.Parameter
'PointaConnectionobjectatthestoredprocedure
Setcmd1=NewADODB.Command
cmd1.ActiveConnection=CurrentProject.Connection
cmd1.CommandType=adCmdStoredProc
cmd1.CommandText= "Update_a_shipper"
'Createandappendparameters
Setprm1=cmd1.CreateParameter("@id_for_update",_
adInteger,adParamInput)
prm1.Value=4
cmd1.Parameters.Appendprm1
Setprm2=cmd1.CreateParameter("@new_name",adVarChar,_
adParamInput,40)
prm2.Value= "CABShippingCo."
cmd1.Parameters.Appendprm2
'Invokeastoredprocedurebyexecutingacommand
cmd1.Execute
EndSub

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.

SubCreateDelete_a_shipperProcedure() 
Dimstr1AsString
Dimcnn1AsADODB.Connection
DimProcedureNameAsString
'PointaConnectionobjectatthecurrentproject
Setcnn1=CurrentProject.Connection
'Deletetheprocedureifitexistsalready
ProcedureName= "Delete_a_shipper"
Drop_a_procedurecnn1,ProcedureName
'Createtheprocedure
str1= "CREATEPROCEDURE " &ProcedureName& " " &vbLf&_
" " &vbLf&_
"@id_to_deleteint " &vbLf&_
" " &vbLf&_
"AS " &vbLf&_
"DELETEFROMShippers " &vbLf&_
"WHEREShipperID=@id_to_delete "
cnn1.Executestr1
'RefreshDatabasewindowtoshownewprocedure
RefreshDatabaseWindow
EndSub
SubDeleteAShipper() 
Dimcmd1AsADODB.Command
Dimprm1AsADODB.Parameter
Dimprm2AsADODB.Parameter
'PointaConnectionobjectatthestoredprocedure
Setcmd1=NewADODB.Command
cmd1.ActiveConnection=CurrentProject.Connection
cmd1.CommandType=adCmdStoredProc
cmd1.CommandText= "Delete_a_shipper"
'Createandappendparameter
Setprm1=cmd1.CreateParameter("@id_to_delete",_
adInteger,adParamInput)
prm1.Value=4
cmd1.Parameters.Appendprm1
'Invokeastoredprocedurebyexecutingacommand
cmd1.Execute
EndSub