From: AVG on
I am posting this at the suggestion of Tony Toews in response to another of
my posts.

Access 2007 SP2 with SQL Server 2005 back end.

Datasheet form bound to an updatable view.

The view is based on multiple tables, but does have a single field primary
key.
Whether the PK is created in the view, or in Access, makes no difference.

The problem is on inserting a new record.
Like most forms, some fields are required and some are not.

If data is entered in all the required fields any (or none) of the
non-required fields, all works fine.

The problem is that if user changes their mind and deletes the data in a
non-required field prior to saving the record, the form displays #deleted in
each field of the new record after saving it.

The record, however, IS inserted and does appear in the form if it is
requeried.

This happens because of the way the Access retrieves a newly inserted record
from SQL Server.

If the view or table that a form is bound to contains an autonumber
(identity) field, Access will query SQL Server for the last inserted value
and then query SQL Server for the new record using PkField = IdentityValue,
in order to display it.

However, when a SQL Server view is based on more than one table, SQL Server
does not pass the identity property along with the view.

In that case, Access uses all of the entered field values in a where clause,
to request the new record.
E.G. Where field1 = value1 and Field2 = value2, etc. Only the fields where
data was entered is included.
While it does take a couple of round trips, this does work.

If the user made an entry in a field and then deleted it, Access still
includes that field in the where clause.
The problem is that it still uses the exact same construct.
Where field1 = value1 and Field2 = value2, etc.

Well, don't we all know that if value1 is NULL, we must use the construct,
'field1 IS NULL' and not 'field1 = NULL'?
Apparently, Access doesn't know that!

So, since no record is returned with that query, Access displays #deleted.

I have an open case with Microsoft for over three months now.
They have duplicated the problem and they don't have a solution or
workaround, other than a requery, which would not be acceptable to the
client.

Fixing a bug in Access or even creating a hotfix is a long-term project for
Microsoft and they haven't even decided if they will do either.

Of course, another solution would be if there were a way to get SQL Server
to pass the identity property through the view, so that Access would use
that, but from all of my research, it seems that there is no way to do that.

I had suggested that the Access team discuss it with the SQL Server team and
when I pressed the issue, was told (by a supervisor) that they had no one
in-house that knew enough of both Access and SQL Server to know how to
present it to the SQL Server team (not exact words).

So, anyone have any suggestions?

--

AG
Email: npATadhdataDOTcom




From: Banana on
On 6/13/10 1:41 PM, AVG wrote:
> So, anyone have any suggestions?

Considering that you've had an open case with MS, some of suggestions
may already been tried but I want to make sure we covered all bases so
we know what has been already tried and failed (and why).

It has been said before that to avoid field-by-field comparsion, one
would add a RowVersion column to the table (in older SQL Server, that's
misleadingly known as Timestamp, which is not in fact a timestamp).
Access will then compare only the RowVersion and thus avoid the problem
of losing the pointer to the changed entry. Did you try that?

If it did not address the problem, can you make use of SCOPE_IDENTITY()?
You said there was no way of getting the ID back from SQL Server, which
is strange as SCOPE_IDENTITY() should do that for you, but maybe I'm not
fully understanding the problem here.

Thirdly, you say the problem occurs when the user changes an edit prior
to the saving the record. Do you have any VBA code that saves the record
behind the scene or could it be in partial state? I would expect that
SQL Server would never receive the edited rows until it was saved by
Access/your VBA code and thus that issue should not occur.

Finally, if all of above does not apply/won't fix your problem, the
alternative to look at is to use ADO recordset and bind it to the form,
set the form's UniqueTable property and see if that help Access focus on
only one table. If that does not even work, then you may need to take a
step further and manipulate the ReSyncCommand property so it look at
only the identity column. However, I've yet to try that.

HTH.
From: Tony Toews on
On Sun, 13 Jun 2010 16:41:53 -0400, "AVG"
<NOSPAMagiamb(a)newsgroup.nospam> wrote:

>Datasheet form bound to an updatable view.

Banana has some fine suggestions.

Dumb question. Does the same thing happen if you use a continuous
form rather than a datasheet form?

Tony
From: AVG on
Thanks for the quick reply Tony.
Hadn't thought of that, so just tried it - yes, the same thing happens.
I'll address Banana's comments next.
--

