Using Scope_Identity with an Update Query

This stuff

"[ISCenter_Monitor].[usp_log_ISCenter_Event]" & _
                    " @EventName='" & ReportName & "'," & _
                    " @ModuleName='" & MODNAME & "'," & _
                    " @ProcedureName='" & ProcName & "'"

has the look of T-SQL to it.
Are you playing mix-and-match?
Are you looking to create a pass-through query to execute a sproc on the SQL Server side?
Or are you looking to create a VBA/ADO procedure to execute on the MS Access side?

It has to be one or the other.

If it is a passthrough, you are missing EXEC for sure.  If it is not a pass-through, all those @ will be a problem.

Nice SQL syntax tutorials here
http://www.w3schools.com/sql/sql_update.asp

Although for myself, I always do updates through VBA recordset code.
Much easier to test and debug without screwing up the data, but...
The syntax is
UPDATE table_name
 SET column1=value1,column2=value2,...
 WHERE some_column=some_value;

so

Update DataSheet
 SET databaseUserID=Something,currentTimestamp=currentTimestamp,...
 WHERE some_column=some_value;

I think, though that you are looking to do that on the SQL Server side
You are hoping to get SQL Server to use
SELECT SCOPE_IDENTITY()
and update some field with it.

9 for 10 that will involve writing a new sproc on the server -- because I don't think you'll get that to happen in a passthrough.  You could write a passthrough to retrieve the result of the T-SQL 'SELECT SCOPE_IDENTITY()' and then use that value in another passthrough

"EXEC [ISCenter_Monitor].[usp_log_ISCenter_Event]" & "' " & ReportName & "'," & _
                    " '" & MODNAME & "'," & " " & ProcName & "'"
Although, where in there is that 'SELECT SCOPE_IDENTITY()' meant to go?

Remember, the syntax for Access to execute a passthrough is just
EXEC SomeSproc 'StringValue', IntegerValue,'DateValueAsLiteralString'

Here's an actual intelligible bit on how to use Scope_Identity
http://technet.microsoft.com/en-us/library/ms190315.aspx

You'll DEFINITELY need to write a new sproc that itself executes [ISCenter_Monitor].[usp_log_ISCenter_Event] if you are going to use it!

Thanks for all the info.

To clarify, I want to use VBA to execute sprocs.  here is my current SPROC - that does inlcude the @@IDENTITY value set.  The first portion of my code that executes the first SProc  successfully, however, When it reaches the 2nd exec statement it runs, but returns a null value in the SQL data.

I was ask to avoid using linked tables or dao.recordset types, hence my attempt to find the solution that will execute the SPROC, but I understand that when the Sproc is executed it creates the PK (EventID), it is this value I need in order to update the various fields with in the SQL table (ISCenter_EventLog) remaining fields like the ErrorMsg that occurred on the Access Side of the database.
'
I am looking for the best approach to create an Error log within SQL that is call via VBA that will capture any errors that may occur in the Access databae.

USE [TRACI_ANALYTICS]
GO

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 

Select allOpen in new window

I was ask to avoid using linked tables or dao.recordset types

So you were asked to run a world record 100m time AFTER being amputated below both knees :(

It is what it is.

I don't see Select Scope_Identity() anywhere in your T-SQL that you posted
I think what you are going to need is to declare a variable to hold that Identity near the nose of your procedure.


DECLARE @OpenEventID      integer
DECLARE @EndDate            datetime
DECLARE @RETVAL                  integer
 Declare @MyScopeIdentityValue           Integer
--I assume your identities are auto-numbers!



And down below where you are doing the insert you'll need to fill it
      BEGIN
            INSERT INTO      [ISCenter_Monitor].[ISCenter_EventLog]  
                  (EventName, EventStartDate, ModuleName, ProcedureName, EventStep, EventStepParentID)
            SELECT    @EventName
                        , GETDATE()
                        , @ModuleName
                        , @ProcedureName
                        , @EventStep
                        , @EventStepParentID
                        
            --SET @RETVAL = @@IDENTITY
                                   --I think that's the problem
                                   --Try
                                   select @MyScopeIdentityValue = Scope_Identity()
                                   
                                   
      END


And then when you are doing your update, use @MyScopeIdentityValue wherever you need it here.
      UPDATE      [ISCenter_Monitor].[ISCenter_EventLog]  
      SET              EventEndDate            = GETDATE()
                  , AffectedRows            = @AffectedRows
                  , StepSucceeded            = @StepSucceeded
                  , ErrorMessage            = @ErrorMessage
                  , @AdditionalInfo                        = @AdditionalInfo                        
      WHERE      EventID = @EventID

For Scope_Identity, the key is that you have to get it between the BEGIN and END where the record creation is happening

ok how do I call this and use the value of the Scope_Identity() from within the VBA in Access, since I am trrying to capture the "error Msg" from Access?

K

On the Access side, create a pass-through query that returns records
In VBA you are then going to alter that pass-through's SQL, and then execute it
In DAO, it would be

Dim db As Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL as string

Set db = CurrentDb
Set qdf = db.QueryDefs("TheNameOfYourPassThrough")
strSQL  = "exec TheNameOfYourSproc parameter1, parameter2, Parameter3"
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)

