Solved

Calling a Stored Procedure to get Paramter value using ADO

Posted on 2013-06-28
22
533 Views
Last Modified: 2013-08-15
I am very rusty and need some assistances in creating an MS Access ADO Class that will return the parameter value of a SqL stored procedure.  I need to be able to execute the following stored Procedure from within MS Access 2003 and be able to access the listed parameters.  This will create an Event log when processing code checking for pass and fails, lenght of time to processs, etc.

Not sure where to begin,

Thanks.


StoredProc:

USE [TRACI_ANALYTICS]
GO
/****** Object:  StoredProcedure [ISCenter_Monitor].[usp_log_ISCenter_Event]    Script Date: 06/28/2013 14:12:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [ISCenter_Monitor].[usp_log_ISCenter_Event]  
(
	  @EventID				integer			= NULL 
	, @EventName			nvarchar(255)	= NULL 
	, @ModuleName			nvarchar(255)	= NULL 
	, @ProcedureName		nvarchar(255)	= NULL 
	, @EventStep			nvarchar(255)	= NULL 
	, @EventStepParentID	integer			= NULL 
	, @AffectedRows			integer			= NULL 
	, @StepSucceeded		bit				= NULL 
	, @ErrorMessage			nvarchar(512)	= NULL 
	, @AdditionalInfo		nvarchar(1024)	= NULL 
)
AS BEGIN 


--========================================================================================
--
-- SCRIPT:      usp_log_ISCenter_Event
-- DESCRIPTION:  Logs an event to ISCenter_Monitor].[ISCenter_EventLog]
--
-- The following parameters are used only when an event entry is being created. 
-- These parameters will be ignored when closing an event; no error will be raised.  
-- (@EventID is NULL):
--	 @EventName
--	 @ModuleName
--	 @ProcedureName
--	 @EventStep
--	 @EventStepParentID
--
-- CHANGE HISTORY
-- DATE		    BY      ISSUE #          DESCRIPTION
-- ------------ ------- ---------------  -----------------------------------------------------------
-- 27-June-2013 49mwg					 Created sproc. 
--
--========================================================================================

	
SET NOCOUNT ON 
	
DECLARE @OpenEventID	integer
DECLARE @EndDate		datetime 
DECLARE @RETVAL			integer

BEGIN TRY 

IF @EventID IS NULL 
BEGIN 
	-- Creating a new event log entry
	-- If @EventStepParentID is provided, verify that the ParentID exists. 
	IF @EventStepParentID IS NOT NULL 
	BEGIN 
		SELECT	@OpenEventID = EventID 
		FROM	[ISCenter_Monitor].[ISCenter_EventLog] 
		WHERE	EventID = @EventStepParentID 
	
		IF @OpenEventID IS NULL 
		BEGIN 
			RAISERROR   (  N'A parent event was specified (EventID = %d), but no matching EventID was found in the event table.'  
						 , 11		-- severity 
						 , 1		-- state
						 , @EventStepParentID 
						)	
		END 
	END 
	
	BEGIN 
		INSERT INTO	[ISCenter_Monitor].[ISCenter_EventLog]  
			(EventName, EventStartDate, ModuleName, ProcedureName, EventStep, EventStepParentID) 
		SELECT    @EventName 
				, GETDATE() 
				, @ModuleName 
				, @ProcedureName 
				, @EventStep 
				, @EventStepParentID 
				
		SET @RETVAL = @@IDENTITY
	END 
	
END 

ELSE BEGIN 
	-- Closing an existing event log entry
	-- Verify that the specified @EventID exists. 
	
	IF @StepSucceeded IS NULL 
	BEGIN 
		RAISERROR   (  N'@StepSucceeded is NULL. You must specify SUCCEED (1) or FAIL (0).'  
					 , 11		-- severity 
					 , 1		-- state
					 , @EventID  
					)
	END 
	
	SELECT	@OpenEventID	= EventID, 
			@EndDate		= EventEndDate 
	FROM	[ISCenter_Monitor].[ISCenter_EventLog] 
	WHERE	EventID = @EventID 
	
	IF @OpenEventID IS NULL 
	BEGIN 
		RAISERROR   (  N'The specified event (EventID = %d) was not found in the event table.'  
					 , 11		-- severity 
					 , 1		-- state
					 , @EventID  
					)
	END 
		 
	IF @EndDate IS NOT NULL
	BEGIN 
		RAISERROR   (  N'The specified event (EventID = %d) already has an end date specified. This event may not be updated.'  
					 , 11		-- severity 
					 , 1		-- state
					 , @EventID  
					)
	END 
		 
	UPDATE	[ISCenter_Monitor].[ISCenter_EventLog]  
	SET		  EventEndDate		= GETDATE() 
			, AffectedRows		= @AffectedRows 
			, StepSucceeded		= @StepSucceeded 
			, ErrorMessage		= @ErrorMessage 
			, @AdditionalInfo	= @AdditionalInfo				
	WHERE	EventID = @EventID 				

END 

END TRY 


BEGIN CATCH
    DECLARE @ErrorMsg NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMsg = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMsg, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );

	RETURN -1 
END CATCH 

RETURN @RETVAL  

SET NOCOUNT OFF 

END 

Open in new window


Here is my current Test Script for SQL
-- Test script. Run on AQL02. 

USE TRACI_ANALYTICS 
GO 

-- TRUNCATE TABLE [ISCenter_Monitor].[usp_log_ISCenter_Event] 

DECLARE @RETVAL integer 

EXEC @RETVAL = [ISCenter_Monitor].[usp_log_ISCenter_Event] 
	    @EventName = 'Test Event' 
	  --, @EventStepParentID = 999 

SELECT @RETVAL 



--DECLARE @RETVAL integer 

EXEC @RETVAL = [ISCenter_Monitor].[usp_log_ISCenter_Event] 
	    @EventID = 20
	  , @AffectedRows = 99999 
	  --, @StepSucceeded = 0 
	  , @ErrorMessage	= 'Error Message' 
	  , @AdditionalInfo = 'Additional Information' 

SELECT @RETVAL 


SELECT	* 
FROM	ISCenter_Monitor.ISCenter_EventLog

/*

Open in new window

0
Comment
Question by:Karen Schaefer
  • 12
  • 10
22 Comments
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
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
0
 

Author Comment

by:Karen Schaefer
Comment Utility
thanks for the suggestion, however, it is in what appears to be in german, is there an English version?
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
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
0
 

Author Comment

by:Karen Schaefer
Comment Utility
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
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
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")

Open 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

Open 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
0
 

Author Comment

by:Karen Schaefer
Comment Utility
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
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
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
0
 

Author Comment

by:Karen Schaefer
Comment Utility
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
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
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
0
 

Author Comment

by:Karen Schaefer
Comment Utility
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
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
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
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Karen Schaefer
Comment Utility
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
0
 

Author Comment

by:Karen Schaefer
Comment Utility
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

Open 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
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
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.
0
 

Author Comment

by:Karen Schaefer
Comment Utility
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?

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26733748.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
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
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
0
 

Author Comment

by:Karen Schaefer
Comment Utility
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
0
 

Author Comment

by:Karen Schaefer
Comment Utility
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

Open in new window

How do I pass the variable with the connection string?
errK
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
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
0
 

Author Comment

by:Karen Schaefer
Comment Utility
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 = ""

Open 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
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
Comment Utility
sorry also, but a little bit thinking is also up to you, I'm afraid.

When I look at your initial post, it seems to be that there is a table named "ISCenter_EventLog" which has the schema name "ISCenter_Monitor". So if you find a constant named "cActualDBServer" why do you write these names into that constant? I cannot believe that you think that this is the name of the server?

I also wrote above that in the sub "ADOOpenConnection" there are connection strings which you can adjust like you need. There is also a sample connection string in the remarks which contains the syntax which you need to use in case of Windows authentication, I also wrote above that in this case you don't need to use the user and password constants. So why do you write a username and additionally a domain name in the user constant?

If you do not know what a connectionstring is that please read the following site:
http://www.connectionstrings.com/sql-server/

Here you'll find all possible sorts of connectionstrings for nearly any database. You can take one which works for your environment (if you don't know the parameters, you need to ask the database admin in your environment) and insert it into the ADOOpenConnection sub and replace the assembling of the connectionstring with constants by your working connectionstring.
0
 

Author Closing Comment

by:Karen Schaefer
Comment Utility
thanks for the input- have not had time to test solution - plan to return when time permits
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now