DataBase - Access 2007 bug with SQL Server Back End

Asked By AVG

13-Jun-10 04:41 PM

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, do not we all know that if value1 is NULL, we must use the construct,
'field1 IS NULL' and not 'field1 = NULL'?
Apparently, Access does not 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 do not 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 have not 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

13-Jun-10 06:17 PM

Considering that you have 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 is
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 am 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/will not 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 have yet to try that.

HTH.

13-Jun-10 07:19 PM

Banana has some fine suggestions.

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

Tony

13-Jun-10 08:00 PM

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

AG
Email: npATadhdataDOTcom

13-Jun-10 09:08 PM

Banana,

Thanks for the quick reply.

MS has not 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 do not 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 will not 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 do not 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 is query.

ADO -
I should have mentioned that the project is an accdb using ODBC, not an adp.
Please correct if I am mistaken, but is not binding to an ADO recordset
limited to adp's.
I was not 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

13-Jun-10 09:35 PM

I did ascribe as such in your original post.


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


Okay, I was not thinking clearly on how you were working with and can see
why it will not have helped. Getting the ID would still need you to requery.

I also assume you have 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 am under the impression
those fields are numeric and thus we cannot 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 does not satisfy the requirement.


Hmm. Unfortunately, the documentation are confusing on that point. Yes,
it does seem to imply that it is a ADP-only feature but I can assure you,
having used UniqueTable to great success in a *DB file, it is not. The
only thing about those two properties is that it only works with an ADO
recordset even though they are 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 is 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.

14-Jun-10 09:56 AM

Answers and one question below.

--

AG
Email: npATadhdataDOTcom



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 do not
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.

14-Jun-10 11:02 AM

Okay. I will need to test that out and see how it works out. Thanks for
that piece of information.


I certainly can understand that. I would not dare to suggest that you
change the data type and will assume that there are no good


Now that was a crucial piece of information. I typically do not 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 is 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 cannot 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 have 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 is no confusion
WRT finding the record. But that is 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.

14-Jun-10 04:46 PM

Darn, worth a try tough.


Sounds like Banana has some useful ideas.

Tony

14-Jun-10 11:17 PM

I do not 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 have sometimes done the detail unbound, as well, though that is a lot
more complicated, and not often justified.

In general, though, I do not use multi-table recordsources in
production apps. I will often do them in quick-and-dirty data cleanup
forms that I create for my own use, but I do not 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/

14-Jun-10 11:22 PM

I think that this is the source of the problem. Or, more
specifically, I would say that expecting this to behave the same way
with an ODBC back end as it does with a Jet/ACE back end is way too
much to ask of Jet and ODBC. In fact, as I said in my other post, I
only seldom make datasheets/continuous forms editable, and seldom
have more than one table in an editable recordsource.

If the problem is only with inserts, then disallow additions and use
a different form to do the adds.

I just question the utility of a datasheet form for editing to being
with, but for multiple tables, it is a real red flag to me that
there is a design error.

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

14-Jun-10 09:33 PM

David,

Thanks for your posts. I would agree with you in many instances, but not
this one. This is a major client, a datasheet is appropriate for their
business and is what they want. Also, the same problem would exist even for
a single form. There is a lot of data entry involved and if they had to deal
with each involved table using separate forms, their operation would slow to
a crawl.

Microsoft's own technical article
http://msdn.microsoft.com/en-us/library/bb188204.aspx, recommends using
views with 'instead of' triggers.
While it may not be optimal, it IS SUPPOSED to work. And it DOES work,
except when the user changes their mind and deletes the data in a field
prior to the insert. The problem is due to an acknowledged bug that is left
to us to find a workaround. I am hoping to try Banana's suggestion of using
an ADO recordset within a day or so. That at least sounds like it has
promise and is more than I got from Microsoft.

--

AG
Email: npATadhdataDOTcom

15-Jun-10 03:06 PM

I was able to try the ADO solution.
The form was not updatable at all until I set the UniqueTable property.
I could then perform updates. However, apparently ADO has a long reach.
The trace showed that the view itself was not being updated, but the
individual source tables.
That bypasses my triggers, so will not work for this application.
When I tried to insert a row, the error generated was 'Invalid input
parameter values. Check the status values for detail'.

I set the UniqueTable property to the name of the table that generates the
PK,
and set the ResyncCommand to 'SELECT * FROM myview WHERE PkField =
SCOPE_IDENTITY().

Don't know where the error is actually generated from, but must be
completely within Access. I tried to catch it in the form_error event, but
it does not appear there.

The trace only shows SELECT ((1)), so it is not even passing anything to SQL
Server.

As for which tables are updatable, the view consists of 10 tables, only
three of which get updates or inserts.
The triggers are necessary because, one of the three tables should not be
updated. Where it appears to the user that they are changing a value, the
trigger either substitutes a different record or inserts a new one.
The other tables are necessary for display of related data and to be able to
utilize custom sorting in (and from) other processes, like reports.

