Acces Project- insert trigger problem

  • Thread starter Thread starter VS Sreenivas
  • Start date Start date
V

VS Sreenivas

I am working on an Access Project.
The insert trigger for one table adds rows into a second
table.
When doing this from the Access UI, I get the message:-
'The Data was added to the data base but that data won't
be displayed in the form because it does not satisfy the
criteria in the underlying record source'.
The insert trigger, however, is successful in actually
adding the rows to the second table and this error
message comes only from the Access UI. When the same
insert is done from Query Analyser, I do not get the
message.
 
What's happening is that your form is set to only show records in the table
which meet certain criteria, and the row you're inserting doesn't meet those
criteria. For instance, perhaps you have a filter set so that it only
returns records where a particular field is greater than some value, and the
new row you're inserting has a value less that that threshhold value for
that field.
 
Access seems to have some strange bugs when it come to ADP and, in this
regard, 2000 version is particularly worse than the 2002 version.

Some of the following suggestions might seem to have no sense, but they work
for me :

1- Delete any timestamp field. Access has often trouble with them.

2- Make sure that you have defined the "Unique Table" property. If you
can't do it in design view, then define it in the Open event of the form.

3- Create a Resync Command.

(Remember that a Resync Command is a procedure, not a function,
therefore they don't take parenthesis. So you must write something like
ResyncCmd ?, ?, ? and not ResyncCmd (?,?,?) )

4- If the previous can't work, then try with a Bad Resync command, that
is, any resync command that doesn't have the right number of arguments. The
right number is the number of field in the primary key for the unique table,
so you only have to take any stored procedure which has a greater number of
arguments. This will force Access to use the ADODB default resync command,
which is the same as simply displaying the values just entered.

Remember that a wrong Resync Command has not the same effect than no
Resync command at all. In this last case, the default Acess Resync Command,
not the ADODB one, is called.

In your specific case, I usually begin with the number #4.

S. L.
 
Oups, I'm sorry but I think that I've made a mistake in my previous answer.

After reading your message again, I've just noticed that you use an insert
trigger; so I'm wrong in telling that it is possible to correct the error
msg you see with one of my suggestion as I don't use insert triggers anymore
since a long time.

The insert trigger has for effect of modifying the @@identity value.
SLQ-Server has a another function to correct for this but it seems that
Access doesn't know it, hence your error message. Maybe Access 2003 has
this problem corrected.

S. L.
 
No. I am not yet into useing forms.
I am currently doing all the business logic in MSDE
tables, using T SQL. I have not yet started the UI part.
I do my work using both Access and Query Analyser,
exploiting their relative strengths.
What I meant by Access UI was that when I try check the
business logic through Access Project tables by adding
the requisite rows,I get the error message specified
earlier.
On the other hand, when I make the same insert in Query
Analyser, there is no error.
The problem seems to be the Access's own UI. After the
erorr message, all the current rows show '#deleted' and
when I press F9, the last inserted row is reflected.
 
Thanks. But the tricks suggested haven't worked.
I have recently shifted from years of Access development
to SQL Server.
As of now, inolved in a small project. I am doing most of
the business logic through T SQL.
Your remark that you have stopped using triggers worries
me- Is T SQL outdated? What is the alternative?
 
Hi,
Your remark that you have stopped using triggers worries
me- Is T SQL outdated? What is the alternative?
Click to expand...

reading that messages from outside, i really think that

could / should be read as

And @@identity is not any longer needed (outdated) as well:

here a sample stored procedure which returns an ID



CREATE PROC sp_InsertMyRecord
@ProjectName varchar(255),
@ProjectLeiter varchar(255)
AS
SET NOCOUNT ON
SET ROWCOUNT 1

-- Author: Uwe Ricken
DECLARE @RetValue int

INSERT INTO dbo.tProjekte
(ProjektName, ProjektLeiter)
VALUES
(ProjectName, ProjectLeiter)

-- ---------------------------------------------------
-- use either SET with @@IDENTITY (SQL Server 7)
SET @RetValue = @@IDENTTITY -- SQL 6.5/7.0

-- or SCOPE_IDENTITY() on SQL Server 2000
SET @RetValue = SCOPE_IDENTITY() -- SQL 2000
-- ---------------------------------------------------

SET ROWCOUNT 0
SET NOCOUNT OFF

RETURN ISNULL(@RetValue, 0)
GO
 
Sorry, I didn't understand that by Access UI, you didn't mean Access Forms.

The triggers by themselves are not outdated. Simply I think that usings
theses and others kinds triggers add a whole new layer of complexity by
hidden some of the most important pieces of code from the casual reading of
the code of stored procedures.

I mean that when you try to understand some piece of code working on a table
and that this table as one or many triggers, you aren't able to easily see
the whole picture and you must begin to make numerous clicks to see what's
happening.

