Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Form parameter to MSSQL

Status
Not open for further replies.

Guest_imported

New member
Jan 1, 1970
0
Hello,
my question is: is it possible to pass form parameter from ACCESS Project(front-end) to MSSQL7 Stored Procedure, using VBA?
Thank you for any suggestion.

 
I got this from somewhere

'Not sure if this works or not

Dim adCmdStoredProc
adCmdStoredProc = 4

cmdInsert = Server.CreateObject("ADODB.Command");
cmdInsert.ActiveConnection = conn;
cmdInsert.CommandText = "spInsertActivityLog";
cmdInsert.CommandType = adCmdStoredProc;
DougP, MCP

Visit my WEB site to see how Bar-codes can help you be more productive
 
Yes you can but you'll have to pass it into a function or subroutine. What i do is pass it into the function that calls my procedure.

Public Function RunMyStoreProc(strEnd as string)
Dim adComd as Adodb.Command
dim conn as adodb.Connection
dim adParam as adodb.Parameter

set conn = New Adodb.Connection
set adComd = New adodb.Command
set adParam = New Adodb.Parameter

conn.CursorLocation = adUseClient
conn.ConnectionString = myPath
conn.Open

adComd.CommandType = adCmdStoredProc
adComd.CommandText = "MyStoredProcedureName"

Set adParam = adComd.CreateParameter("TargetDate", adVarChar, adParamInput, 12, strEnd)
adComd.Parameters.Append adParam
adComd.ActiveConnection = conn
adComd.Execute

set conn = nothing
set adComd = nothing
set adParam = nothing

End Function

When creating the parameter you need to name the variable as it is in the stored proc., data type, whether it is an input or an output variable, (if its a string)the max length of the variable in the stored proc and then the value of the parameter (in this case strEnd which was an argument in the function).

If the stored proc has more than one variable, you need to append each in the order that they declared in the stored proc, one at a time (you can ease this by copying and pasting the lines of code and change those things that need changing)

The stored proc does not need to just INsert, Update or Delete. A command object can return a recordset, this allows you to set the connectiontimeout and commandtimeout properties.

It was a long one, but it should work fine for you.

BB

:)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top