Access 2007 bug with SQL Server Back End
in [Help]
|
From: Banana on 24 Jun 2010 10:56 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.
From: David W. Fenton on 24 Jun 2010 15:22 Banana <Banana(a)Republic.com> wrote in news:4C23720E.9010205(a)Republic.com: > 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/
From: Banana on 24 Jun 2010 22:57 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. 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.
From: David W. Fenton on 25 Jun 2010 18:58 Banana <Banana(a)Republic.com> wrote in news:4C241B0C.1070506(a)Republic.com: > 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/
From: Tony Toews on 27 Jun 2010 19:06
On Sun, 13 Jun 2010 16:41:53 -0400, "AVG" <NOSPAMagiamb(a)newsgroup.nospam> wrote: >I am posting this at the suggestion of Tony Toews in response to another of >my posts. 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'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/ |