How to execute SQL Server stored procedure...

[Guest_imported]

Hi everyone,
I have a problem and I hope that somebody could help me.
I need to execute SQL Server stored procedure from Access using QueryDef object of DAO. This is an assignment and I really cannot do anything about this, like use ADO for example. I seem keep stuggling with syntax and options assignments. So if anyone could post a small piece of working code, I'd really appreciate this. Let's say, using good old Pubs database.
Thank you in advance.

 

[jfischer]

You can run the following sample code from the debug window to try out the various types of stored procedures you want to access.

Sub SQLSample(sSQLServer As String, _
              bTrustedYn As Boolean, _
              Optional sSQLLogin As String, _
              Optional sSQLPassword As String)
  Dim lwsSQL  As Workspace
  Dim lConSQL As Connection
  Dim dbs     As Database
  Dim qdf     As QueryDef
  Dim rst     As Recordset
  Dim sql     As String
  Dim sConnect As String

  On Error GoTo SQLSample_Err
  'Set up ODBC connection to Pubs Db
  Set lwsSQL = DBEngine.CreateWorkspace("", sSQLLogin, _
      sSQLPassword, dbUseODBC)
  sConnect = "ODBC;DRIVER={SQL Server};SERVER="
  sConnect = sConnect & sSQLServer & ";DATABASE=Pubs;"
  If bTrustedYn Then
    sConnect = sConnect & "Trusted_Connection=Yes;"
  Else
    sConnect = sConnect & "UID=" & sSQLLogin _
             & ";PWD=" & sSQLPassword & ";"
  End If
  Set lConSQL = lwsSQL.OpenConnection("", _
      dbDriverCompleteRequired, False, sConnect)
  Set dbs = lConSQL.Database
  'Set up QueryDef to call a stored procedure
  '** pubsSp_CurrentUser is a custom procedure that returns
  '   the current user ID and workstation ID in parameters.
  '** Change the name and parameters here to match your own
  '   stored procedure that you want to test.
  sql = "{call pubsSp_CurrentUser (?,?)}"
  Set qdf = lConSQL.CreateQueryDef("", sql)
  With qdf
    .Parameters("@UserID").Direction = dbParamOutput
    .Parameters("@WrkStnID").Direction = dbParamOutput
    .Execute
    Debug.Print Trim(.Parameters("@UserID"))
    Debug.Print Trim(.Parameters("@WrkStnID"))
    .Close
  End With
  'Set up RecordSet to access stored procedure result set
  '** reptq4 is modified from reptq3 to omit COMPUTE
  sql = "{call reptq4 (0.00, 50.00, 'business')}"
  Set rst = dbs.OpenRecordset("", sql)
  Debug.Print "RecordCount = " & rst.RecordCount
  While Not rst.EOF
    With rst
      Debug.Print !pub_id & "," & !type & "," _
                & !title_id & "," & !price
      .MoveNext
    End With
  Wend
  rst.Close

SQLSample_Exit:
  On Error Resume Next
  Set dbs = Nothing
  Set lConSQL = Nothing
  Set lwsSQL = Nothing
  Exit Sub

SQLSample_Err:
  MsgBox "Error #" & Err.Number & vbCrLf & vbCrLf _
       & Err.Description, vbOKOny, "SQLSample"
  Resume SQLSample_Exit
End Sub