Calling a Stored Procedure to get Paramter value using ADO

Hi,

maybe the attached libraries are helpful for this purpose. As I work mainly with ADPs I've written an ADO library class ("clsADO") which contains a lot of functions which are useful to access SQL Server with ADO.

You only need to adjust the connection string (in the current version it uses the SQLNCLI driver which is recommended but needs an installation of the SQLNCLI driver on the target computer, it also uses SQL User connection) and the constants at the beginning of the class which are needed for the server name and so on.

The library also contains a very comfortable message box which is a replacement of the VBA message box and can be used anywhere with "Msg.Box". The first parameters are identical to the standard message box, it contains a lot additional parameters to display a SQL table from a SQL command or from a filled ADO recordset (which are automatically converted to a HTML table which is displayed in the message box).
Based on this message box another class is responsible for displaying VBA and/or ADO errors which are displayed in a structured HTML table. The message box contains additional buttons to export the message to Excel, print, mail or save the message. This makes it easy for the user to mail an error message to the programmer without the need of creating screen shots.
There is also an additional message box class which allows to create a kind of "conditional formatting" with such HTML tables to mark for example single cells in a different background color.

The ADO class uses this error class (and therefore the message box) to display ADO errors.

There are some "ExecuteSP" functions which are able to return an ADO recordset or an ADO command object (this is useful to access SP parameters declared as OUTPUT after executing the SP, it can also return an ADO recordset which is stored into the classes own ADO recordset property which gives you the ability to return a recordset, OUTPUT variables from the SP and also the @RETURN_VALUE returned with the "RETURN" T-SQL command).
The functions to execute SPs are made in a way that it should work with any SP.

To call any of these functions just type "FNADO." and then you instantiate an object of the class clsADO at the first time it's being used and you then see a list of the available functions displayed with IntelliSense.
The class is used in my current project and is used since around 4 years, works very stable.

Before you can use the library you need to change the references in the VBA editor because I saved this with A2010 and this can only use the 14.0 versions of the libraries. So if you only have A2003 available you need to open the reference list and adjust these libraries to the A2003 versions, then compile it again.

If you want to use this in your own database you simply need to import all modules into your own and add all VBA references listed in the current version available on your computer. It should compile and work even with older library versions.

Cheers,

Christian
ADOAndMsgBox.zip

thanks for the suggestion, however, it is in what appears to be in german, is there an English version?

Hi,

the very most part of it is documented in English. The only module with German comments is "modObjects" - I don't think that the code here is very complicate to understand. "modObjects" is used to instantiate the class modules in a single object, for example "Msg" to instantiate a message class where "Box" is a function of the class (so you can use "Msg.Box"). In the same way you can use "FNADO" to instantiate a clsADO object to directly use the class without creating an object variable anywhere.

The advantage of this method is that you CAN use the modObjects module for easier access, but you don't need it, you can also create an object variable and instantiate the class module on your own (that's sometimes good if you want to create independent ADO objects for example).

Cheers,

Christian

Sorry, I am very confused by this code - its seems to be overkill for what I am trying to accomplish.  I just want to make a connection to the SQL data via calling a stored procedure from within Access, I want to call the class when a procedure starts and ends.  Capturing the Start Date/Time, determine if the procedure/code Passed or Failed, and then enter the compeltion time. Doing this without the use of pass thru query.  If it fails, then capturing the error message.  all by updating the EventLog table (from within sql).

Where in this sample code could I simplify and still accomplish my task?

Thanks,

Karen

Hi,

after changing the constants to work with your SQL Server like described above you could execute the stored procedure with this library like this (for your example 1 in your test execute script):

Debug.Print FNADO.ADOExecuteSP("[ISCenter_Monitor].[usp_log_ISCenter_Event]", _
                               "@EventName,@EventStepParentID", _
                               "Test Event,999")

Select allOpen in new window

(Instead of "Debug.Print" you can use a variable or whatever you want to do with the return value.)

The classes in the demo database is no sample code, it's a fully usable ADO library which also contains a general usable error and message box object. Any of the functions in the library are used in a large database project working for years. If you do not want to use it you can at least see what you need to do to execute a stored procedure and open a connection.
There are variants of this function which can return an ADO recordset (if the SP contains a SELECT command) or an ADO connection object to read out parameters declared as OUTPUT in the SP and changed inside the SP after it was executed.

