I am using MSDE which is acctualy SQL Server 7 with number of user and filesize restrictions.  I am
atempting to call a stored procedure from Access 2000.  I have a very conveluted method involving using ADODB (see below) but was
wondering if there was a easier way (all the other dev enviroments I have used can do this with one line of code).

Regards
Ben

 Dim cnn As ADODB.Connection
 Dim cmd As ADODB.Command
 Dim param As ADODB.Parameter
 
 Set cnn = CurrentProject.Connection
 
 Set cmd = New ADODB.Command
 
 cmd.ActiveConnection = cnn
 cmd.CommandText = "post"
 cmd.CommandType = adCmdStoredProc
 
 Set param = cmd.CreateParameter("posting_type", adVarChar, adParamInput, 15, Me.posting_type)
 cmd.Parameters.Append param
 Set param = cmd.CreateParameter("posting_date", adVarChar, adParamInput, 15, Me.posting_date)
 cmd.Parameters.Append param
 Set param = cmd.CreateParameter("account_cr", adVarChar, adParamInput, 15, Me.account_cr)
 cmd.Parameters.Append param
 Set param = cmd.CreateParameter("account_de", adVarChar, adParamInput, 15, Me.account_de)
 cmd.Parameters.Append param
 Set param = cmd.CreateParameter("amount_cr", adCurrency, adParamInput, 15, Me.amount)
 cmd.Parameters.Append param
 Set param = cmd.CreateParameter("amount_de", adCurrency, adParamInput, 15, Me.amount)
 cmd.Parameters.Append param
 Set param = cmd.CreateParameter("notes", adVarChar, adParamInput, 15, Me.Notes)
 cmd.Parameters.Append param
 
 cmd.Execute
 
 Set param = Nothing
 Set cmd = Nothing
 Set cmm = Nothing
  
 'str_sql = "INSERT INTO postings ( posting_type, account_num, amount_cr, de_posting_id) VALUES ( '" & Me.posting_type & "',
'" & str_account_num_cr & "', '" & str_anount_cr & "', 0)"
 'MsgBox str_sql
  
 'Set rst = CurrentProject.Connection.Execute(sqr_sql)
 'rst.Close
 'Set rst = Nothing
  
 'str_sql = "INSERT INTO postings ( posting_type, account_num, amount_de, de_posting_id) VALUES ( '" & Me.posting_type & "',
'" & str_account_num_de & "', '" & str_amount_de & "', 0)"
 'MsgBox str_sql

' Set rst = CurrentProject.Connection.Execute(str_sql)
' rst.Close
' Set rst = Nothing
  
End Sub

I use SQL 7, using the style you have commented out... basically

strSQL = "exec sp_post '" & strVar1 & "', " & numVar2 ... etc
set adoRset = adoConn.execute strSQL

but my sql stmt is a call to a stored procedure... the stored procedure may
return records, if not then just use

strSQL = "exec sp_post '" & strVar1 & "', " & numVar2 ... etc
adoConn.execute strSQL, adoExecuteNoRecords

(better check my spelling on the ado constant...)

BUT this method will not support output parameters (I just return values as
part of the recordset if I need to)



-----Original Message-----
From: Ben Ewards [mailto:ben@c...]
Sent: Monday, May 20, 2002 7:18 AM
To: Access
Subject: [access] Calling MSDE (SQL Server 7) stored prodedures with
Access 2000


I am using MSDE which is acctualy SQL Server 7 with number of user and
filesize restrictions.  I am atempting to call a stored procedure from
Access 2000.  I have a very conveluted method involving using ADODB (see
below) but was wondering if there was a easier way (all the other dev
enviroments I have used can do this with one line of code).