--

AG
Email: npATadhdataDOTcom

15-Jun-10 07:22 PM

Separate forms? Who suggested that? Ever heard of subforms?


I still think that adds in a form bound to a multi-table
recordsource is asking for trouble.

I think you have a design error and are unwilling to revisit the
design and are stuck with this bug unless you revisit the basic
architecture.

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

15-Jun-10 07:23 PM

Yes, this is a long-standing known issue with ADO, in that it tries
to be too smart, and tries to update the underlying tables even when
your view is not updatable. This is a huge error on the part of the
people who designed ADO, and is, I think, one of the many reasons
why ADPs/ADO are deprecated by Microsoft.

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

15-Jun-10 03:48 PM

Crikey. That's just not right. I am assuming your form is bound to
something like "SELECT ... FROM myView ...", right? Can you post a trace
of what SQL ADO passes back? Come to think of it, how could ADO know the
source tables... Is it also updating other tables that was not specified
in UniqueTable property?

Also, it could be a driver-specific issue - does this persist if you
select different driver/provider? (e.g. SQL Server instead of SQL Native
Client for example -- even try MSDASQL)


Hmm, I think we are supposed to give it a parameter so it should be
actually "WHERE PkField = ?". Have a look at this page, especially the
second part as that may give you more control:

http://msdn.microsoft.com/en-us/library/ms676094(VS.85).aspx

Another article discussing same concept:
http://support.microsoft.com/kb/251021


For testing purposes, it is sometime desirable to try to do the same
thing you would  have done via a form in the VBA. That way you can then
examine the ADO's (or DAO's) Errors collection and thus get more
specific error messages/information. Does it give you any more information?

BTW, I am not clear if you were able to insert anything with first
attempts (without ReSyncCommand, etc) or just when you did the
SCOPE_IDENTITY() thingy.


Yeah, that is similar to my case where I used an ADO recordset because we
had five tables joined and used for user-directed sorting/filtering and
UniqueTable + ADO recordset was the perfect solution, though my case is
much simpler because we only needed to update one table and not a view
with a INSTEAD OF trigger.

15-Jun-10 05:34 PM

Good info, thanks.

--

AG
Email: npATadhdataDOTcom

15-Jun-10 08:49 PM

I should be able to try your suggestions tomorrow. In the meantime, have you
seen David Fenton's post?

--

AG
Email: npATadhdataDOTcom

16-Jun-10 09:41 AM

As indicated before, I have been lucky to not experience ADO's long reach
only because when I do use ADO, it is typically directly with a table,
except for one case where I used table-valued function but even that, we
were updating just one table. I continue to follow similar rules that
determines updatability and a large part of that is to update only one
table via bound forms. If I wanted to update several tables, I am more
inclined to want to use a single unbound form and execute a stored
procedure instead. Of course, if the client does not want a separate form
for inserting vs editing, then we will have to deal with that somehow.

If he is also correct that it is a ADO flaw, then my suggestion of using
different providers would not work since it is on ADO layer, rather than
the provider's layer. One thing about ADO is that a lot of behavior is
influenced by the provider so for that reason, I have fell in habit of
checking what a provider does and examining its dynamic properties. The
extra flexibility that ADO provides is also its bane, I think. At least,
we have a choice and can choose accordingly to match the requirement at
hand.

HTH.

16-Jun-10 08:08 PM

This is a known issue with ADO, most often encountered when you
design your app so that users have no permissions on the base tables
and use views to provide access. If you write a DML statement using
the views, and it is not updatabase, ADO will try to do the updates
on the underlying tables, bypassing all the security. It will fail,
of course, since the user does not have the permission on the base
tables.

This is one of the many problems with ADPs (which depend on ADO)
that causes Steve Jorgensen, for one, to conclude they were simply
not usable in a production app.

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

16-Jun-10 08:12 PM

If I am not mistaken, in this case, it is multiple tables in the
recordsource, but only one is being edited (and, I presume, having
records added to it).

I have always been frustrated trying to use a single form of any
kind for both adding and editing, and that is why in most of my apps,
adding is done through a separate unbound form, then the editing
form is requeried and then I navigate via bookmark navigation to
thenewly-added record. This keeps the two processes separate. It
also makes it harder for the user to accidentally add a record, as
well as making it easier to abandon a record. The unbound form
collects the fields that are required to create the record (and if
not strictly-speaking required in the table definition, at least the
fields that are necessary to have a usable, identifiable record).

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

16-Jun-10 04:16 PM

Yes, the ADO source is 'select * from myview where ...'.
Yes, it is updating tables not specified in the UniqueTable property.
Trying different providers:
SQL Native Client - form is not updatable.
SQL Server - error, class not registered.
MSDASQL - error, Data source name not found and no default driver specified.

The construct of the view can be easily queried from SQL Server, although I
do not see it in the trace.

Following is the trace when the form opens. Note, the extra selects are for
combo box rowsources.

