[AccessD] Stored procedures as Queries

Thanks Jim.  I am looking now at forms bound to ADO recordsets.  I will figure out the other uses 
for the ADO recordset.  Every time I go back to this the process gets easier.

John W. Colby
www.ColbyConsulting.com

On 8/6/2011 8:11 AM, Jim Dettman wrote:
> 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.
>