[AccessD] Stored procedures from Access

I don't see a provider in that connection string, John.

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, January 22, 2009 11:32 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com