SET NO_BROWSETABLE ON
go
SELECT vwPreappraisal.* FROM vwPreappraisal WHERE
((([vwPreappraisal].[ContactID])=6992) And (([vwPreappraisal].[ModeID])=11)
And (([vwPreappraisal].[BatID])=5783)) ORDER BY [RegionCode], [WineName],
[Designation], [Vintage], [Producer], [SortOrder]
go
set implicit_transactions off
go
SELECT "dbo"."tblMstWineNames"."WineNameID"
,"dbo"."tblMstWineNames"."WineName" ,"dbo"."tblMstWineNames"."RegionID"
,"dbo"."tblMstWineNames"."WineAppelID" ,"dbo"."tblMstRegions"."RegionCode"
FROM {oj "dbo"."tblMstWineNames" LEFT OUTER JOIN "dbo"."tblMstRegions" ON
("dbo"."tblMstWineNames"."RegionID" = "dbo"."tblMstRegions"."RegionID" ) }
ORDER BY "dbo"."tblMstWineNames"."WineName"
,"dbo"."tblMstRegions"."RegionCode"
go
SELECT "WineNameID" ,"WineName" ,"RegionID" ,"WineAppelID"  FROM
go
SELECT "DesignationID" ,"Designation"  FROM "dbo"."tblMstDesignations" WHERE
("DesignationID" > 0 ) ORDER BY "dbo"."tblMstDesignations"."Designation"
go
SELECT "dbo"."tblMstBottleSizes"."SizeID"
,"dbo"."tblMstBottleSizes"."BottleName"
,"dbo"."tblMstBottleSizes"."RegionId" ,"dbo"."tblMstRegions"."RegionCode"
FROM {oj "dbo"."tblMstBottleSizes" LEFT OUTER JOIN "dbo"."tblMstRegions" ON
("dbo"."tblMstBottleSizes"."RegionId" = "dbo"."tblMstRegions"."RegionID" ) }
ORDER BY "dbo"."tblMstBottleSizes"."BottleName"
,"dbo"."tblMstBottleSizes"."RegionId"
go
SELECT "SizeID" ,"BottleName" ,"RegionId" ,"SortOrder" ,"IsLargeFormat"
,"BottleCode"  FROM "dbo"."tblMstBottleSizes"
go
SELECT "ProducerID" ,"Producer"  FROM "dbo"."tblMstProducers" WHERE
("ProducerID" > 0 ) ORDER BY "dbo"."tblMstProducers"."Producer"
go
SELECT "VintageId" ,"Vintage"  FROM "dbo"."tblMstVintages" ORDER BY
go
SELECT "VintageId" ,"Vintage"  FROM "dbo"."tblMstVintages"
go


Following is trace on an update:

exec sp_executesql N'UPDATE "InsertTest"."dbo"."tblMstItems" SET
varchar(1),@P9 int',108,1251,0,0,0,1,113,'N',3242949
go
exec sp_executesql N'SELECT
FROM "InsertTest"."dbo"."tblWineHistory" WHERE "WHID"=@P1',N'@P1
int',3666809
go
exec sp_executesql N'SELECT "DesignationID" FROM
int',0
go
exec sp_executesql N'SELECT "ProducerID" FROM
go
exec sp_executesql N'SELECT "AppellationID" FROM
int',0
go
exec sp_executesql N'SELECT
FROM "InsertTest"."dbo"."tblWineLots" WHERE "WLID"=@P1',N'@P1 int',5245783
go
exec sp_executesql N'SELECT "RegionCode","RegionDescription","RegionID" FROM
go
exec sp_executesql N'SELECT "WineName","RegionID","WineNameID" FROM
go
exec sp_executesql N'SELECT "Vintage","VintageId" FROM
go
exec sp_executesql N'SELECT "BottleName","SortOrder","BottleCode","SizeID"
FROM "InsertTest"."dbo"."tblMstBottleSizes" WHERE "SizeID"=@P1',N'@P1 int',1
go
exec sp_executesql N'SELECT

16-Jun-10 05:20 PM

Actually, AVG said 3 tables are being updated/inserted via a INSTEAD OF
trigger. He was following a MSDN article by Andy Baron that recommended
this very thing for Access.


Certainly understandable, and it does make things much more
straightforward. I have used similar technique but there are also places
where having a subform that does both editing/adding is more efficient
in terms of data entry than forcing a separate form. As long the client
understand the inherent complexity, tradeoffs and have been told of all
possible alternatives, I am not going to argue with the client in area of
what UI/data entry process they want to have. (designing a
database/tables/relationship is entirely another matter, though)

16-Jun-10 05:26 PM

Thanks. Better that I know about it by now. I also have to say that is
pretty stupid behavior.


