[AccessD] Stored procedures as Queries

John,

  You can use a temp querydef:

Dim qdf As QueryDef
Dim sParam As string

            
sParam = ""

For i = 0 To rs.Fields.count - 2
   sParam = "?," & sParam
Next i

sParam = sParam & "?"
            
sql = "{call qrySoTransHeaderInsert(" & sParam & ")}"
Set qdf = gConSqlComp.CreateQueryDef("", sql)
            
For i = 0 To rs.Fields.count - 1
   qdf.Parameters(i) = rs.Fields(i).Value
Next
qdf.Execute

qdf.Close

  This snippet of code is used to move a record between a local JET table
and a SQL one in the backend.  The sproc is doing the actual insert into the
BE table.

  Here is part of the corresponding fetch:

sql = "{call qrySoTransGet('" & Me!cboTransId & "')}"
Set qdf = gConSqlComp.CreateQueryDef("", sql)
qdf.ODBCTimeout = 0 ' was 10
Set rs = qdf.OpenRecordset(dbOpenForwardOnly)
If rs.EOF Then GoTo No_Data
If (TID = Exchange_All) Then
   sql = "Delete From tblSoTransHeader"
   gDbSo.Execute sql
   Set rsD = gDbSo.OpenRecordset("tblSoTransHeader", dbOpenDynaset,
dbAppendOnly)
   rsD.AddNew
   ts = rs!ts
            
   For Each fld In rsD.Fields
      fld.Value = rs(fld.Name)
   Next
   rsD.Update
   rsD.Close
End If
rs.NextRecordset
If (TID = Exchange_All Or TID = Exchange_Dtl) And Not rs.EOF Then
   sql = "Delete From tblSoTransDetail"


  qrySOTransGet is a sproc that returns multiple recordsets, one for each
table involved in with the order system.

Last, here's a handy routine when your debugging calling sproc's:

Sub GetSPParameters(strSPName As String)

          ' Return the attributes of the parameters of a stored procedure.
          ' From the debug window:
          ' Call GetSPParameters("qrySMGetPeriod")
          '
          Dim strConnect As String
          Dim cnn As ADODB.Connection
          Dim cmd As ADODB.Command
          Dim prm As ADODB.Parameter

10        Set cnn = New ADODB.Connection
20        cnn.Open "DSN=SYS"

30        Set cmd = New ADODB.Command
40        cmd.ActiveConnection = cnn
50        cmd.CommandText = strSPName
60        cmd.CommandType = adCmdStoredProc

70        cmd.Parameters.Refresh
80        For i = 0 To cmd.Parameters.Count - 1
90            Debug.Print "Parameter: " & i
100           Debug.Print "     Name: " & cmd.Parameters(i).Name
110           Debug.Print "     Type: " & cmd.Parameters(i).Type
120           Debug.Print "Direction: " & cmd.Parameters(i).Direction
130           Debug.Print "     Size: " & cmd.Parameters(i).size
140           Debug.Print "   Attrib: " & cmd.Parameters(i).Attributes
150           Debug.Print "    Value: " & cmd.Parameters(i).Value
160           Debug.Print ""
170       Next i

180       cnn.Close
190       Set cnn = Nothing

End Sub

   It's in ADO, but it's a great way to determine all the params and their
attributes.

Jim.
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, August 05, 2011 11:58 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Stored procedures as Queries

I am using pass through queries where I reference a stored procedure and
pass a param through.  At 
the moment I am having to open the querydef and replace the parameter with
some value, save the sql 
of the querydef and then save the querydef.  Is there any other way to do
this?  It seems crude to 
the max.

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com