The Parameter Object and the Parameters Collection

The Parameter object in an ODBCDirect workspace is similar to the Parameter object in a Microsoft Jet workspace, with a few differences. In an ODBCDirect workspace, you can change the setting of the Type property, which is read-only in a Microsoft Jet workspace. You can also use the Direction property to indicate whether a parameter is an input parameter, an output parameter, or both, or the return value from the procedure. The following example specifies parameters for a query in an ODBCDirect workspace.

Function RunStoredProc() As Boolean
	Dim wrk As Workspace
	Dim qdf As QueryDef, rst As Recordset
	Dim cnn As Connection, strConnect As String, strSQL As String

	' Create ODBCDirect workspace.
	Set wrk = CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
	strConnect = "ODBC;DSN=Pubs;UID=sa;PWD=;DATABASE=Pubs"
	' Open connection.
	Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)

	strSQL = "CREATE PROCEDURE tamram @lolimit money AS " _
		& "SELECT pub_id, type, title_id, price " _
		& "FROM titles WHERE price >@lolimit"
	' Execute SQL statement to create stored procedure.
	cnn.Execute strSQL

	' Create new QueryDef.
	Set qdf = cnn.CreateQueryDef("RunStoredProc")
	With qdf
		' Set SQL property to call stored procedure.
		.SQL = "{ call tamram (?) }"
		' Provide value for parameter.
		.Parameters(0).Value = CCur(10)
		' Open recordset on querydef.
		Set rst = .OpenRecordset()
		' Print recordset to Debug window.
		PrintRecordset rst
	End With
End Function