Access 2007 bug with SQL Server Back End
in [Help]
|
From: Banana on 15 Jun 2010 15:48 On 6/15/10 12:06 PM, AVG wrote: > 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. Crikey. That's just not right. I'm 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 wasn't 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) > 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(). 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 > 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. For testing purposes, it's sometime desirable to try to do the same thing you'd 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'm not clear if you were able to insert anything with first attempts (without ReSyncCommand, etc) or just when you did the SCOPE_IDENTITY() thingy. > 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. Yeah, that's 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.
From: AVG on 15 Jun 2010 17:34 Good info, thanks. -- AG Email: npATadhdataDOTcom "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message news:Xns9D989C9BCCC1Af99a49ed1d0c49c5bbb2(a)74.209.136.88... > "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/
From: AVG on 15 Jun 2010 20:49 I should be able to try your suggestions tomorrow. In the meantime, have you seen David Fenton's post? -- AG Email: npATadhdataDOTcom "Banana" <Banana(a)Republic.com> wrote in message news:4C17D91C.2040702(a)Republic.com... > On 6/15/10 12:06 PM, AVG wrote: >> 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. > > Crikey. That's just not right. I'm 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 wasn't 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) > >> 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(). > > 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 > >> 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. > > For testing purposes, it's sometime desirable to try to do the same thing > you'd 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'm not clear if you were able to insert anything with first attempts > (without ReSyncCommand, etc) or just when you did the SCOPE_IDENTITY() > thingy. > >> 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. > > Yeah, that's 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.
From: Banana on 16 Jun 2010 09:41 On 6/15/10 5:49 PM, AVG wrote: > I should be able to try your suggestions tomorrow. In the meantime, have you > seen David Fenton's post? As indicated before, I've been lucky to not experience ADO's long reach only because when I do use ADO, it's 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'm more inclined to want to use a single unbound form and execute a stored procedure instead. Of course, if the client doesn't want a separate form for inserting vs editing, then we'll have to deal with that somehow. If he is also correct that it's a ADO flaw, then my suggestion of using different providers wouldn't work since it's 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've 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.
From: David W. Fenton on 16 Jun 2010 16:08
Banana <Banana(a)Republic.com> wrote in news:4C17D91C.2040702(a)Republic.com: > how could ADO know the > source tables... 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's 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 doesn't 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/ |