Whenever possible, I like to use stored procedures in macros when the process involves some sort of database update or manipulation. This way it limits access to the code and reduces the chance of someone “accidentally” changing it within the macro dialog window. Yes – they can still throw a wrench into the macros script – but at least I can contain it.
Calling a stored procedure is relatively easy to do.
Here’s a walk through…
Keeping it simple, the following script will create a stored procedure (named SP_UpdateUDF) to update the USER_10 field for a part in the PART table. The stored procedure has ONE parameter which is the part id to update. The script also grants access to PUBLIC to give everyone permission to execute the store procedure.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE proc dbo.SP_UpdateUDF @I_PartID VARCHAR(30) AS SET NOCOUNT ON --For synchronization machine id placement UPDATE PART SET USER_10='COMPLETE' WHERE ID = @I_PartID SET NOCOUNT OFF GO GRANT EXECUTE ON dbo.SP_UpdateUDF TO PUBLIC GO |
Now here’s the VISUAL macro to call the stored procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Const adParamInput = &H0001 Const adVarChar = 200 Dim strServerName Dim strDatabase Dim strUserName Dim strPassword strServerName="E N T E R N A M E O F D A T A B A S E S E R V E R" strDatabase="E N T E R N A M E O F D A T A B A S E" strUserName="E N T E R U S E R N A M E" strPassword="E N T E R P A S S W O R D" strcnn="Driver={SQL Server};Server=" & strServerName & ";Database=" & strDatabase & ";Uid=" & strUsername & ";Pwd=" & strPassword & ";" Set Conn = CreateObject("ADODB.Connection") conn.open strcnn Set Cmd = CreateObject("ADODB.Command") Cmd.ActiveConnection = conn ' Set up the parameter for our Stored Procedure *** watch the line wrap *** Cmd.Parameters.Append Cmd.CreateParameter("I_PartID", adVarChar, adParamInput , 30, "E N T E R Y O U R P A R T I D") Cmd.CommandText = "SP_UpdateUDF" Cmd.Execute |