You will find many people that will tell you that using triggers will
simplify your code and make it faster. Well, I have many books here on
SQL-Server performance - some of them from Microsoft - and I have never
found a single piece of text that was stating that triggers was faster in
doing something than equivalent explicit transact statements.

As to the simplicity, well, try to understand a big piece of code that have
been written by someone than yourself and who relies heavily on triggers and
you will quickly understand what I mean (and that database doesn't have to
be constitued of hundreds of tables). And don't forget that if this is
professional coding, then your code will be read by someone else in the
futur. It's funny to think that you will impress some guy with your
technical ability by writing a lot of triggers; but it will be much less
funny when this guy will be you.

One possible exception could be when these triggers are used to maintain an
historical version of the database, if and only if it is possible to
understand the relevant stored procedures in the primary version without
having to refer to this second version. But whis this statement, I mean
truly independant versions, when you could easily delete the second without
interfering with the first in any way.

Personally, I have ceased to write triggers a long time ago and many of my
database coding problems had gone with them too. This is my personnal
opinion; make whatever you want with it, it's only up to you on how you
decide to work.

S. L.
 
a> Thanks. But the tricks suggested haven't worked.

1. Access 2000 or 2002?

2. Please post the definition of the involved tables and the trigger.

3. Are you sure the 2nd table (the one where the trigger is inserting the
record) does not have yet another trigger, that in turn would modify the 1st
table?

a> Your remark that you have stopped using triggers
a> worries me- Is T SQL outdated?

T-SQL can't be outdated, since it's the native language of SQL Server. In
fact, it is being extended in the next release of it.

You want to use triggers when you (1) want instant action in the database
(contrary, for instance, to a deferred batch); (2) don't want to tie that
action to a particular application. The trigger will work regardless of the
reason that caused the change of the data. Today you are working on one
project; if you succeed; soon there will be the 2nd one, working with the
same data. Then you will either have to duplicate the code (and then support
two instances of it), or have one trigger.


Vadim
 
Thanks for the clarification.

I have moved to SQL Server after years of Acces
development.
I am implementing all business logic through triggers and
procedures.
Hence the worry when a MVP says that he does not use
triggers.

I am using Access 2002.
I will restate my problem.

Working on a small project.
Presently working only on the tables.
Doing most of business logic through T SQL.
Not yet developed forms or other UI.

In the Project,
The insert trigger for one table adds rows into a second
table.
(No further triggers. That has been checked).
When I try to check the functioning of the trigger by
adding a row in the table from Access,
I get the message:-
'The Data was added to the data base but that data won't
be displayed in the form because it does not satisfy the
criteria in the underlying record source'.
The insert trigger, however, is successful in actually
adding the rows to the second table and this error
message comes only from the table in Access.
When the same row is added from Query Analyser, or
through a stored procedure, I do not get the message.
Tried trapping the error through Form.Error.
DataErr returned is 30014.
However, not useful.
All columns in the row reflect '#deleted' .
When I press F9, the last entered data is reflected.
Pl remember that there is no problem with the T SQL as
the problem comes only from Access.
 
VS> I will restate my problem.

actually, there was no need to restate the same information once again. What
you see is not usual, I tried a trivial sample, inserted a record in a table
that had a trigger that inserted a record into another table. Access behaved
correctly. So it must be something unusual in your situation - that's why I
asked to put here the definitions of the tables and of the trigger. You can
get them by generating scripts in query analyzer ("script object to new
window" - create table) or in enterprise manager.

An idea of what might be causing the effect: don't you apply a filter to the
first table?


Vadim
 
Oh, I'm not a MVP. Sorry for the misunderstanding.

I've never said that triggers was outdated but simply that personally I
found much easier doing my work by using only stored procedures, without
triggers. This is only a personnal appreciation, based on my personal
experience; nothing else.

So you don't have to have any worry.

S. L.
 
VS> Hence the worry when a MVP says that he does not
VS> use triggers.

"Outdated" does not automatically mean "bad". Rather, the contrary. In the
older times, the standards of programming were often higher than they are
today, so you are more likely to find a higher quality code in the old
applications than in the new.

Here's an example. Initially, the macro language of Microsoft Word was
WordBasic. Then, moving towards the common language for all applications,
Microsoft made VBA the language of choise. However, the "outdated" WordBasic
code still executed many, many times faster than the equivalent VBA code -
until Microsoft eliminated support of direct WordBasic execution in Word.

Vadim
 
Thanks to all who have responded to my problem.
Based on all suggestions, I started to
progresively eliminate various causes
and arrived at the Identity property of the primary key
column of the table.
When I removed the identity (auto increment) property,
the error message stopped.
When the coln was set back to auto increment, the error
had returned.
Apprently there is mismatch between Access and MSDE wrt
the auto number field.
 
Back
Top