Access 2007 bug with SQL Server Back End
in [Help]
|
From: Banana on 19 Jun 2010 21:16 On 6/18/10 4:52 PM, AVG wrote: > I have gotten a tremendous amount of pressure this week to finish the > project, so since the issue only rears it's 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. Best of luck. I do hope you find a solution your client will be happy - if it's something different or creative, I'd love to hear about it. > 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. 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 didn't get a #Deleted in that case. I don't have an analyzer result - too immature with my PostgreSQL-fu at this point. I can only say that a rule isn't 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 didn't 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.
From: David W. Fenton on 19 Jun 2010 23:32 Banana <Banana(a)Republic.com> wrote in news:4C1D6BE3.7070504(a)Republic.com: > On 6/18/10 3:40 PM, David W. Fenton wrote: >> ODBC may be evolving, but is Jet/ACE's interface to it evolving >> along with it? So far as I'm aware, it's not. > > Good point. I hope it'll 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're familiar with ADO.NET, there's 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. Is it Access or Jet/ACE that's making the bad assumptions? >> 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). > > As I said, it's deprecated and I believe it's actually so in 2007, > not 2010. Well, while it may have been deprecated in A2007, I believe you can't use it at all in A2010 (but I could be misremembering). [] > It'd be utterly backward of Access team to not provide a > VBA-like replacement. 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/
From: AVG on 20 Jun 2010 21:34 AG Email: npATadhdataDOTcom "Banana" <Banana(a)Republic.com> wrote in message news:4C1D6BF7.2010801(a)Republic.com... > On 6/18/10 4:52 PM, AVG wrote: >> I have gotten a tremendous amount of pressure this week to finish the >> project, so since the issue only rears it's 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. > > Best of luck. I do hope you find a solution your client will be happy - if > it's something different or creative, I'd love to hear about it. > >> 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. > > 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 didn't get a #Deleted in that case. > > I don't have an analyzer result - too immature with my PostgreSQL-fu at > this point. I can only say that a rule isn't 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 didn't 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. 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.
From: Banana on 20 Jun 2010 23:56 On 6/19/10 8:32 PM, David W. Fenton wrote: > Is it Access or Jet/ACE that's making the bad assumptions? 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 couldn't 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's no longer true and should have passed back a @@identity or whatever. Because of that, my attempts do ".Bookmark = .Lastmodified" landed me on a "deleted" record. So it's 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's typically some variant of PTQ or where navigating won't be againts an edited recordset. > Well, while it may have been deprecated in A2007, I believe you > can't use it at all in A2010 (but I could be misremembering). Hmm. I know for fact that DAP is completely gone in 2010, but don't know if that's true for ODBCDirect. No matter as it was junky technology anyway. > Yes, but I understand why they'd want to get away from such a > completely open-ended scripting language. 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 wouldn't 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 wouldn't exist without VBA and thus a suitable replacement and restricting it to requiring a Visual Studio license may be too excessive.
From: Banana on 20 Jun 2010 23:57
On 6/20/10 6:34 PM, AVG wrote: > Interesting. Was that with Access 2007? Or an earlier version? On 2003. > Does PostgreSQL pass the identity (autonumber) property of a field in the > view? > If it does, that would explain why it works. 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's a child table to the main table that uses a natural key. The view doesn't 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 won't be until next weekend before I can get to play with PG again. Thanks for pointing that one out. |