The "SELECT * FROM ISCenter_Monitor.ISCenter_EventLog" is not part of your SP, but you can of course also return an ADO recordset like with the DAO "DLookup":

With FNADO
    Debug.Print .ADOExecuteSP("[ISCenter_Monitor].[usp_log_ISCenter_Event]", _
                              "@EventName,@EventStepParentID", _
                              "Test Event,999")
    Set .rsADO = .ADOSelect("*","ISCenter_Monitor.ISCenter_EventLog")
End With

Select allOpen in new window

This would execute the SP like above and then set the "rsADO" ADO recordset which is also part of the class to the result of the SELECT created by the "ADOSelect" function. This function can also use a WHERE statement and an ORDER BY statement with further parameters. You can see the description in the documentation of each function.

You see, it's very easy to work with this library and you don't need to think about ADO details all the time, it's as easy as working with DAO domain functions. The connection is automatically opened and closed after execution of each command.

Cheers,

Christian

it doesn't like the adOpenStatic, after I updated the references for Access 2003.  what am I missing?

Attempting to test the Sample and the form load has issues:

Missing TempVars????

Private Sub Form_Load()
    Dim strStyle As String
    strStyle = "<style type=""text/css""><!--" + vbCr + _
               "body {font-family: Arial; background-color: #FFFFE0}" + vbCr + _
               "--></style>"
    SetStyle strStyle
    ' reset the image to an empty icon
    frmMsgPic.PictureData = frmIconEmpty.PictureData
    Me.bolStop = False
    If Nz(TempVars("bolDebug")) <> "" Then
        If TempVars("bolDebug") Then
            Me.cmdStop.Visible = True
        Else
            Me.cmdStop.Visible = False
        End If
    End If
End Sub

Where can I find the ACEDAO.dll file?

I got this error when I tried to incorporate this code into my working project.

K

Hi,

you must insert a reference to the ADO 2.8 library. The ACE library is the replacement for the DAO 3.6 library since A2007, in A2003 and earlier it was the DAO 3.x library which should be already in the list of your references. So you can simply remove the ACE link.

Cheers,

Christian

I am still unable compile the code due to the missing object of "Form_frmMessage"

I do see the     Dim frmCCMsg As Form_frmMessage
 in the PublicFunction Box.  but the code will not compile?

Any ideas?

K

Hi,

yes, you probably didn't insert the form "frmMessage" from the database above into your own database. This is the HTML message box (the replacement for the normal VBA message box) which is much more comfortable.

Cheers,

Christian

Ok I figure that out.  How I do not want to use a message box,  My code will be part of an automation process running at night and I do not want to interupt the process to deal with a msgbox.  I appreciate your example and code, and all the work it took to create it.  However,  All I need to do is capture when a process failed in my automated nightly runs and update the error log in Sql.  What do I need to do to modify your code to accomplish this?  By passing the variables results and entering that data into the Log Table.

K

Hi,

you can of course use the library without the message box. In the procedure where the Form_frmMessage is instantiated the only job is to create a formatted message. So you can simply take the message parameters (simple strings) of that function and do with it what you want - surpress it, save it to a log file, whatever you like. In the simplest form you can write "Exit Function" right after the "Public Sub fnErr" row so simply nothing happens.

You also do not need to use the complete thing, but you should be able to find really ANY code to execute a stored procedure or open and close an ADO connection so you can extract the wanted rows and build your own procedure.

Cheers,

Christian

Also I want to be able to use the current user id and Windows' Login as name and password.

Private Const cActualDatabase   As String = "TRACI_ANALYTICS"
' use full qualified domain name (FQDN) to make sure the database will run from anywhere
Private Const cActualDBServer   As String = "AQL02.ISCenter_EventLog\ISCenter_Monitor"

Private Const cActualDBUser     As String = "????????????"


Private Const cActualDBPassword As String = "?????????????????"

Please verify my ActualDBServer syntax!

K

I just found this code within the project I am currently attempting to modify.

Could you help me modify the code towards my needs?

Public Function LookupInfoUsingKey(pstrDBT As EnumLookupDBType, _
                            ByVal pstrKey As String, _
                   Optional ByVal pstrNotFoundValue As String = "" _
                            ) As String

Const ProcName = "LookupInfoUsingKey"

  On Error GoTo PROC_ERR