You'd then use rs!WhateverField however you'd need to

ADO has different syntax, but the idea is the same.

The thing is -- you are passing everything in to a sproc, so why does anything need to come back to Access?  Isn't the sproc going to do all this?

Ok, here's a little sproc I built to demo it

Alter PROCEDURE SpTestScopeIdentity 
	-- Add the parameters for the stored procedure here
	@JobID int = 0, 
	@Path nvarchar(255) = '',
	@FileName nvarchar(255) = ''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	Declare @MyScopeIdentityValue int
    -- Insert statements for procedure here
	Insert into tblPictures (JobID, Path, Filename)
	select @JobID,@Path,@FileName
	select @MyScopeIdentityValue = Scope_Identity()	
END
select @MyScopeIdentityValue,@JobID,@Path,@FileName
GO

Select allOpen in new window

And here's a query on the T-SQL side that uses that sproc and returns values

USE [TI_Data]
GO
DECLARE	@return_value int
EXEC	@return_value = [dbo].[SpTestScopeIdentity]
		@JobID = 1,
		@Path = 'w:\ti\',
		@FileName = '1.jpg'
GO

Select allOpen in new window

And it nicely returns 1 row, with the Identity value and three inputs
And in VBA I have

Dim db As Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qrySpTestScopeIdentity")
strSQL = "exec SpTestScopeIdentity 1, 'w:\ti\', '1.jpg'"
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
MsgBox rs.Fields(0)

Select allOpen in new window

And I get a nice messagebox with the autonumber created by the sproc

I need to caputre any errors that happen in Access and store the error information in the Sql tables.

so if this can be done inn Sproc - great,  the problem I have had in the past is that I can execute the first portion Insert query, however when I attempt to update the same record - it can't find the record or If I include the remaining fields in the original Insert Query - it returns "NULL" values for the Error Message, etc.

Please keep in mind that I am new to SPROC creation and SQL Server.  I am more an Access Person.

Thanks,

K

I definitely an Access dude -- but SQL server makes a great back-end
Here's ADO code to use my test sproc

Dim cn As ADODB.Connection
Dim strSQL As String
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionString = "ODBC;Description=ReesRDP;DRIVER=SQL Server;SERVER=REESRDP\SQLEXPRESS05;UID=nickmeyer;Trusted_Connection=Yes;DATABASE=TI_Data"

strSQL = "exec SpTestScopeIdentity 1, 'w:\ti\', '1.jpg'"

cn.Open
Set rs = CreateObject("ADODB.Recordset")
rs.Open strSQL, cn
MsgBox rs.Fields(0)

cn.Close
Set rs = Nothing
Set cn = Nothing

Select allOpen in new window

Does strSQL make sense to you?
That's all you are doing -- passing in EXEC someparameter, anotherparameter, anotherparamter (with the correct SQL delimiters for strings, of course)

Now, my simple sproc just spit the Scope_Identity back -- but at this point it is easy enough to do other stuff with it -- either in the sproc, or back in Access

Attempting to use the demo version - get the following errors:

Msg 208, Level 16, State 6, Procedure SpTestScopeIdentity, Line 28
Invalid object name 'SpTestScopeIdentity'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'dbo.SpTestScopeIdentity'.

Look carefully!
I posted it as an ALTER procedure...
Change that to a CREATE procedure

But--you will need to change the tables too
They come from my data!

Now,
Here's that sproc altered to do an insert AND then an update

Alter PROCEDURE SpTestScopeIdentity 
	-- Add the parameters for the stored procedure here
	@JobID int = 0, 
	@Path nvarchar(255) = '',
	@FileName nvarchar(255) = ''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	Declare @MyScopeIdentityValue int
    -- Insert statements for procedure here
	Insert into tblPictures (JobID, Path, Filename)
	select @JobID,@Path,@FileName
	
	select @MyScopeIdentityValue = Scope_Identity()
	
	update tblPictures
	set [Path] = 'w:\ti\bad\'
	where PictureID = @MyScopeIdentityValue			