Regards
Ben

 Dim cnn As ADODB.Connection
 Dim cmd As ADODB.Command
 Dim param As ADODB.Parameter
 
 Set cnn = CurrentProject.Connection
 
 Set cmd = New ADODB.Command
 
 cmd.ActiveConnection = cnn
 cmd.CommandText = "post"
 cmd.CommandType = adCmdStoredProc
 
 Set param = cmd.CreateParameter("posting_type", adVarChar, adParamInput,
15, Me.posting_type)
 cmd.Parameters.Append param
 Set param = cmd.CreateParameter("posting_date", adVarChar, adParamInput,
15, Me.posting_date)
 cmd.Parameters.Append param
 Set param = cmd.CreateParameter("account_cr", adVarChar, adParamInput, 15,
Me.account_cr)
 cmd.Parameters.Append param
 Set param = cmd.CreateParameter("account_de", adVarChar, adParamInput, 15,
Me.account_de)
 cmd.Parameters.Append param
 Set param = cmd.CreateParameter("amount_cr", adCurrency, adParamInput, 15,
Me.amount)
 cmd.Parameters.Append param
 Set param = cmd.CreateParameter("amount_de", adCurrency, adParamInput, 15,
Me.amount)
 cmd.Parameters.Append param
 Set param = cmd.CreateParameter("notes", adVarChar, adParamInput, 15,
Me.Notes)
 cmd.Parameters.Append param
 
 cmd.Execute
 
 Set param = Nothing
 Set cmd = Nothing
 Set cmm = Nothing
  
 'str_sql = "INSERT INTO postings ( posting_type, account_num, amount_cr,
