Prepare Property Example

This example uses the Prepare property to specify that a query should be executed directly rather than first creating a temporary stored procedure on the server.

Sub PrepareX()

   Dim wrkODBC As Workspace
   Dim conPubs As Connection
   Dim qdfTemp As QueryDef
   Dim rstTemp As Recordset

   ' Create ODBCDirect Workspace object and open Connection
   ' object.
   Set wrkODBC = CreateWorkspace("", _
      "admin", "", dbUseODBC)
   Set conPubs = wrkODBC.OpenConnection("Publishers", , , _
      "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

   Set qdfTemp = conPubs.CreateQueryDef("")

   With qdfTemp
      ' Because you will only run this query once, specify
      ' the ODBC SQLExecDirect API function. If you do
      ' not set this property before you set the SQL
      ' property, the ODBC SQLPrepare API function will
      ' be called anyway which will nullify any
      ' performance gain.
      .Prepare = dbQUnprepare
      .SQL = "UPDATE roysched " & _
         "SET royalty = royalty * 2 " & _
         "WHERE title_id LIKE 'BU____' OR " & _
         "title_id LIKE 'PC____'"
      .Execute
   End With

   Debug.Print "Query results:"

   ' Open recordset containing modified records.
   Set rstTemp = conPubs.OpenRecordset( _
      "SELECT * FROM roysched " & _
      "WHERE title_id LIKE 'BU____' OR " & _
      "title_id LIKE 'PC____'")

   ' Enumerate recordset.
   With rstTemp
      Do While Not .EOF
         Debug.Print , !title_id, !lorange, _
            !hirange, !royalty
         .MoveNext
      Loop
      .Close
   End With

   conPubs.Close
   wrkODBC.Close

End Sub