Dim strConnect          As String
Dim strReturn           As String
Dim lngResult           As Long
Dim lngRecordsAffected  As Long


    m_strLastError = ""
    
    Set mConnect = New ADODB.Connection
    Set mCommand = New ADODB.Command
    
    strConnect = ConnectString(pstrDBT, m_strServerName, m_strDatabaseName, m_strSQL_UID, m_strSQL_PWD)
    
    With mConnect
        .ConnectionString = strConnect
        .Mode = adModeRead
        .Open
    End With
    
    With mCommand
        .ActiveConnection = mConnect
        .CommandType = adCmdStoredProc
        .CommandText = SProcName
        .Parameters("@Key") = pstrKey
        .Execute lngRecordsAffected
        strReturn = Nz(.Parameters("@Value"))
        lngResult = .Parameters("@Return_Value")
    End With
    
    Select Case lngResult
    
        Case 9902
            m_strLastError = "Warning, value not found by " & SProcName
        Case 9903
            m_strLastError = "Error, unable to look up value by " & SProcName
        
    End Select

    
    

PROC_EXIT:
  On Error Resume Next
    
    If (m_strLastError = "") Then
        LookupInfoUsingKey = strReturn
        mConnect.Close
        
    Else
        LookupInfoUsingKey = pstrNotFoundValue
        
        m_strLastError = MODNAME & "." & ProcName & " -- " & m_strLastError
        
        'MsgBox m_strLastError
    End If
    
  On Error GoTo 0

    Exit Function

PROC_ERR:
    m_strLastError = Err.Number & ". " & Err.Description
    
    Resume PROC_EXIT

End Function

Private Function ConnectString(DBT As EnumLookupDBType, _
                                ServerName As String, _
                                DatabaseName As String, _
                                Optional SQL_UID As String, _
                                Optional SQL_PWD As String _
                                ) As String
 
Const ProcName = "ConnectString"

Dim strConnect  As String


  On Error GoTo PROC_ERR

    m_strLastError = ""
    
     
    Select Case DBT
    
        Case DBT_SQL_Server
        
            If SQL_UID = "" Then
                strConnect = "PROVIDER=SQLOLEDB;DATA SOURCE=" & ServerName & _
                                ";INITIAL CATALOG=" & DatabaseName & _
                                ";INTEGRATED SECURITY=SSPI;"
            Else
                If SQL_PWD = "" Then
                    strConnect = "PROVIDER=SQLOLEDB;DATA SOURCE=" & ServerName & _
                                    ";INITIAL CATALOG =" & DatabaseName & _
                                    ";UID=" & SQL_UID
                Else
                    strConnect = "PROVIDER=SQLOLEDB;DATA SOURCE=" & ServerName & _
                                    ";INITIAL CATALOG =" & DatabaseName & _
                                    ";UID=" & SQL_UID & _
                                    ";PWD=" & SQL_PWD
                End If
            End If
        
        Case DBT_Access
        
    
    End Select
    
    

PROC_EXIT:
  On Error Resume Next
    
    If (m_strLastError = "") Then
        ConnectString = strConnect
    Else
        ConnectString = ""
        
        m_strLastError = MODNAME & "." & ProcName & " -- " & m_strLastError
        'mobjError.AppendExtraInfo ...
        
        'mWriteLog 0, m_strLastError
        
        'mobjError.ShowMessage
    End If
    
  On Error GoTo 0

    Exit Function

PROC_ERR:
    m_strLastError = Err.Number & ". " & Err.Description
    
    Resume PROC_EXIT

End Function

Select allOpen in new window

To reiterate - I need to accomplish the following:

On error write the following to a SQL Pass thru via Automation
  - EventName
 - ModuleName
 - Procedure NAme
 - EventStep
 - EventStep ParentID
 - EventStartDate
 - EventEndDate

What do I need to do to this code to allow me to Append data to the sql tbl ISCenter_EventsLog?

Thanks for all your efforts.

K

I cannot tell you if your server syntax is correct, you should already have that information from your local IT. In general, a FQDN would be "yourserver.yourdomain.yourtopleveldomain" for example. And if it has more than the standard instance then it should be additionally "\YourInstance".

But that has nothing to do with the login. Please look into the "ADOOpenConnection" procedure, there you'll find the alternative connection string which you need if you do not have a SQL Server login but Windows authentication. In the last case you don't need any password or username, the "Integrated Security='SSPI'" tells the server to use the Windows credentials automatically.