I do not know who Steve Jorgensen is, other than that he used to post
here long, long ago. But I am not surprised that ADP were problematic,
and ADO does have its baggage. This is why I tend to stick to DAO/ODBC
first and only turn to ADO if it can be used to solve a specific problem
(and has done with good results). Until Access team decide to give us a
replacement for ADO (specifically the ability to have disconnected
recordsets, asynchronous operations, along few other goodies) that is
what we have to work with.

16-Jun-10 05:54 PM

RE: MSDASQL, I think it requires a bit more tweaking to get it to work -
I cannot remember off the hand but I remember I could not just change from
NCLI to MSDASQL and use same SQL statement or something like that. But
eh, forget it.


Good point. I suppose it could be done and may have been done using a
low level call or does not show up in trace because it does not match the
trace's criteria. (e.g. not a BatchCompleted statement for example)


That's true though I was thinking it still has to resync anyway after
getting the PK.


I would have had expected there to be a configuration option to suppress
ADO's silly long-reaching but if there were, it'd have been known by now.

The fact remains that you are still stuck with no good way to insert new
records without getting errors, be it #Deleted in DAO/ODBC or Invalid
Input Parameter in ADO/OLEDB. David already has mentioned using an
unbound form and keeping the addition/edit process separate. I think you
already indicated that this is not an acceptable option for you. The
alternatives are:

1) Use a local temporary table that mirrors the view structure and
commit the edits/inserts as a batch rather than one-by-one live. That
way you can then use VBA and thus leverage the server-side processing to
handle your data correctly while having the appearance of a bound form.
The trade off is that the changes are not live and your users would have
to commit them at a certain point of time. If you can live with few
minutes worth of delay then that should not be a big deal. If client is
willing to pay for extra programming effort, you could shrink that
window by doing one-by-one update behind the scene.

2) I have seen but never used the idea of dynamically binding/un-binding
form. The idea seems to me more hassles than benefits.

3) Use Form Footer section to contain the unbound fields for inserting
new records. The problem is that you have to requery to get it to show
up once committed, and DAO does not support dynamic cursors (well, it
could via ODBCDirect but I fear that is even worse alternative than ADO
and it is deprecated as of 2007 anyway). At least the editing would stay
live and they could then insert more than one rows without problems but
need to requery to see those new rows.

I realize those are not the ideal solutions but that is basically the
cards we have been dealt. Maybe one of those will be able to meet your
client's requirement?

HTH.

16-Jun-10 09:12 PM

Thanks for all of your help.
I will discuss the options with client. My guess is that he will try living
with #deleted and see how often it happens.
I think the next best option is the local temporary table. I already use
that method in another of his apps.
A separate unbound form is definitely out of the question.
I had the opportunity today to do a remote session with him and one of his
best data entry persons. I could not even completely follow all they were
doing, it all happened so fast - including looking up some info on the
internet.
If I did anything to noticeably slow them down, there would be a mob with
torches at my door :).

Yes, the structure is much more normalized than the current system, and a
bit more than I would like. Part of that is their desire for better tracking
and part is dictated by a planned sharing of data with Great Plains.

--

AG
Email: npATadhdataDOTcom

18-Jun-10 03:35 AM

Steve is one of the brilliant Access gurus of the past. I do not know
what he ended up doing, but he really tried very, very hard to make
ADPs work.


That only works if you are *not* using an ADP!


I wonder what will happen in the next couple of versions of Access.
I really think they need to do something to serve the needs of SQL
Server users. ODBC is really long in the tooth and needs to be
replaced with something that is more current in terms of modern
database functionality.

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

18-Jun-10 03:36 AM

Uh, when the client starts telling me how to do my job, then I tend
to start looking for the exits.

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

18-Jun-10 03:39 AM

I am not at all convinced that adding in a separate unbound form
would slow down the process at all.

A client that insisted I implement a non-reliable solution would be
one I'd be unhappy to work for. it is my job to determine the best
solutions, not theirs, and if they insist on telling me how to do my
job, I am going to start looking for the exits.

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

18-Jun-10 08:10 AM

That's very noble, but sometimes one must look at the bigger picture and
also consider the ripple effect.
This particular issue is only one piece, of one large application, out of
several applications for a large, and very good client.

BTW, if I did not mention it before, thanks for all of your input.

--

AG
Email: npATadhdataDOTcom

18-Jun-10 08:14 AM

Aha, and thus the significance of quoting him. Thanks.


Okay, but nobody here is using ADP anyway.


1) Are you aware that Access team asked about Access 15 + SQL Server
integration few months ago on their blog? So at least they are looking
into this matter. it is not clear as of yet what they will do with it and I
just hope it does not mean return of ADP.

2) ODBC certainly has a long history but I do not think I would
characterize it as 'really long in the tooth'. For one thing, Microsoft
released new ODBC specifications & API with some enhancements. Next, SQL
Server implements ODBC natively rather than a wrapper atop their native
libraries. Thirdly, pick any random RDBMS and chance are it that there is
an official ODBC driver that is mature and supported compared to OLEDB
driver which may be supplied by a third party and require a separate
fee. If I were to guess at prevalence, I'd think that ODBC is in front,
JDBC behind, and OLEDB third. This is not to say that OLEDB does not have
its benefits, just that ODBC remains very much viable as OLEDB and I
hope that Access team will continue to develop Jet as a good ODBC client
among OLEDB.