de_posting_id) VALUES ( '" & Me.posting_type & "', '" & str_account_num_cr &
"', '" & str_anount_cr & "', 0)"
 'MsgBox str_sql
  
 'Set rst = CurrentProject.Connection.Execute(sqr_sql)
 'rst.Close
 'Set rst = Nothing
  
 'str_sql = "INSERT INTO postings ( posting_type, account_num, amount_de,
de_posting_id) VALUES ( '" & Me.posting_type & "', '" & str_account_num_de &
"', '" & str_amount_de & "', 0)"
 'MsgBox str_sql

' Set rst = CurrentProject.Connection.Execute(str_sql)
' rst.Close
' Set rst = Nothing
  
End Sub

If you are looking for a return value or output parameter, the way you
are headed is correct, but you can currentproject.execute sp's as a
single line of code using the execute statement.

Here are my other notes about Access 2000 and Stored Procedures.

Hope this helps.

Forms, Reports, Combo Boxes, and Recordsets can use stored procedures
as a Record/Row source.

Note about Parameters: Recordsets use the same syntax that the query
analyzer uses, but forms can't.  A form can only take the stored
procedure name for a recordsource and the named parameters are
seperately set in the forms InputParameters property.

Note about Forms: Stored procedures with Optional parameters must also
be set.  I set them to null to be consistant with the behavior of query
analyzer.

Query Analyzer (and Recordsets) would accept the following:
spKYLAB_LotGrade  @pSite_No =1, @pItem_Type_ID = 'FG', @pItem_No = NULL
or
EXEC spKYLAB_LotGrade  @pSite_No = 1, @pItem_Type_ID = 'FG'
or
spKYLAB_LotGrade 1,'FG'

Forms would require the following:
ME.RecordSource="spKYLAB_LotGrade"
ME.InputParameters="@pSite_No = 1, @pItem_Type_ID = 'FG', @pItem_No 
NULL"

Another FORM example:
ME.InputParameters = "@pItem_Type_ID nvarchar(10)=Form!txtItemType,
@pItem_No Int=Null, @pSite_No INT = 1"
ME.RecordSource = "sp_GetInfo"

Combo Boxes use the same syntax as Query Analyzer/recordsets but
additionally require "EXEC".
ME.cboSites.RowSource = "EXEC spKYLAB_LotGrade @pSite_No =1,
@pItem_Type_ID = 'FG'"

FINAL NOTE.  I've found that consistantly using named parameters
throughout the application as well as always setting the optional
parameters to NULL, that I can reuse functions for forms, combo box,
reports and recordsets to provide the corrected record/row source.

For forms I wrote another routine to break out the paramters from the
sp.  My form loads look something like...

Me.InputParameters = getFormSPnParms(Me.OpenArgs, 2)
Me.RecordSource = getFormSPnParms(Me.OpenArgs, 1)

Function getFormSPnParms(ByVal Source As String, PARAMETER As Integer)
As String
    On Error GoTo Error_Handler
    Dim I As Long, j As Long, dLen As Integer, P As Integer, Delimiter
As String
    j = 1
    If Source = "" Then GoTo Error_Handler
    If PARAMETER = 0 Then GoTo Error_Handler
    Delimiter = " "  ' Default Value
    dLen = Len(Delimiter)
    I = j
    If PARAMETER = 1 Then
        j = InStr(I, Source, Delimiter)
        If j = 0 Then 'No more delimiters
            j = Len(Source) + 1
        End If
        getFormSPnParms = "DBO." & Trim(Mid(Source, I, j - I))
    Else
        j = InStr(I, Source, Delimiter)
        If j = 0 Then 'No more delimiters
            j = Len(Source) + 1
        End If
        getFormSPnParms = Trim(Mid(Source, j, Len(Source)))
    End If
Done:
    Exit Function
Error_Handler:
    getFormSPnParms = "Error"
    If Err = 0 Then
        GoTo Done
    Else
        Resume Done
    End If
End Function

If you need the Return_Value or OUTPUT parameters of a stored
procedure, see MSDN Article: HOWTO: (Q174223) Refresh ADO Parameters
Collection for a Stored Procedure.

There are a lot of opinions on refreshing parameters, as well as usage
of cursors.  The bottom line is if you can avoid them, do so, but if
the performance is acceptable and the business requires it, so be it.

Good luck.





--- Ben Ewards <ben@c...> wrote:
> I am using MSDE which is acctualy SQL Server 7 with number of user
> and filesize restrictions.  I am atempting to call a stored procedure
> from Access 2000.  I have a very conveluted method involving using
> ADODB (see below) but was wondering if there was a easier way (all
> the other dev enviroments I have used can do this with one line of
> code).
> 
> Regards
> Ben
> 
>  Dim cnn As ADODB.Connection
>  Dim cmd As ADODB.Command
>  Dim param As ADODB.Parameter
>  
>  Set cnn = CurrentProject.Connection
>  
>  Set cmd = New ADODB.Command
>  
>  cmd.ActiveConnection = cnn
>  cmd.CommandText = "post"
>  cmd.CommandType = adCmdStoredProc
>  
>  Set param = cmd.CreateParameter("posting_type", adVarChar,
> adParamInput, 15, Me.posting_type)
>  cmd.Parameters.Append param
>  Set param = cmd.CreateParameter("posting_date", adVarChar,
> adParamInput, 15, Me.posting_date)
>  cmd.Parameters.Append param
>  Set param = cmd.CreateParameter("account_cr", adVarChar,
> adParamInput, 15, Me.account_cr)
>  cmd.Parameters.Append param
>  Set param = cmd.CreateParameter("account_de", adVarChar,
> adParamInput, 15, Me.account_de)
>  cmd.Parameters.Append param
>  Set param = cmd.CreateParameter("amount_cr", adCurrency,
> adParamInput, 15, Me.amount)
>  cmd.Parameters.Append param
>  Set param = cmd.CreateParameter("amount_de", adCurrency,
> adParamInput, 15, Me.amount)
>  cmd.Parameters.Append param
>  Set param = cmd.CreateParameter("notes", adVarChar, adParamInput,
> 15, Me.Notes)
>  cmd.Parameters.Append param
>  
>  cmd.Execute
>  
>  Set param = Nothing
>  Set cmd = Nothing
>  Set cmm = Nothing
>   
>  'str_sql = "INSERT INTO postings ( posting_type, account_num,
> amount_cr, de_posting_id) VALUES ( '" & Me.posting_type & "', '" &
> str_account_num_cr & "', '" & str_anount_cr & "', 0)"
>  'MsgBox str_sql
>   
>  'Set rst = CurrentProject.Connection.Execute(sqr_sql)
>  'rst.Close
>  'Set rst = Nothing
>   
>  'str_sql = "INSERT INTO postings ( posting_type, account_num,
> amount_de, de_posting_id) VALUES ( '" & Me.posting_type & "', '" &
> str_account_num_de & "', '" & str_amount_de & "', 0)"
>  'MsgBox str_sql
> 
> ' Set rst = CurrentProject.Connection.Execute(str_sql)
> ' rst.Close
> ' Set rst = Nothing
>   
> End Sub


__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com