END
select @MyScopeIdentityValue,@JobID,@Path,@FileName
GO

Select allOpen in new window

Funny thing was, I DEFINITELY don't like using UPDATE on production data.  That's just asking for trouble.  So I scripted tblPictures out to a query window and created tblPictures1 to test on.

Guess what?  It is doing what you have problems with -- null or zero values being dropped in and not what was inserted or updated

And when I went to go play with it again, now it is inserting correctly and updating correctly.
Go figger!

So, now for nuts and bolts.
When and how is this all to fire?
What does Access know and can pass in via a passthrough when it fires?
Does firing it create data that only Access knows about, or can everything be done in T-SQL in one go with what Access passes in?
And why are we doing an insert and an update?
Are there multiple tables being worked with?

I think I've given you what you need to use Scope_Identity()
Now, what do we need to do to solve your operational problem?

found possible solution - its easier when you finally figure out what word to using when searching the www for information - in this case the word was "OUTPUT", not parameter.

Came accross this.

http://bytes.com/topic/access/answers/205120-get-stored-procedure-output-value-back-vba

after: cmd.Execute Options:=adExecuteNoRecords
all you need is:

Dim X as long
x = cmd.Parameters("@OutPutVar")


Note I have not tested it because I am waiting on my coworker to grant me permissions to change the SPROC.

I need to modify it to include the new variable = @outputVar
and
Set that variable to equal @OutPutVar or hard code an actual value for testing purposes.

Have you ever used this approach, Nick?

Thanks for all your great suggestions - I will let you know if this actually resolves the issue - along with the code changes.

K

Generally, I operate in DAO.  Wherever I can, I use VBA DAO recordsets to do my data heavy lifting -- because I never deal with more than a few records at a time, so any mythical performance hit over straight up SQL is miniscule -- and it is a heck of a lot easier to debug

with rs
    .edit
    !somefield = somevalue
    .update
end with

because you can drop a breakpoint on the .update, and then run a mouse over each value in the code window to see what you are going to get.

A SQL update or insert command is not so nice to debug.

I have a whack of passthroughs that return actual recordsets to Access.  Generally, the return_value isn't a lot of use to me -- I either got my recordset or I didn't -- and I am not executing sprocs that alter data where I would be looking for a codeback value to tell me about success or failure.

Here was where I hunted down how to get the ADO code right
http://social.msdn.microsoft.com/Forums/office/en-US/2c350f9d-68ea-4039-83b5-33b12bf66ca5/problem-executing-stored-procedure-from-vba-ado-connection

You'll see that early on they are working on a command approach -- which you've now seen can return a parameter back.

I prefer to get the whole recordset back to VBA -- because then I can walk through that to see what I've done.  My final iteration of my test sproc is

Alter PROCEDURE SpTestScopeIdentity 
	-- Add the parameters for the stored procedure here
	@JobID int = 0, 
	@Path nvarchar(255) = '',
	@FileName nvarchar(255) = ''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	Declare @MyScopeIdentityValue int
    -- Insert statements for procedure here
	Insert into tblPictures1 (JobID, Path, Filename)
	select @JobID,@Path,@FileName
	
	select @MyScopeIdentityValue = Scope_Identity()
	
	update tblPictures1
	set [Path] = 'w:\ti\bad\'
	where PictureID = @MyScopeIdentityValue			
END



select @MyScopeIdentityValue as II,@JobID as iJobID,@Path as iPath,@FileName as iFileName, PictureId,jobID,Path,filename from tblPictures1 where Pictureid= @MyScopeIdentityValue
GO

Select allOpen in new window

When I execute it from the Access side, I get a recordset back of all my input parameters, and the resulting field entries.  Very easy for me to see if I've got things right!

But returning back the output parameter and building the error-handling into the T-SQL is certainly a valid approach.  I am an Access VBA DAO guy.  My T-SQL gets used when Access can't handle things quickly.  As long as Access is up for it, I code it there.

Trying to debug between multiple layers can be a bugger
KYSS -- Keep your SQL simple!

When you do these kinds of things in  DAO, it is pretty straightforward to pull back the identity value

