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