Access 2007 bug with SQL Server Back End
in [Help]
|
From: David W. Fenton on 14 Jun 2010 19:22 "AVG" <NOSPAMagiamb(a)newsgroup.nospam> wrote in news:4c158107$0$22529$607ed4bc(a)cv.net: > The view that the form is bound to consists of at least two joined > tables. 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's a real red flag to me that there's a design error. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: AVG on 14 Jun 2010 21:33 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 "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message news:Xns9D97C488FBF38f99a49ed1d0c49c5bbb2(a)74.209.136.100... > "AVG" <NOSPAMagiamb(a)newsgroup.nospam> wrote in > news:4c154288$0$22523$607ed4bc(a)cv.net: > >> Datasheet form bound to an updatable view. > > 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/
From: AVG on 15 Jun 2010 15:06 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 won't 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 doesn't 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 "Banana" <Banana(a)Republic.com> wrote in message news:4C16448F.9010701(a)Republic.com... > On 6/14/10 6:56 AM, AVG 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'. > > Okay. I'll need to test that out and see how it works out. Thanks for that > piece of information. > >> 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. > > 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. > >> The view uses 'INSTEAD OF' triggers for insert, update and delete, >> however, >> the PK is an identity column in one of the tables. > > 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. > >> Just how does Access utilize UniqueTable? Would it still be applicable? > > 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. >
From: David W. Fenton on 15 Jun 2010 15:22 "AVG" <NOSPAMagiamb(a)newsgroup.nospam> wrote in news:4c16d850$0$5004$607ed4bc(a)cv.net: > 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. Separate forms? Who suggested that? Ever heard of subforms? > 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. 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/
From: David W. Fenton on 15 Jun 2010 15:23
"AVG" <NOSPAMagiamb(a)newsgroup.nospam> wrote in news:4c17cf11$0$22546$607ed4bc(a)cv.net: > 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. 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/ |