Private Sub ConsigneeID_NotInList(NewData As String, response As Integer)
Dim db As Database
Dim rs As Recordset
Dim lngOwnerID As Long
If vbYes = MsgBox("'" & StrConv(NewData, vbProperCase) & "' is not entered as a current Client." & vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, " ") Then
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM [tblClients] WHERE 1=2;", dbOpenDynaset, dbSeeChanges)
    With rs
        .AddNew
        ![Client Name] = StrConv(NewData, vbProperCase)
        .Update
        .Bookmark = .LastModified
        lngOwnerID = ![Client ID]
    End With
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
       
    response = acDataErrAdded
Else
    response = acDataErrContinue
End If
End Sub

Select allOpen in new window

This is a NotInList() event from a combo box. lngOwnerID is the identity value created and was straightforward to pull back to the sub -- although I don't make any use of it in the code I posted.  In production, a form is opened, filtered to OwnerId= lngOwnerID

But someone doesn't really want you using what Access is really good at :(_

I have code that does a straight SQL insert -- but doesn't pull back the value for use, too

    Set db = CurrentDb
    strSQL = "INSERT INTO [tblInsLocations] ([LocationName],[HSTProvince]) VALUES('" & StrConv(NewData, vbProperCase) & "', " & response & ");"
    db.Execute strSQL

Select allOpen in new window

This code is ADO -- which is what you'd like -- and pulls back the new identity value

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim strSQL As String
Dim x As Integer
Dim PicID As Long
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionString = "ODBC;Description=ReesRDP;DRIVER=SQL Server;SERVER=REESRDP\SQLEXPRESS05;UID=nickmeyer;Trusted_Connection=Yes;DATABASE=TI_Data"

'strSQL = "exec SpTestScopeIdentity 1, 'w:\ti\', '1.jpg'"
'not a sproc but a straight table manipulation
strSQL = "select * from tblPictures1"
cn.Open
Set rs = CreateObject("ADODB.Recordset")
rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic
    With rs
        .AddNew
        !JobID = 1
        !Path = "w:\ti\"
        !Filename = "14.jpg"
        .Update
        .MoveLast
        PicID = !PictureID
    End With
cn.Close
Set rs = Nothing
Set cn = Nothing

MsgBox PicID

Select allOpen in new window

Because of how ADO works with cursors
http://www.w3schools.com/ado/prop_rs_cursortype.asp
There isn't any danger that the identity value you haul back will be someone else's

Modifying how you execute your insert, and doing it through VBA and ADO and not using as a sproc, you should be able to haul back the identity value to then do your update through ADO as well.

in this case the word was "OUTPUT", not parameter.
Actually, as your Stored Procedure is written here http:#a39453161 there is no need to make any changes whatsoever.  You are already returning a special type of output parameter, it is called a RETURN parameter.  All you have to do is check for the "RETURN" parameter when the call is complete.  If you are not sure, post your MS Access code as is and I can suggest changes.

The OUTPUT parameter is useful when you want to return a scalar data type that is not an integer or when you cannot use the RETURN parameter.

The only minor quibble I have with your Stored Procedure is with your use of @@IDENTITY, I would use SCOPE_IDENTITY() instead.  The reason for this, is that if that table has a TRIGGER on it and if that TRIGGER inserts into another table with an identity column, then the @@IDENTITY value would contain the identity value from that other table.  Clearly not what you want, but as you can tell the risk involves a lot of Ifs that I should not think is your case.  Moral of the story:  Get in the habit of always using SCOPE_IDENTITY() instead of @@IDENTITY (unless you are still using SQL Server 7).

Ok,  Here is my latest attempt:

The problem is the update of data for the existing newly created ID still will not update the necessary fields - Unable to Update the NULL values.  Note I am able to get the REturn Value (EventID) for the second portion of the code

I am able to debug the code and there are actual values, however, these values do not update the Null values in the Sql Table.

thanks,

K

Function ParamSPT(ReportName As String, MODNAME As String, RecCt As Long, ProcName As String, _
                    sErr As String, nResults As Boolean)
   
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim parm As ADODB.Parameter
Dim EventID As Long
Dim X As Integer

Const sproc As String = "ISCenter_Monitor.usp_log_ISCenter_Event"
Const connstr = _
    "Driver={SQL Server};Server=AQL02;database=TRACI_ANALYTICS;UID=;PWD="
   
   On Error GoTo ParamSPT_Error

    conn.ConnectionString = connstr
    
    conn.Open
    
    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = sproc
        .Parameters("@EventName").value = ReportName
        .Parameters("@ModuleName").value = MODNAME
        .Parameters("@ProcedureName").value = ProcName
    End With
    
    cmd.Execute
        EventID = cmd.Parameters("@RETURN_VALUE").value
        X = EventID
    With cmd
        .Parameters("@Eventid").value = X
        .Parameters("@ErrorMessage").value = sErr
        .Parameters("@AffectedRows").value = RecCt
        .Parameters("@StepSucceeded").value = nResults
    End With
    
    conn.Close
   On Error GoTo 0
   Exit Function

