sql server and access

  • Thread starter Thread starter Filip
  • Start date Start date
F

Filip

Hi,

I've got an Access form with a view as record source.
When I change a value in a textbox, I've got the following
error in Access:

"The data was added to the database but the data won't be
displayed in the form because it doesn't satisfy the
criteria in the underlying record source."

When I do a refresh of the record source, the fields are
updates and perfectly shown in Access. I've also set a
Unique Table property.

What is the problem, because the view isn't that special,
just a few funtions and fields from different tables, but
only one table contain sdata that has to be changed.

Thanks

Filip
 
Hi,



By default, MS SQL Server do not display records that do not satisfy the criteria of the view,
which is a different than Jet. Sure, Jet behaviour is useful when it is time to "undo" manually
something to a record that has just ... slipped out of view in MS SQL Server. You want to reproduce
the same behaviour in MS SQL Server than what you have in Jet, in this matter? Funny you ask, if
you have a general answer, tell me.

First, WITH CHECK OPTION may allow you to AVOID setting a value to something that is not
qualified by the criteria. Hard hard, in general. An alternative is to mimic a keyset: get the set
of the primary key of the records to be displayed in a temp table (the primary key do not change,
generally), and build the criteria, for your VIEW, based on that:

WHERE myPk IN (SELECT pk FROM #pks )

So, as example, if you want prices of items that are less than 10.00$ to update them by 2%,,
without that trick, all items with a price of 9.81$ or more will slip out of view, after the update
(since their new price will be > 10.00$). But if you use a view based on the key value of the
items, the keys have not changed, and all your initial items would still be visible for further
"manual" modifications. Otherwise, if you use WITH CHECK OPTION, the update will fail if there is
an item price >= 9.81$, and if you do not have WITH CHECK OPTION, then the item with a unit price
= 9.81$ will be updated by an increase of 2%, but won't be available anymore to your end user,
Click to expand...
through the view with a WHERE UnitPrice<= 10.00.

Jet already uses a keyset approach behind the scene. It uses the key values of the records that
satisfy the WHERE clause at the moment the query is open, and keep that set of keys (plus those of
the records YOUR user append) as the record to be display further on, whatever happen to the fields
values implied or not in the initial WHERE clause.


Hoping it makes sense,
Vanderghast, Access MVP
 
Back
Top