currentproject.connction.execute "create procedure name1 (vde varchar) as select * from msysobjects"
--------------------------------
Create a Parameterized Jet Stored Procedure using DDL in access 2000
The information in this article applies to:
Microsoft access 2000
Microsoft Visual Basic for Applications
This article was previously published under Q202116
Advanced: Requires expert coding, interoperability, and multiuser skills.
IN THIS TASK
SUMMARY
Creating a Stored Procedure
SUMMARY
This article shows you how to create a stored procedure in the Visual Basic Environment and, if needed, how to add a parameter to the existing stored procedure.
NOTE: Native stored procedures are new in Microsoft access 2000. You cannot create and run stored procedures through the user interface. You must use the Visual Basic Environment with ActiveX Data Objects (ADO) or the Database Definition Language (DDL).
back to the top
Creating a Stored Procedure
The following steps show you how to create a stored procedure with a parameter and how to run it in the Visual Basic Environment:
In the sample database Northwind.mdb, create a new module called TestStoredProc.
On the Tools menu, click References. In the list of available references, click to select (check) Microsoft ActiveX Data Objects 2.1 Library.
Type the following in the new module:
Public Function CreateProc()
Dim strProc As String
strProc = "Create Procedure qryCustByCity " & _
"(prmCity varchar) as " & _
"select * from Customers where City = prmCity"
CurrentProject.Connection.Execute strProc
End Function
In the Immediate window, type the following and press ENTER:
?CreateProc
Steps 1 though 4 create a stored procedure called strProc, which queries for records whose city matches the parameter, prmCity. The remaining steps involve creating code that runs strProc and passes a parameter.
Return to the module that you created in step 1.
Type the following code:
Public Function RSFromParameterQuery(strCity As String)
Dim prm As ADODB.Parameter
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "qryCustByCity"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("prmCity", adVarChar, _
adParamInput, Len(strCity))
prm.Value = strCity
cmd.Parameters.Append prm
Set rst = New ADODB.Recordset
rst.Open cmd
Do Until rst.EOF
Debug.Print rst(0), rst(1), rst(2)
rst.MoveNext
Loop
End Function
In the Immediate window, type the following and press ENTER:
?RSFromParameterQuery("London")
In the Immediate window, you should see a listing of the customers in London.
back to the top
Last Reviewed: 10/27/2002
Keywords: kbhowto kbHOWTOmaster kbProgramming KB202116 kbAudDeveloper
--------------------------------
Create a Parameterized Jet Stored Procedure using DDL in access 2000
The information in this article applies to:
Microsoft access 2000
Microsoft Visual Basic for Applications
This article was previously published under Q202116
Advanced: Requires expert coding, interoperability, and multiuser skills.
IN THIS TASK
SUMMARY
Creating a Stored Procedure
SUMMARY
This article shows you how to create a stored procedure in the Visual Basic Environment and, if needed, how to add a parameter to the existing stored procedure.
NOTE: Native stored procedures are new in Microsoft access 2000. You cannot create and run stored procedures through the user interface. You must use the Visual Basic Environment with ActiveX Data Objects (ADO) or the Database Definition Language (DDL).
back to the top
Creating a Stored Procedure
The following steps show you how to create a stored procedure with a parameter and how to run it in the Visual Basic Environment:
In the sample database Northwind.mdb, create a new module called TestStoredProc.
On the Tools menu, click References. In the list of available references, click to select (check) Microsoft ActiveX Data Objects 2.1 Library.
Type the following in the new module:
Public Function CreateProc()
Dim strProc As String
strProc = "Create Procedure qryCustByCity " & _
"(prmCity varchar) as " & _
"select * from Customers where City = prmCity"
CurrentProject.Connection.Execute strProc
End Function
In the Immediate window, type the following and press ENTER:
?CreateProc
Steps 1 though 4 create a stored procedure called strProc, which queries for records whose city matches the parameter, prmCity. The remaining steps involve creating code that runs strProc and passes a parameter.
Return to the module that you created in step 1.
Type the following code:
Public Function RSFromParameterQuery(strCity As String)
Dim prm As ADODB.Parameter
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "qryCustByCity"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("prmCity", adVarChar, _
adParamInput, Len(strCity))
prm.Value = strCity
cmd.Parameters.Append prm
Set rst = New ADODB.Recordset
rst.Open cmd
Do Until rst.EOF
Debug.Print rst(0), rst(1), rst(2)
rst.MoveNext
Loop
End Function
In the Immediate window, type the following and press ENTER:
?RSFromParameterQuery("London")
In the Immediate window, you should see a listing of the customers in London.
back to the top
Last Reviewed: 10/27/2002
Keywords: kbhowto kbHOWTOmaster kbProgramming KB202116 kbAudDeveloper