Access 2007 bug with SQL Server Back End
in [Help]
|
From: David W. Fenton on 16 Jun 2010 16:12 Banana <Banana(a)Republic.com> wrote in news:4C18D475.9030502(a)Republic.com: > 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 I'm not mistaken, in this case, it's multiple tables in the recordsource, but only one is being edited (and, I presume, having records added to it). I have always been frustrated trying to use a single form of any kind for both adding and editing, and that's why in most of my apps, adding is done through a separate unbound form, then the editing form is requeried and then I navigate via bookmark navigation to thenewly-added record. This keeps the two processes separate. It also makes it harder for the user to accidentally add a record, as well as making it easier to abandon a record. The unbound form collects the fields that are required to create the record (and if not strictly-speaking required in the table definition, at least the fields that are necessary to have a usable, identifiable record). -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: AVG on 16 Jun 2010 16:16 Yes, the ADO source is 'select * from myview where ...'. Yes, it is updating tables not specified in the UniqueTable property. Trying different providers: SQL Native Client - form is not updatable. SQL Server - error, class not registered. MSDASQL - error, Data source name not found and no default driver specified. The construct of the view can be easily queried from SQL Server, although I don't see it in the trace. Following is the trace when the form opens. Note, the extra selects are for combo box rowsources. SET NO_BROWSETABLE ON go SELECT vwPreappraisal.* FROM vwPreappraisal WHERE ((([vwPreappraisal].[ContactID])=6992) And (([vwPreappraisal].[ModeID])=11) And (([vwPreappraisal].[BatID])=5783)) ORDER BY [RegionCode], [WineName], [Designation], [Vintage], [Producer], [SortOrder] go set implicit_transactions off go SELECT "dbo"."tblMstWineNames"."WineNameID" ,"dbo"."tblMstWineNames"."WineName" ,"dbo"."tblMstWineNames"."RegionID" ,"dbo"."tblMstWineNames"."WineAppelID" ,"dbo"."tblMstRegions"."RegionCode" FROM {oj "dbo"."tblMstWineNames" LEFT OUTER JOIN "dbo"."tblMstRegions" ON ("dbo"."tblMstWineNames"."RegionID" = "dbo"."tblMstRegions"."RegionID" ) } ORDER BY "dbo"."tblMstWineNames"."WineName" ,"dbo"."tblMstRegions"."RegionCode" go SELECT "WineNameID" ,"WineName" ,"RegionID" ,"WineAppelID" FROM "dbo"."tblMstWineNames" go SELECT "DesignationID" ,"Designation" FROM "dbo"."tblMstDesignations" WHERE ("DesignationID" > 0 ) ORDER BY "dbo"."tblMstDesignations"."Designation" go SELECT "dbo"."tblMstBottleSizes"."SizeID" ,"dbo"."tblMstBottleSizes"."BottleName" ,"dbo"."tblMstBottleSizes"."RegionId" ,"dbo"."tblMstRegions"."RegionCode" FROM {oj "dbo"."tblMstBottleSizes" LEFT OUTER JOIN "dbo"."tblMstRegions" ON ("dbo"."tblMstBottleSizes"."RegionId" = "dbo"."tblMstRegions"."RegionID" ) } ORDER BY "dbo"."tblMstBottleSizes"."BottleName" ,"dbo"."tblMstBottleSizes"."RegionId" go SELECT "SizeID" ,"BottleName" ,"RegionId" ,"SortOrder" ,"IsLargeFormat" ,"BottleCode" FROM "dbo"."tblMstBottleSizes" go SELECT "ProducerID" ,"Producer" FROM "dbo"."tblMstProducers" WHERE ("ProducerID" > 0 ) ORDER BY "dbo"."tblMstProducers"."Producer" go SELECT "VintageId" ,"Vintage" FROM "dbo"."tblMstVintages" ORDER BY "dbo"."tblMstVintages"."Vintage" go SELECT "VintageId" ,"Vintage" FROM "dbo"."tblMstVintages" go Following is trace on an update: exec sp_executesql N'UPDATE "InsertTest"."dbo"."tblMstItems" SET "VintageID"=@P1 WHERE "WineNameID"=@P2 AND "AppellationID"=@P3 AND "DesignationID"=@P4 AND "ProducerID"=@P5 AND "SizeID"=@P6 AND "VintageID"=@P7 AND "ClassCode"=@P8 AND "UPC" IS NULL AND "ItemID"=@P9',N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 varchar(1),@P9 int',108,1251,0,0,0,1,113,'N',3242949 go exec sp_executesql N'SELECT "WHID","ContactID","AuctionID","Quantity","LowBase","HighBase","LowEstimate","HighEstimate","Reserve","CalcOverride","PaGroup","Seq","NoteChoice","NoteId","ModeID","BatID","BoxCodeID","PaIsGroupLot","ChgHandl","HistNote","IsCurrent","WhTs" FROM "InsertTest"."dbo"."tblWineHistory" WHERE "WHID"=@P1',N'@P1 int',3666809 go exec sp_executesql N'SELECT "DesignationID" FROM "InsertTest"."dbo"."tblMstDesignations" WHERE "DesignationID"=@P1',N'@P1 int',0 go exec sp_executesql N'SELECT "ProducerID" FROM "InsertTest"."dbo"."tblMstProducers" WHERE "ProducerID"=@P1',N'@P1 int',0 go exec sp_executesql N'SELECT "AppellationID" FROM "InsertTest"."dbo"."tblMstAppellations" WHERE "AppellationID"=@P1',N'@P1 int',0 go exec sp_executesql N'SELECT "WLID","ItemID","Levels","Label","Cork","Color","Other","DateCreated","CreateInitials","WlTs" FROM "InsertTest"."dbo"."tblWineLots" WHERE "WLID"=@P1',N'@P1 int',5245783 go exec sp_executesql N'SELECT "RegionCode","RegionDescription","RegionID" FROM "InsertTest"."dbo"."tblMstRegions" WHERE "RegionID"=@P1',N'@P1 int',20 go exec sp_executesql N'SELECT "WineName","RegionID","WineNameID" FROM "InsertTest"."dbo"."tblMstWineNames" WHERE "WineNameID"=@P1',N'@P1 int',1251 go exec sp_executesql N'SELECT "Vintage","VintageId" FROM "InsertTest"."dbo"."tblMstVintages" WHERE "VintageId"=@P1',N'@P1 int',113 go exec sp_executesql N'SELECT "BottleName","SortOrder","BottleCode","SizeID" FROM "InsertTest"."dbo"."tblMstBottleSizes" WHERE "SizeID"=@P1',N'@P1 int',1 go exec sp_executesql N'SELECT "WineNameID","AppellationID","DesignationID","ProducerID","SizeID","VintageID","ClassCode","UPC","ItemID" FROM "InsertTest"."dbo"."tblMstItems" WHERE "ItemID"=@P1',N'@P1 int',3242949 go On starting a new record (first time only), the trace showed sp_fkeys and sp_MShelpcolumns executing for each table contained in the view. Following is the trace on attempting to save a new record. This is whre the 'Invalid Input Parameter...' error occurs in Access, which makes sense, as it is trying to insert directly into a table. SELECT ((1)) go SET FMTONLY ON select "WHID","ContactID","AuctionID","Quantity","LowBase","HighBase","LowEstimate","HighEstimate","Reserve","CalcOverride","PaGroup","Seq","NoteChoice","NoteId","ModeID","BatID","BoxCodeID","PaIsGroupLot","ChgHandl","HistNote","IsCurrent" from "InsertTest"."dbo"."tblWineHistory" SET FMTONLY OFF go SET NO_BROWSETABLE ON go declare @p1 int set @p1=-1 exec sp_prepare @p1 output,N'@P1 int,@P2 int,@P3 int,@P4 smallint,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 bit,@P11 varchar(3),@P12 smallint,@P13 varchar(1),@P14 int,@P15 smallint,@P16 int,@P17 int,@P18 bit,@P19 bit,@P20 varchar(255),@P21 bit',N'INSERT INTO "InsertTest"."dbo"."tblWineHistory"("WHID", "ContactID", "AuctionID", "Quantity", "LowBase", "HighBase", "LowEstimate", "HighEstimate", "Reserve", "CalcOverride", "PaGroup", "Seq", "NoteChoice", "NoteId", "ModeID", "BatID", "BoxCodeID", "PaIsGroupLot", "ChgHandl", "HistNote", "IsCurrent") VALUES(@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21)',1 select @p1 go Looking at a few more examples of the ResyncCommand, I think it's intention is to resync edited records, rather than inserts. I see what you mean about the parameter, but on on insert, there is no PK value to substitute for the parameter. Based on these results and David's info, while it was a good suggestion, I think it is time to scrap the ADO solution. -- 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 17:20 On 6/16/10 1:12 PM, David W. Fenton wrote: > If I'm not mistaken, in this case, it's multiple tables in the > recordsource, but only one is being edited (and, I presume, having > records added to it). Actually, AVG said 3 tables are being updated/inserted via a INSTEAD OF trigger. He was following a MSDN article by Andy Baron that recommended this very thing for Access. > I have always been frustrated trying to use a single form of any > kind for both adding and editing, and that's why in most of my apps, > adding is done through a separate unbound form, then the editing > form is requeried and then I navigate via bookmark navigation to > thenewly-added record. This keeps the two processes separate. It > also makes it harder for the user to accidentally add a record, as > well as making it easier to abandon a record. The unbound form > collects the fields that are required to create the record (and if > not strictly-speaking required in the table definition, at least the > fields that are necessary to have a usable, identifiable record). Certainly understandable, and it does make things much more straightforward. I've used similar technique but there are also places where having a subform that does both editing/adding is more efficient in terms of data entry than forcing a separate form. As long the client understand the inherent complexity, tradeoffs and have been told of all possible alternatives, I'm not going to argue with the client in area of what UI/data entry process they want to have. (designing a database/tables/relationship is entirely another matter, though)
From: Banana on 16 Jun 2010 17:26 On 6/16/10 1:08 PM, David W. Fenton wrote: > 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. Thanks. Better that I know about it by now. I also have to say that is pretty stupid behavior. > 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. I don't know who Steve Jorgensen is, other than that he used to post here long, long ago. But I'm not surprised that ADP were problematic, and ADO does have its baggage. This is why I tend to stick to DAO/ODBC first and only turn to ADO if it can be used to solve a specific problem (and has done with good results). Until Access team decide to give us a replacement for ADO (specifically the ability to have disconnected recordsets, asynchronous operations, along few other goodies) that's what we have to work with.
From: Banana on 16 Jun 2010 17:54
On 6/16/10 1:16 PM, AVG wrote: > Yes, the ADO source is 'select * from myview where ...'. > Yes, it is updating tables not specified in the UniqueTable property. > Trying different providers: > SQL Native Client - form is not updatable. > SQL Server - error, class not registered. > MSDASQL - error, Data source name not found and no default driver specified. RE: MSDASQL, I think it requires a bit more tweaking to get it to work - I can't remember off the hand but I remember I couldn't just change from NCLI to MSDASQL and use same SQL statement or something like that. But eh, forget it. > The construct of the view can be easily queried from SQL Server, although I > don't see it in the trace. Good point. I suppose it could be done and may have been done using a low level call or doesn't show up in trace because it doesn't match the trace's criteria. (e.g. not a BatchCompleted statement for example) > Looking at a few more examples of the ResyncCommand, I think it's intention > is to resync edited records, rather than inserts. > I see what you mean about the parameter, but on on insert, there is no PK > value to substitute for the parameter. That's true though I was thinking it still has to resync anyway after getting the PK. > Based on these results and David's info, while it was a good suggestion, I > think it is time to scrap the ADO solution. I would have had expected there to be a configuration option to suppress ADO's silly long-reaching but if there were, it'd have been known by now. The fact remains that you're still stuck with no good way to insert new records without getting errors, be it #Deleted in DAO/ODBC or Invalid Input Parameter in ADO/OLEDB. David already has mentioned using an unbound form and keeping the addition/edit process separate. I think you already indicated that this is not an acceptable option for you. The alternatives are: 1) Use a local temporary table that mirrors the view structure and commit the edits/inserts as a batch rather than one-by-one live. That way you can then use VBA and thus leverage the server-side processing to handle your data correctly while having the appearance of a bound form. The trade off is that the changes are not live and your users would have to commit them at a certain point of time. If you can live with few minutes worth of delay then that shouldn't be a big deal. If client is willing to pay for extra programming effort, you could shrink that window by doing one-by-one update behind the scene. 2) I've seen but never used the idea of dynamically binding/un-binding form. The idea seems to me more hassles than benefits. 3) Use Form Footer section to contain the unbound fields for inserting new records. The problem is that you have to requery to get it to show up once committed, and DAO doesn't support dynamic cursors (well, it could via ODBCDirect but I fear that is even worse alternative than ADO and it's deprecated as of 2007 anyway). At least the editing would stay live and they could then insert more than one rows without problems but need to requery to see those new rows. I realize those are not the ideal solutions but that's basically the cards we've been dealt. Maybe one of those will be able to meet your client's requirement? HTH. |