3) Honestly, I do not know what they will do with the "ADO replacement". A
right step in the direction probably would be to actually support OLEDB
linked tables. I suppose it falls into three possibilities:

a) Enhance DAO to support those new functionality. Note that we
basically already had that in form of ODBCDirect, now deprecated, which
I think floppped due to the fact that like ADO, it had no UI element -
it had to be done in VBA and making a new workspace makes it more
complicated for no good reason. There may be other problems with it but
I do not know because among data access technologies, it is very very
sparsely documented and rarely mentioned in various venues which further
hints at its failure.

b) Write a new data access library, perhaps building atop OLEDB but
native to Access. I think that is foolish because that just means more of
Access team's budget get sucked up developing this library and will end
up keeping up with the Joneses with the rest of the development world. I
certainly hope not; I hate to see a new project come in only to die a
few years later (e.g. JRO, RDO and all other silly duplicating libraries).

c) Take in an existing library and integrate it. Right now, the only
blessed data acces library I know of from Microsoft besides ADO (which
may not be deprecated and supported but certainly not actively
developed) and those in MDAC is ADO.NET. Because it is a part of .NET
framework, it may very well mean a large change to the Access' codebase
is going to be needed to work with it well enough or at least avoid the
unwanted overhead of COM Interop. Because there is already large
investment backing ADO.NET, I think the ROI for Access team is better
since they can just ride the wave but whether the change necessary to
integrate into Access justify the investment is a question I can only
speculate on.

18-Jun-10 10:40 PM

He tried and tried and tried through all three versions, and
eventually threw up his hands in despair.

[]


Oh, yes. On StackOverflow.com, I frequently point those who are
asking about the future of ADP to that post.


ODBC may be evolving, but is Jet/ACE's interface to it evolving
along with it? So far as I am aware, it is not.


I see ODBCDirect as similar in purpose to the ADP, which is to avoid
Jet, which I see as completely misguided from the beginning. Note
that both ADPs and ODBCDirect are de facto deprecated (though
ODBCDirect is *really* deprecated in A2010).


OLEDB is COM, and I do not think it is wise to build anything on a COM
platform.


Seems to me that the .NET enabling of Access is the future, as COM
cannot go on forever in the face of MS's push for code security. it is
also "odd man out" in terms of MS's current development platforms.
VBA goes along with that, i.e., another COM component that Access is
dependent on, and that, I believe, eventually has to be replaced
with something .NET-based.

But the other alternative is a deprecation of VBA in favor of the
new macros, which are pretty full-featured in terms of flow
control/logic and error handling. But that means no ad hoc
integration with outside components. That's inflexible but it means
Access code execution is safer.

18-Jun-10 10:41 PM

Have you tried convincing them to do it in a way that avoids the
problem?


I am playing devil's advocate. Use this feedback with the client to
make yourself look good... ;)

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

18-Jun-10 07:52 PM

I have gotten a tremendous amount of pressure this week to finish the
project, so since the issue only rears it is head if user deletes the data in
a non-required field, it will be taking a back seat to the balance of the
project for now.

Thanks again to both you and Banana. While we did not find a solution, I
certainly learned some things - one of the reasons that I like this
business.

--

AG
Email: npATadhdataDOTcom

19-Jun-10 09:16 PM

Good point. I hope it will be the case for next version. I think the
biggest favor they can do at this point is to open up this part for
configuration. If you are familiar with ADO.NET, there is a means of
specifying InsertCommand, UpdateCommand, and DeleteCommand. Just to have
those properties and thus override Access' default (which tend to work
OK for several cases but will fail in boundary cases just like this one
we looked at in this thread) would be significantly simpler than trying
to update and fixing bad assumptions Access makes in executing those
commands in response to bound forms' activity.


As I said, it is deprecated and I believe it is actually so in 2007, not 2010.


For some reasons, I thought ADO.NET built atop OLEDB despite it being a
COM but now I cannot find anything to substantiate that.


I would love to see a .NET-enabled Access even though I know that not
many developers are big fan of .NET for various reasons. But as you say,
it is the future for better or worse.


I hope not, too. That'd pretty much make it no better than FileMaker and
even FileMaker has been improving their products to support a wider
range of scripting not to mention Alpha Five bragging that their
development team develop Alpha Five in same language (xBasic) as the
developers use for their A5 application, if I understand things
correctly. It'd be utterly backward of Access team to not provide a
VBA-like replacement.

19-Jun-10 09:16 PM

Best of luck. I do hope you find a solution your client will be happy -
if it is something different or creative, I'd love to hear about it.


FWIW:

