Error 7874

[CyberDwarf]

Hi y'al

I have hit a problem within Access which gives the error message:-
" Error 7874 - Access cannot find object 'Select column1 as 1, column0 as 2....'

This follows a save (in VB) for a new record to the backend SQL Server table.

There is an insert trigger which inserts an audit trail record to a different table, but this trigger functions perfectly (rows all inserted correctly)

In addition, the Update & Delete triggers (with the identical functionality) work perfectly & generate no errors within the VB code..... :-

I've seen this error in all sorts of contexts on forums, but nothing as yet to help

TI

 

[Pieter Wijnen]

There must be an error in the Insert (Update Trigger)
If the Returned text is actual, the aliasing looks wrong, You can't use
numerals
ie change "Select Column1 as 1.. " to "Select Column1 As C1.."
but that's where the error is

Pieter

 

[steve.ingle]

Hi Pieter,

Thanks for your post.

However, my trigger contains nothing like the SQL code shown in the error message!!!

 

[Pieter Wijnen]

Can you post the trigger? - Mind you I'm An Oracle DBA, not MSSQL

If Everything works as expected, Why not ignore the error?

ie

On Error Goto Err_handler

....

EX_Function:
Exit Function
Err_handler:
Select Case Err.Number
Case 7874:
Resume Next
Case Else
MsgBox "Error: " & Err.Description, vbInformation, "Error Code = " &
Err.Number
End Select
Resume EX_Function
Exit Function

Pieter

 

[steve.ingle]

Hi Piete

If Everything works as expected, Why not ignore the error

Yeah! I did just that. Got fed up spending so much time trying to track down the bug ;

Can you post the trigger? - Mind you I'm An Oracle DBA, not MSSQ OK:
-----------------------------------------------------------------------------------------------
CREATE TRIGGER AdultsAuditInser
ON dbo.adult
AFTER INSER
A
Declare @RowIdent in
Declare @UserIdent in
Declare @HostId nvarchar (32
Declare @HostName nvarchar (64
Declare @RowName nvarchar (64
SET NOCOUNT O

SET @HostId = HOST_ID(
SET @HostName = HOST_NAME(
SELECT @UserIdent = [log_UserID] FROM [Logon] WHERE [log_HostName] = @HostNam
SELECT @RowIdent = [AdultNo] FROM [Inserted
SELECT @RowName = [pAddressName] FROM [Inserted
INSERT INTO [Audit] ( [aud_Tablename], [aud_UserIdent], [aud_Operation],[aud_RowIdent], [aud_Rowname]
Values ( 'Adults', @UserIdent, 'INSERT', @RowIdent, @RowName
SET NOCOUNT OF

That's all!

TI

 

[Pieter Wijnen]

yeah, weird allthough not an MSSQL expert (DBA) it looks fine to me
so let's just ignore the error <g>

I do prefer PL/SQL though, might have to do with 20 years of experience <g>
(gawd, I,m getting old)

I do however have a sneaking suspicion that async operations play a part....

Pieter

Hi Pieter

If Everything works as expected, Why not ignore the error?

Yeah! I did just that. Got fed up spending so much time trying to track
down the bug

Can you post the trigger? - Mind you I'm An Oracle DBA, not MSSQL OK:-
------------------------------------------------------------------------------------------------
CREATE TRIGGER AdultsAuditInsert
ON dbo.adults
AFTER INSERT
AS
Declare @RowIdent int
Declare @UserIdent int
Declare @HostId nvarchar (32)
Declare @HostName nvarchar (64)
Declare @RowName nvarchar (64)
SET NOCOUNT ON

SET @HostId = HOST_ID()
SET @HostName = HOST_NAME()
SELECT @UserIdent = [log_UserID] FROM [Logon] WHERE [log_HostName] =
@HostName
SELECT @RowIdent = [AdultNo] FROM [Inserted]
SELECT @RowName = [pAddressName] FROM [Inserted]
INSERT INTO [Audit] ( [aud_Tablename], [aud_UserIdent],
[aud_Operation],[aud_RowIdent], [aud_Rowname] )
Values ( 'Adults', @UserIdent, 'INSERT', @RowIdent, @RowName )
SET NOCOUNT OFF

That's all!!

TIA

 

[steve.ingle]

Hi Pieter

I think you may be correct about async problems - looks like Access is clearing the record buffer after the insert trigger fires :-o

I had thought, maybe, some locel memory locations were being overwritten.

Thanks for your input


Steve