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 



Programming Microsoft Office Access 2003
Programming MicrosoftВ® Office Access 2003 (Core Reference) (Pro-Developer)
ISBN: 0735619425
EAN: 2147483647
Year: 2006
Pages: 144
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net