As part of teaching myself PostgreSQL, I actually thought of your
situation and put this to practice. I was able to write a INSTEAD OF
rule upon a three-table view that would represent one row collectively
and found that it even manage to handle the case where a non-required
field is filled then deleted prior to actually inserting the whole row
into view "virtually". I did not get a #Deleted in that case.

I do not have an analyzer result - too immature with my PostgreSQL-fu at
this point. I can only say that a rule is not a true trigger.
PostgreSQL's rules are basically a way to rewrite/expand the incoming
SQL statement and happens after parsing the query but before creating an
execution plan (so the docs say...). Whether the fact that it was a rule
or because PostgreSQL / its ODBC driver does something right (even if it
technically is a bug), Access did not mind that at all.

Not that I would think it practical at this point to suddenly change the
backend server under your client. ;) But at least we know it _could_
work somewhere else.

20-Jun-10 03:32 AM

Is it Access or Jet/ACE that is making the bad assumptions?


Well, while it may have been deprecated in A2007, I believe you
cannot use it at all in A2010 (but I could be misremembering).

[]


Yes, but I understand why they'd want to get away from such a
completely open-ended scripting language.

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

20-Jun-10 09:34 PM

AG
Email: npATadhdataDOTcom


Interesting. Was that with Access 2007? Or an earlier version?
Does PostgreSQL pass the identity (autonumber) property of a field in the
view?
If it does, that would explain why it works.

20-Jun-10 11:56 PM

I was tempted to say Access because I observe this problem far more
often via a bound form scenario than when I manipulate Jet/ACE via VBA.
However, thinking about it, it could actually be Jet/ACE - So, I set up
a quick test running a recordset adding new record, editing the same
record then deleting it, all in VBA and watched MySQL's general log as I
stepped through the operation. I actually could not finish the operation
because as soon as I passed .AddNew, Jet asked for a new record with
criteria "WHERE ID IS NULL", which is sort of reasonable since it was
null when I added it but that is no longer true and should have passed
back a @@identity or whatever. Because of that, my attempts do
definitely Jet/ACE making the bad assumptions.

Thinking about it, it seems that it just happen that I run into that
problem more often with a bound form only because when I do it in VBA,
it is typically some variant of PTQ or where navigating will not be againts
an edited recordset.


Hmm. I know for fact that DAP is completely gone in 2010, but do not know
if that is true for ODBCDirect. No matter as it was junky technology anyway.


Well, if we are to assume that VSTO is the future, I can see how it'd
work out in such way that the Access would not come with open-ended
language out of the box but if you had VSTO, you could do anything with
it. That'd probably be the most neat solution for all parties involved,
save for the open question of whether one really can continue to create
Access applications with just macros. I'd imagine by far large majority
of Access applications simply would not exist without VBA and thus a
suitable replacement and restricting it to requiring a Visual Studio
license may be too excessive.

20-Jun-10 11:57 PM

On 2003.


Come to think of it, the sample data I used had natural keys. There is a
table that does have a serial data type (which is what PG call their
autonumber/identity) but in that view it is a child table to the main
table that uses a natural key. The view does not show the serial number
for that child table. FWIW, though, I have configured PG ODBC driver to
emulate SQL Server's identity behavior.

I should try and switch the order other way to verify that it works even
with the autonumber being used as a keyset to navigate the recordset.
Probably will not be until next weekend before I can get to play with PG
again. Thanks for pointing that one out.

21-Jun-10 08:43 AM

From what I have seen, the factor that determines how Access requests the
new record is whether or not it recognizes a field that is an 'autonumber'.
If it recognizes an autonumber, it will request the new record using
@@identity, otherwise it will use the fields that had data entered into
them.

Just open the linked view in design view in Access and see it there is a
field with data type of autonumber.
--

AG
Email: npATadhdataDOTcom

22-Jun-10 12:56 AM

Eh?

I am not sure I understand what you are talking about with VSTO.

I would assume that if they replace VBA with .NET, the IDE will be
there within Access for writing .NET code. I would also assume it
would be limited to managed code, which means certain things about
what kinds of external components you can reference.

I do not consider that a terrible thing, actually.

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

21-Jun-10 09:06 PM

If I open the linked table itself, Access correctly identifies it as an
autonumber. But in a view containing the same linked table, it is just a
when I created a new multiple-table view using the identity column as
unique index, I get the same #Deleted behavior only if I insert then
delete non-required data.

PostgreSQL uses a sequence generator so I thought that if I went and
inserted the next value from the sequence it would save me. To my
surprise, it did not. I would have thought that the problem was related
to the fact that it had to get the new ID back from the server by using
all other columns but should not be a problem if ID was fed in and thus
available to Access for adding, as was the case with my first view that
used natural keys. I am still scratching my head over that one.

In my efforts to understand the logs (PostgreSQL's logs are downright
ugly compared to either SQL Server or MySQL logs!), I thought of this
workaround and it does seem to work. See if you can replicate that with
the code below:

