[AccessD] Stored procedures from Access

Martin,

The parameters can be anything.  In one SP it is the name of 
the table and the quantity of records to export.  In another 
it is the name of the table to place data in, and a query 
name to use to place data into the table.

Stuff like that.

ATM I am just opening a query (in SQL Server), and typing 
EXEC MySPName, Param1, Param2, etc.  What I am trying to do 
is to build a method of doing that exact same thing from 
Access.  It would also be helpful to get back error 
messages, recordseffected etc.  I know nothing about that 
either btw.

The function provided by Charlotte is below.  My biggest 
problem with that function appears to be the connect string. 
  My Access FE is not "connected" to any specific database, 
nor in fact even any specific server, in fact is just a FE 
not connected to anything.  I need to dynamically build a 
connect string (which I am working on now) to connect to a 
specific server / database.  Then MAYBE this function will 
do what I need.

So far I am trying a connection string of:

Driver={SQL Native Client 10.0};
Server=Stonehenge;
Database=FirstAmericanTax;
Trusted_Connection=yes;

However I am having to replace some of the code (the cnn 
specific) because the currentproject.connection is not valid 
and immediately errors.

I replaced it with:

Set cnn = new ADODB.connection

cnn.Connectionstring=mTrustedConnection() - (which builds 
that connection string)

and then I try to open the connection but get an error message.

I just don't know enough to do this I am afraid.  As always 
happens I spend about three days learning what should be a 
trivial task.  If you don't know what you are doing (and I 
obviously don't) then you just spin your wheels forever 
getting nowhere.  I kind of figured that SOMEONE out here in 
AccessD would have done this before.

Charlotte's code:

'CallADOStoredProc(ByVal SPName As String, ParamArray 
Params() As Variant)

Public Function CallADOStoredProc(ByVal SPName As String, _
                                   ParamArray Params() As 
Variant)
   'Created by Charlotte Foust 9/30/2000
   'last modified 1/12/2001
   'Calls a saved query or stored procedure
   On Error GoTo Proc_err
   Dim varValue As Variant
   Dim strSQL As String
   Dim intLoop As Integer
   Dim varPrmType As Variant
   Dim lngRecords As Long

   Dim cnn As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim errCurr As ADODB.Error
   Dim colErrs As ADODB.Errors

   Const ERR_OPER_ON_INVALID_CONNECTION = 3709
   Const ERR_RECORD_IS_DELETED = -2147467259

   'this simplifies accessing the errors
   'collection, which belongs to the
   'connection
   Set cnn = CurrentProject.Connection
   Set colErrs = cnn.Errors

   Set cmd = New ADODB.Command
   With cmd
     .ActiveConnection = cnn
     'this could also be written as
     'colErrs.Clear
     .ActiveConnection.Errors.Clear
     .CommandType = adCmdStoredProc
     .CommandText = SPName

     For intLoop = LBound(Params) To UBound(Params)
       Select Case VarType(Params(intLoop))
         Case vbString
           varPrmType = adVarWChar
         Case vbLong
           varPrmType = adBigInt
         Case vbDate
           varPrmType = adDate
           'if SQL Server, use adDBTimeStamp
         Case vbInteger
           varPrmType = adSmallInt
         Case vbDouble
           varPrmType = adDouble
         Case vbSingle
           varPrmType = adSingle
         Case vbBoolean
           varPrmType = adBoolean
         Case vbCurrency
           varPrmType = adCurrency
            Case vbByte
           varPrmType = adUnsignedTinyInt
         Case vbNull
           varPrmType = Null
         Case Else
           'WARNING!  Not supported in ADO 2.5
           varPrmType = adVariant
       End Select
       If varPrmType = adVarWChar Then
         .Parameters.Append .CreateParameter( _
               "prm" & intLoop, varPrmType, adParamInput, 
Len(Params(intLoop)) + 2, Params(intLoop))
       Else
         'you have to create ALL the parameters
         .Parameters.Append .CreateParameter( _
               "prm" & intLoop, varPrmType, adParamInput, , 
Params(intLoop))
       End If 'varPrmType = adVarWChar
     Next intLoop
     .Execute RecordsAffected:=lngRecords, 
Options:=adCmdStoredProc
   End With
Proc_exit:
   On Error Resume Next
   CallADOStoredProc = lngRecords
   Set cmd = Nothing
   Exit Function
Proc_err:
   'ADO errors and Jet errors aren't the same
   'collection, so this handles them differently
   If colErrs.Count > 0 Then
     'There are ADO errors
     For Each errCurr In colErrs
       Select Case errCurr
         Case ERR_OPER_ON_INVALID_CONNECTION
         Stop
           Resume Proc_exit
         Case Else
             MsgBox errCurr.Number & "--" _
                     & errCurr.Description & " (" _
                     & errCurr.Source & ")"
             Resume Proc_exit
       End Select
     Next errCurr
     colErrs.Clear
   Else
     'there is an other error
     MsgBox Err.Number & "--" & Err.Description
     Resume Proc_exit
   End If
     Resume 0
End Function


John W. Colby
www.ColbyConsulting.com


Martin Reid wrote:
> John
> 
> I missed most of this thread as I am working on something else
> 
> Basically what you want is to
> 
> Execute a stored procedure passing in the name of the procedure to execute and other associated parameters which will be basically table names? 
> 
> 
> You will also need to change the database name in the connection string using a parameter?
> 
> That about it?
> 
> I haven't seen the Function you are referring to.
> 
> Martin