Access 2007 bug with SQL Server Back End

#51

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 -

"I wanted to let you know we do have a bug report based on the information
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

"Tony Toews" <ttoews (AT) telusplanet (DOT) net> wrote

Quote:

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

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

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

Tony

#52

On Wed, 23 Jun 2010 08:44:44 -0400, "AVG"
<NOSPAMagiamb (AT) newsgroup (DOT) nospam> wrote:

Quote:

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

"I wanted to let you know we do have a bug report based on the information
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.

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

Tony

#53

On 6/23/10 3:36 PM, Tony Toews wrote:

Quote:

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

Tony

Considering that I've 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 can't say if they were same as AVG has
pinpointed and it's too long ago for me to remember the exact
circumstances but suffice to say it does happen frequently enough to be
an irritation), I'm going to be very, very irate if they didn't 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 isn't 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.

#54

On Wed, 23 Jun 2010 16:42:31 -0700, Banana <Banana (AT) Republic (DOT) com>
wrote:

Quote:

Considering that I've 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 can't say if they were same as AVG has
pinpointed and it's too long ago for me to remember the exact
circumstances but suffice to say it does happen frequently enough to be
an irritation), I'm going to be very, very irate if they didn't 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 isn't 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.

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

#55

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

"Tony Toews" <ttoews (AT) telusplanet (DOT) net> wrote

Quote:

On Wed, 23 Jun 2010 16:42:31 -0700, Banana <Banana (AT) Republic (DOT) com
wrote:

Considering that I've 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 can't say if they were same as AVG has
pinpointed and it's too long ago for me to remember the exact
circumstances but suffice to say it does happen frequently enough to be
an irritation), I'm going to be very, very irate if they didn't 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 isn't 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.

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

#56

On 6/24/10 7:15 AM, AVG wrote:

Quote:

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.

Yes, that's 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.

#57

Banana <Banana (AT) Republic (DOT) com> wrote in
news:4C23720E.9010205 (AT) Republic (DOT) com:

Quote:

On 6/24/10 7:15 AM, AVG wrote:
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.

Yes, that's 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.

Well, I'm not sure this isn't 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/

#58

On 6/24/10 12:22 PM, David W. Fenton wrote:

Quote:

Well, I'm not sure this isn't 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.

I don't 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's 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 doesn't 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's 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's Jet that's the responsible
party for handling the inserts correctly. ODBC is just a messenger.

#59

Banana <Banana (AT) Republic (DOT) com> wrote in
news:4C241B0C.1070506 (AT) Republic (DOT) com:

Quote:

On 6/24/10 12:22 PM, David W. Fenton wrote:
Well, I'm not sure this isn't 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.

I don't 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's 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.

I kind of see this as railing against Access when it tells you
"recordset is not updatable" by screaming at it and saying "But it
*should* be updatable!"

It's 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/

#60

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

Quote:

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

Blogged at
http://msmvps.com/blogs/access/archi...ll-values.aspx
so it'll 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/