Private ctl As Access.Control 'iterator
Private KeyValues As New Collection

Private Sub Form_BeforeUpdate(Cancel As Integer)

Set KeyValues = Nothing

For Each ctl In Me.Controls
If ctl.Tag = "Insertable" Then
KeyValues.Add ctl.Value, ctl.name
End If
Next

Me.Undo

For Each ctl In Me.Controls
If ctl.Tag = "Insertable" Then
If Not IsNull(KeyValues(ctl.name)) Then
ctl.Value = KeyValues(ctl.name)
End If
End If
Next

End Sub

22-Jun-10 09:05 AM

In the beginning, I thought that the PK should be the driver, but
unfortunately, it is the Autonumber property that Access needs and I have
found no way to propogate that to Access.
Defining the PK in the view itself or within Access via DDL makes no
difference to the problem.

Your solution looks very promising. I will not get to try it for a few days,
but will do so.

--

AG
Email: npATadhdataDOTcom

22-Jun-10 01:46 PM

Right now, VSTO is not packaged with the Office but with Visual Studio.
Therefore to do anything in the Office via managed code environment you
would need to get a certain version of Visual Studio. Also, it used to
be a separate product as well. Not anymore, it seems.

Who knows - they may change the structure and package VSTO to go with
Office when it actually replaces VBA editor rather than being an
alternative but that is not how things are set up right now AFAICT.


Nor do I. However, I have been surprised by backlash among other people
who has been programming in VB/VBA at the idea that managed code should
be the successor.

22-Jun-10 10:59 PM

I do not think those people understand the issues that managed code
addresses. I know VB.NET is very different from VBA, but if they
make a version specific to Office/Access, then I think I can likely
adapt. It will be interesting to see if they can convert old code,
though. The change from Access Basic to VBA was minor by comparison,
I'd think. Something like the compatibility checker in A2010 for web
deployment would seem to me to be the best way to address that (run
the compatibility checker, fix up your code to meet the necessary
requirements, then convert. Dunno how possible that would be, but
I am speculating about something that is likely a couple of Access
versions in the future.

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

22-Jun-10 11:02 PM

Is this perhaps the same problem as with BigInt in SQL Server? That
is, the server-side data type does not exist in Jet/ACE, so you have
to work around it? If I recally correctly, BigInt autoincrement PKs
are added correctly, but you have to cast them as string in order to
display them. Maybe something like that is necessary with this data
type in PostgreSQL.

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

22-Jun-10 10:43 PM

I checked and verified that the data type is a four bytes integer. But
to satisfy my own curiosity - I went and made a modified view that would
convert the column from integer to text and tried it out. I verified
that Access correctly perceived the converted column as a text rather
than its original integer. Got the same behavior of #Deleted nonetheless.

Also, if it was represented as eight bytes integer (e.g. bigint) we
would not be able to do anything; all rows would show up as #Deleted as
soon as it is opened. Tangent: PostgreSQL ODBC driver has an option to
coerce bigint into other data type so even if it was using bigint, I'd
be getting text anyway.

In this case, we can update, delete, and even insert into this view
without any problem. it is only when someone types something in a field
that is not required then later clear that field prior to actually saving
the record back to the source that we get the #Deleted behavior. This
occurs Jet/ACE sends back "WHERE ... a_field = NULL ..." in its second
attempt to find the newly inserted record, just as AVG reported in the
OP. Obviously this is an impossible criteria and thus Access never find
that newly inserted record.

Returning back to the original view that shows the column as an integer,
I tried to insert value by hand and it functions just fine, which lines
up with what I observed with the other view using natural keys. I then
found out that my earlier idea of using nextval() does in fact work - it
failed first time because in my rule, I had new id autogenerated in the
insert, thus tossing out whatever would be in the id. When I changed
that rule to insert the value as it is, nextval() now works correctly
and thus can be used to protect from the #Deleted and is more robust
than my other kludge of undoing then redoing.

Unfortunately, that may be problematic to implement against a SQL Server
or MySQL backend as either does not support the concept of sequences and
thus there is no multiuser-safe nextval() to call. One could kludge by
creating a little one row, one column table to hold the highest id and
use a serializable transaction to obtain a new value. I am doubtful that
is a better solution than the undoing/redoing kludge I mentioned in
other branch.

22-Jun-10 10:55 PM

As a matter of fact, they already have something... not to convert the
code but to enable interoperability between VBA and VSTO code. I cannot
find the site I originally saw but hope this should cover everything
adequately:

http://msdn.microsoft.com/en-us/library/bb931201(office.12).aspx
http://channel9.msdn.com/shows/In+the+Office/Extend-your-VBA-with-NET/

That means the application that replies on code can be migrated from VBA
to VSTO in staged migration, which may be more easier to sell than
converting everything at once then dealing with bugs that crops up from
conversion.

23-Jun-10 01:19 AM

Wow. has my suggestion ever spawned an interesting thread.  I will take
complete credit.  <smile>

Tony

23-Jun-10 08:44 AM

Tony,

It was a good suggestion. I learned some things (and hope others did also).

And, I certainly appreciate all the effort and feedback from Banana and
David.

BTW, I just got a message from the Microsoft tech that is handling the
case -

you provided.  Right now I do not have any good estimates on when this might
be fixed."

Don't know if that means they will 'at some point' fix it, or just that it
is now 'officially' a bug.

--

AG
Email: npATadhdataDOTcom

23-Jun-10 06:36 PM

Hard to say.  I assume MS fixes bugs which affect more people.  I hate
to say it but if you are the only person reporting this problem well
....

Tony

23-Jun-10 07:42 PM

Considering that I have reproduced this against two different backends,
and I personally have had run afoul of #Deleted few times but worked
around it in past (to be fair - I cannot say if they were same as AVG has
pinpointed and it is too long ago for me to remember the exact
circumstances but suffice to say it does happen frequently enough to be
an irritation), I am going to be very, very irate if they did not think it
important. Besides they did ask about improving SQL Server integration
for next version of Access few months ago. I'd think that'd be a good
case to make.

I intend to report this because well, #Deleted just is not right! I do
sincerely hope others will likewise report this bug so Access will be
able to fully support different objects (in this cases, views and
triggers). As was discussed in another branch - ODBC and OLEDB are
evolving. Time for Access to keep up.

23-Jun-10 08:09 PM

If you report it and others do then I would assume the chances of it
getting fixed get better and better.   Especially if you can give MS
clear step by step instructions and a small sample database.

Tony

24-Jun-10 10:15 AM

Yes, it would be good if Microsoft had more reports of the problem. Perhaps
they would fix it faster (assuming they intend to fix it).
I have already sent them a database and Access file, but, the more the
merrier :).