ok I was research like you suggested and came accross another of your post.  This may be what I am looking for what do you think?

https://www.experts-exchange.com/questions/26733748/Access-ADO-Recordset-via-Stored-Procedure-Multiple-Paramaters.html

ADOExecuteSP_RS

Dah,  Just found it in your code sample you gave me earlier.

Could you help me get this working for my needs?

To get the ADOExecuteSP_RS
 to work I need to have my error log build a string that contains the values of the parameters in a comma delimited setup correct?

ADOExecute(  @EventName,@ModuleName,@ProcedureName,@EventStep,@ParentID,@EventStartDate, @EventEndDate), "ABC", 123, "abc",'DEF", 454, #1/1/2012#, #3/1/2013#

Right????

OR

Is this doing just the opposite, take data from SQL to put into ACCESS?
K

Hi,

yep, this is the same procedure coming from exactly this library (only a little older version).

The way how you can execute it I've also described here.

You only need to add your complete list of parameters to it and also the complete list of values you want to write (and, by the way, you must not use the Access syntax for dates but the SQL Server syntax like 20131231, not #12/31/2013#, also you must not use any other delimiter character or any space and you can change the delimiter character if you want to use another than ","). Please see the posting above with the example where only two parameters was used.

Cheers,

Christian

This what I entered in my error code for the error log>

 ADOExecuteSP_RS "usp_log_ISCenter_Event", "@EventName, @ModuleName,@ProcedureName , @EventStep,@EventStepParentID, @StepSucceeded, @ErrorMessage,@AdditionalInfo", "Test Event, NULL,NULL,NULL, NULL,2013-07-15,13:57:35.950,NULL,NULL,NULL,NULL,NULL"

What is wrong with my syntax?

K

ok I now am unable to get the connection when I use the

ErrorHandler:
    DoCmd.Hourglass (False)
    DoCmd.SetWarnings (True)
    'MsgBox "The database has experienced an error." & vbCrLf & "If you are not aware of the cause of the error, see your manager before proceeding.", , "Database Error"
    
  '>>>>>  With FNADO
    
        Debug.Print _
            .ADOExecuteSP_RS("[ISCenter_Monitor].[usp_log_ISCenter_Event]", _
            "@EventName, @ModuleName,@ProcedureName,@EventStep,@EventStepParentID, @StepSucceeded, @ErrorMessage,@AdditionalInfo", _
            "Test Event, NULL,NULL,NULL, NULL,2013-07-15,13:57:35.950,NULL,NULL,NULL,NULL,NULL")
        Set .rsADO = .ADOSelect("*", "ISCenter_Monitor.ISCenter_EventLog")

    End With
    'Call HandleError(ErrNumber:=Err.Number, _
                                    ErrDescription:=Err.Description, _
                                    ErrTitle:=Err.Source, _
                                    module:=MODNAME, _
                                    Procedure:=ProcName)
    Exit Sub
End Sub

Select allOpen in new window

How do I pass the variable with the connection string?
K

Hi,

there is no need to pass in any connection string. Simply adjust it once in the ADOOpenConnection sub which will be used by all of this subs.

The "Debug.Print" was only for demonstration purposes, you do not need that in the end-user database.

The date value should be passed without the "-". The time value contains a microsecond value which I'm not sure if ADO pass that correctly - but try it. I would not create two different fields for date and time, simply use one field for both.

Cheers,

Christian

sorry this is getting very confusing.

How do I make the connection to the sql, do I need to use a passthru query.

How do I setup the Const for the database name, DBServer, and that it will get the window user name and password as trusted connection.

Private Const cActualDatabase   As String = "TRACI_ANALYTICS"
' use full qualified domain name (FQDN) to make sure the database will run from anywhere
Private Const cActualDBServer   As String = "AQL02.ISCenter_Monitor.ISCenter_EventLog"
Private Const cActualDBUser     As String = "THYSSENNA\49kxs"
Private Const cActualDBPassword As String = ""

Select allOpen in new window

for cActualDBUser     What should be used instead of the hardcoded names.
for cActualDBPassword  - What is the correct syntax to use the trusted Connection or uses the current users pswd?

K

thanks for the input- have not had time to test solution - plan to return when time permits