AG
Email: npATadhdataDOTcom


"Tony Toews" <ttoews(a)telusplanet.net> wrote in message
news:8qpa1656phii0dju8sdve4j3cj2b4uikf7(a)4ax.com...
> On Sun, 13 Jun 2010 16:41:53 -0400, "AVG"
> <NOSPAMagiamb(a)newsgroup.nospam> wrote:
>
>>Datasheet form bound to an updatable view.
>
> Banana has some fine suggestions.
>
> Dumb question. Does the same thing happen if you use a continuous
> form rather than a datasheet form?
>
> Tony



From: AVG on
Banana,

Thanks for the quick reply.

MS hasn't told me everything that they have tried, only that they have tried
everything the tech and escalation team could think of.

They only had two suggestions:
1. Requery.
2. Change all non-required numeric fields to text and don't allow nulls.
That is totally impractical. This is a very large project with many tables
and relationships that is 90% done. That would add a great deal of time and
cost to the project. Neither of which would be acceptable to the client.

Regarding RowVersion -
The problem is only on inserting a new record, not updating and existing
record, which works fine.

The view that the form is bound to consists of at least two joined tables.
Each does have a Timestamp column.
I have tried including and excluding each Timestamp with no change in
results.

For brevity, I did leave out the fact that I collected all of my info by
monitoring SQL Profiler and that prior to Access requesting the new record
by using all of the entered fields, it first requests the record by 'PKfield
IS NULL' - something else we all know won't return a record. That wasted
round trip (and another), I actually discovered two years ago and had
another case with MS - they would not address it unless I could prove to
them that the wasted trips was crippling the network - which, of course, I
could not do.

SCOPE_IDENTITY() -
Since this is a bound form, I don't see how I could do that.
Unless, I used a local table and constantly synchronized it with SQL Server.
Again, a good bit of work as there are several places in the system using
forms bound to views.

VBA code, etc. -
The code behind, does validation, etc. prior to the record being saved and
does not perform any specific 'save'. That is done by Access when user tabs
or moves to another record, etc.
If a user never enters any keystroke in a non-required field, Access does
not include that field when it queries SQL Server for the inserted record.
It only includes fields where data is entered. Apparently, if data is
entered in a field and then deleted prior to saving, Access still includes
that field in it's query.

ADO -
I should have mentioned that the project is an accdb using ODBC, not an adp.
Please correct if I am mistaken, but isn't binding to an ADO recordset
limited to adp's.
I wasn't familiar with the ReSyncCommand and UniqueTable, so just did a
search and they seem to apply to adp's also.
If a form can be bound to an ADO recordset in an accdb, can you point me to
an example?

--

AG
Email: npATadhdataDOTcom


"Banana" <Banana(a)Republic.com> wrote in message
news:4C15590A.6090507(a)Republic.com...
> On 6/13/10 1:41 PM, AVG wrote:
>> So, anyone have any suggestions?
>
> Considering that you've had an open case with MS, some of suggestions may
> already been tried but I want to make sure we covered all bases so we know
> what has been already tried and failed (and why).
>
> It has been said before that to avoid field-by-field comparsion, one would
> add a RowVersion column to the table (in older SQL Server, that's
> misleadingly known as Timestamp, which is not in fact a timestamp). Access
> will then compare only the RowVersion and thus avoid the problem of losing
> the pointer to the changed entry. Did you try that?
>
> If it did not address the problem, can you make use of SCOPE_IDENTITY()?
> You said there was no way of getting the ID back from SQL Server, which is
> strange as SCOPE_IDENTITY() should do that for you, but maybe I'm not
> fully understanding the problem here.
>
> Thirdly, you say the problem occurs when the user changes an edit prior to
> the saving the record. Do you have any VBA code that saves the record
> behind the scene or could it be in partial state? I would expect that SQL
> Server would never receive the edited rows until it was saved by
> Access/your VBA code and thus that issue should not occur.
>
> Finally, if all of above does not apply/won't fix your problem, the
> alternative to look at is to use ADO recordset and bind it to the form,
> set the form's UniqueTable property and see if that help Access focus on
> only one table. If that does not even work, then you may need to take a
> step further and manipulate the ReSyncCommand property so it look at only
> the identity column. However, I've yet to try that.
>
> HTH.