Even in their own technical article, they recommend using views with instead
of triggers.
http://msdn.microsoft.com/en-us/library/bb188204.aspx

FWIW, I was just working in Access 2003 and was curious if the problem
existed there also. So I saved my sample 2007 db as 2003 and tried it.
The problem did exist with 2003.

--

AG
Email: npATadhdataDOTcom

24-Jun-10 10:56 AM

Yes, that is what I observed, as I was using 2003 all the time. I even
was able to reproduce this even against a base table at least for MySQL
backend and not just a view so the scope is more wide and thus should
definitely addressed.

24-Jun-10 07:22 PM

Well, I am not sure this is not a flaw in classic ODBC and something
that is rather hard to address.

I still believe that the interface choice is a mistake, and if that
mistake is avoided, this "bug" is avoided.

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

24-Jun-10 10:57 PM

I do not agree. It is Jet/ACE that is generating the SQL statements and
consequently building the erroneous string containing "a_field = NULL".
Furthermore, it is Jet that is making blind guesses in regards to
locating the newly inserted row without even presenting a simple
property for the developers to tell how to obtain the information needed
to process changes.

BTW, I had a deeper look into this and remembered that PostgreSQL ODBC
driver has a setting that emulates SQL Server's identity column. It was
originally enabled. When I disabled that setting, PostgreSQL's serial
was represented as just "Number" and had same problems as MySQL tables
did. Turn the emulation back on, and that behavior disappears for any
base tables but does not help with the views. Based on that info, it
seems to me that whatever they did to map SQL Server's identity column
to Access' AutoNumber was a bit of voodoo and not a properly general
solution.

ODBC API does provide information on whether a column is a kind of auto
incrementing, but does not provide a means of providing the value itself
as that kind of operation is inherently backend-specific. This is
actually reasonable as there is no good way to generalize that behavior.
Remember that @@identity may not be always be reliable then there is the
fact that PostgreSQL and Oracle wants us to name the sequence for
currval()/nextval(). Which goes to the original point: Providing a
property for linked tables/views to declare a column as an
autoincrementing column and specify how Access would then obtain the new
value would be far more simpler and robust than any voodoo they
currently take to make things appear to work when inserting new rows
using identity as the keyset. Changing the interface will not
necessarily solve this fundamental problem.

BTW, the Jet's behavior of discovering new values is documented in the
Jet/ODBC whitepaper so I would think it is Jet that is the responsible
party for handling the inserts correctly. ODBC is just a messenger.

25-Jun-10 10:58 PM

I kind of see this as railing against Access when it tells you
*should* be updatable!"

it is not, so get used to it.

I just think the UI is mistaken from the get-go, and whether or not
this should work theoretically is really a different question. Sure,
it should work theoretically. Theoretically, Jet/ACE supports 255
simultaneous users, but nobody with any sense is going to recommend
trying it in reality.

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

27-Jun-10 07:06 PM

Blogged at
http://msmvps.com/blogs/access/archive/2010/06/27/bug-with-access-and-sql-server-erroneous-deleted-bug-on-insert-with-null-values.aspx
so it will be easier for others to find this problem in the search
engines.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/