ParamSPT_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ParamSPT of Module basErrorEventLog"
End Function

Select allOpen in new window

USE [TRACI_ANALYTICS]
GO

/****** Object:  StoredProcedure [ISCenter_Monitor].[usp_log_ISCenter_Event]    Script Date: 09/04/2013 13:37:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE 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
--
-- AUTHOR:      Mark Gendron (49mwg)
--
-- 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 

SET @RETVAL = 0 

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 = SCOPE_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 

GO

Select allOpen in new window

As you recall, I had problems with my test table not accepting any values.
And then, POOF!, the problem went away.

I'd look at the table that isn't taking the updates
Can you hand-edit the table in SSMS?
Can you (for testing) create a linked table and hand-edit it in Access?
Can you run an Update query against that linked test table in Access?

You may have a timestamp issue!
Does your table have a timestamp column?
Does it need one?

You do have a primary key, right?

Look over the table (not) taking the updates -- and see if it will take ANY updates first!

Here is my solution - so far.  Got it from help from a co-worker.  Thanks for all your time and effort - your input was invaluable.

Thanks,

Karen

Option Compare Database
Option Explicit

Public Const SPROC As String = "ISCenter_Monitor.usp_log_ISCenter_Event"
Public Const ConnStr = _
    "Driver={SQL Server};Server=AQL02;database=TRACI_ANALYTICS;UID=;PWD="


Public Sub ErrorLogger()
    
    Dim EventID As Long
    Dim RowsAffected As Long
    Dim ErrorMessage As String
    Dim AdditionalInfo As String
    Dim StepSucceeded As Integer
    
    On Error GoTo PROC_ERROR
    
    RowsAffected = 999
    ErrorMessage = "Test Error Message #003"
    AdditionalInfo = "Testing from MS Access VBA/ADODB"
    StepSucceeded = 1
    
    EventID = OpenErrorLogger()
    If EventID <> 0 Then UpdateErrorLog EventID, RowsAffected, _
        ErrorMessage, AdditionalInfo, StepSucceeded
    MsgBox "EventID was " + CStr(EventID)
    
    GoTo PROC_EXIT
    
PROC_ERROR:
    MsgBox Err.Description
    Resume PROC_EXIT
    
PROC_EXIT:
    
End Sub

Public Function OpenErrorLogger() As Long
    On Error GoTo PROC_ERROR
    
    Dim conn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim EventID As Long
    
    EventID = 0
    
    conn.ConnectionString = ConnStr
    conn.Open
    
    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = SPROC
        .NamedParameters = True
            .Parameters("@EventName").Value = "Test Event"
            .Parameters("@ModuleName").Value = "Test Module"
            .Parameters("@ProcedureName").Value = "Test Procedure"
    End With
    cmd.Execute
    EventID = cmd.Parameters("@RETURN_VALUE").Value
    
    GoTo PROC_EXIT
    
PROC_ERROR:
    MsgBox Err.Description
    Resume PROC_EXIT
    
PROC_EXIT:
    On Error Resume Next
    conn.Close
    OpenErrorLogger = EventID
    
End Function
Public Sub UpdateErrorLog(EventID As Long, RowsAffected As Long, ErrMsg _
    As String, AdditionalInfo As String, StepSucceeded As Integer)
    On Error GoTo PROC_ERROR
   
    Dim conn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    
    conn.ConnectionString = ConnStr
    conn.Open
    
    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = SPROC
        .NamedParameters = True
    
        .Parameters("@EventID").Value = EventID
        .Parameters("@ErrorMessage").Value = ErrMsg
        .Parameters("@StepSucceeded").Value = StepSucceeded
        .Parameters("@AffectedRows").Value = RowsAffected
        .Parameters("@AdditionalInfo").Value = AdditionalInfo
    End With
    
    cmd.Execute
    
    GoTo PROC_EXIT
    
PROC_ERROR:
    MsgBox Err.Description
    Resume PROC_EXIT
    
PROC_EXIT:
    On Error Resume Next
    conn.Close
    
End Sub

Select allOpen in new window

:)

Glad to be of service

Nick67