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

On 6/13/10 1:41 PM, AVG wrote:
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.

On Sun, 13 Jun 2010 16:41:53 -0400, "AVG"
<NOSPAMagiamb (AT) newsgroup (DOT) nospam> wrote:

Banana has some fine suggestions.

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

Tony

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 (AT) telusplanet (DOT) net> wrote

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 (AT) Republic (DOT) com> wrote

On 6/13/10 6:08 PM, AVG wrote:
I did ascribe as such in your original post.

That is really tangential but I want to say I can't remember seeing that
before - typically, it asks for primary key to use for scrolling then
does lazy fetching thereafter. Are you saying it's sending a IS NULL
when user enters a new record?

Okay, I wasn't thinking clearly on how you were working with and can see
why it won't have helped. Getting the ID would still need you to requery.

I also assume you've tried Me.Refresh to see if it clear #Deleted, yes?

Gotcha. I think that is the key to the puzzle. I assume that the INSERT
INTO statement put in the NULL value, yes? I'm under the impression
those fields are numeric and thus we can't just cheat our way by passing
in a ZLS. Do you have any values that you can consider to be invalid or
at least used in lieu of NULL? If that is the case, an option is to set
all non-required fields to the default value and thus work around that
problem. Is that an option for you?

No. You can use and in fact, mix ADO with DAO in a *db file. I typically
use 95% DAO and 5% ADO - ADO can be quite helpful in exceptional case
where DAO/ODBC doesn't satisfy the requirement.

Hmm. Unfortunately, the documentation are confusing on that point. Yes,
it does seem to imply that it's a ADP-only feature but I can assure you,
having used UniqueTable to great success in a *DB file, it's not. The
only thing about those two properties is that it only works with an ADO
recordset even though they're available when using DAO recordset (will
throw an error obviously).

For binding ADO to a Access form, take a look and see if this get you
started:

http://support.microsoft.com/kb/281998/en-us

With UniqueTable, it's straightforward. After using the above KB article
to guide you for the binding, after the line "Set Me.Recordset = <ADODB
recordset>", put in this line:

Me.UniqueTable = "<name of table that you are actually updating>"

HTH.

Answers and one question below.

--

AG
Email: npATadhdataDOTcom


"Banana" <Banana (AT) Republic (DOT) com> wrote

Yes, after the 'insert', Access requests the new record using PkField IS
NULL as the where clause.
When nothing is returned, it then resorts to using all of the fields that
had anything entered in them, even if the entry was deleted prior to the
'insert'.

Yes, even though I did not expect it to work, I tried it and no luck.

Some fields are numeric and some text.
Using ZLS was one of the suggestions from MS.
They suggested changing all non-required numeric fields to text and don't
allow nulls.
It is totally impractical. This is a very large project (2 years with 10
months on hold) with many tables and relationships that is 90% done. There
are many places in the system where searches, concatenations, 'can shrink'
(in reports) depend on nulls that would all need to be changed. At last
check, the current production version had over 60,000 (not including
comments) lines of code. It would add a great deal of time and cost to the
project. Neither of which would be acceptable to the client.

This looks promising. Don't know if I can get to it today, but will
definitely try it.

The view uses 'INSTEAD OF' triggers for insert, update and delete, however,
the PK is an identity column in one of the tables.
Just how does Access utilize UniqueTable? Would it still be applicable?

On 6/14/10 6:56 AM, AVG wrote:
Okay. I'll need to test that out and see how it works out. Thanks for
that piece of information.

I certainly can understand that. I wouldn't dare to suggest that you
change the data type and will assume that there are no good
"placeholder" to substitute for nulls for those non-text columns.

Now that was a crucial piece of information. I typically don't work with
triggers but have seen other report problems with using triggers,
especially INSTEAD OF ones, which could interfere with Access'
operations. In such context, it's usually better to work in disconnected
fashion, obtaining the new ID via SCOPE_IDENTITY() or via a return value
of a stored procedure. That typically is problematic when you want a
live display of the data, though. Let's see if ADO will meet your needs,
though.

Basically, if you have a source that joins more than one table and that
causes the query to be non-updatable because we can't uniquely identify
all components back to their originating source, but if we only need to
edit one table, we can set the UniqueTable property (which is actually a
shortcut into ADO recordset's property of a similar name) that instructs
Access/ADO that only one table needs to be updatable and the rest are
just for display.

I was not clear whether your view allow updates to both tables and if
that is the case, then I am inclined to think we need to look at the
ResyncCommand which is where we basically change how Access/ADO updates
the local cache after it issues an update (be it DELETE/UPDATE or
INSERT) to the source. Again, I've yet had the need to do that before
but if the theory holds, you can use ResyncCommand to specify say, a
stored procedure instead of a plain INSERT INTO statement that will then
return the new PK after the trigger has fired so there's no confusion
WRT finding the record. But that's all in theory and I may be off here.
Or, we may get lucky and merely changing to ADO recordset is all we need
to work around the problem of losing the pointer to the new records due
to mismatch in what data it should contain.

HTH.

On Sun, 13 Jun 2010 20:00:37 -0400, "AVG"
<NOSPAMagiamb (AT) newsgroup (DOT) nospam> wrote:

Darn, worth a try tough.

Sounds like Banana has some useful ideas.

Tony

"AVG" <NOSPAMagiamb (AT) newsgroup (DOT) nospam> wrote in
news:4c154288$0$22523$607ed4bc (AT) cv (DOT) net:

I don't believe in making datasheet/continuous forms editable, as a
general rule. There are a few exceptions (e.g., single-combobox
subforms for creating many-to-many joins, invoice details), but in
general I use a read-only list view and an editable single form,
with the link master of the detail form being the PK of the list
form.

I've sometimes done the detail unbound, as well, though that's a lot
more complicated, and not often justified.

In general, though, I don't use multi-table recordsources in
production apps. I'll often do them in quick-and-dirty data cleanup
forms that I create for my own use, but I don't think they are a
valid interface for end users.

So, basically, while what you are doing *should* work, I generally
would never encounter it because what you are doing violates what I
consider some basic principles for application interface design.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/