03-07-03, 10:39 #1Registered User
- Join Date
- Dec 2002
- Washington D.C.
Unanswered: Two Solutions: Which is betterRunning a Stored Procedure from ADO
From one of my forms in an Access Project I want to run a Stored Procedure on the "After Insert" event of the form. The stored procedure is an Insert SQL Statement with two variables.
What is the best way to pass the variables to the stored procedure and run it.
Should I run a SQL Statement like
srtSQL="EXEC SP_Insert (@Var1=Var1,@Var2=Var2)
Or there is a better way to do it with ADO objects?
I found two solutions. Which one do you think is the better one?
The stored procedure on the Server:
CREATE PROCEDURE SP_Insert_Into_CompanyAdrsContact
Insert into tblCompanyAdrsContact (CompAdrsID,ContactID) Values (@CompAdrsID,@ContactID)
On the Access form:
1) Using a SQL stament dynamically to pass parameters from a form:
strSQL = "Exec SP_Insert_Into_CompanyAdrsContact " & Me!CompAdrsID
strSQL = strSQL & "," & Me!ContactID
2) Using ADO objects:
Dim cmd As ADODB.Command
Dim prmContactID As ADODB.Parameter
Dim prmCompAdrsID As ADODB.Parameter
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "SP_Insert_Into_CompanyAdrsContact"
Set prmCompAdrsID = cmd.CreateParameter("@CompAdrsID", adInteger, adParamInput)
Set prmContactID = cmd.CreateParameter("@ContactID", adInteger, adParamInput)
prmCompAdrsID.Value = Me![CompAdrsID]
prmContactID.Value = Me![ContactID]
Last edited by Sia; 03-07-03 at 16:33.
03-07-03, 20:07 #2Registered User
- Join Date
- May 2002
Re: Two Solutions: Which is betterRunning a Stored Procedure from ADO
If you are going to call this proc many times, it's best to use command parameter. This will allow you to gain some performance because of cache. If it's just a one time thing, just execute the string is fine.--