Access 2007 bug with SQL Server Back End
in [Help]
|
From: AVG on 16 Jun 2010 21:12 Thanks for all of your help. I will discuss the options with client. My guess is that he will try living with #deleted and see how often it happens. I think the next best option is the local temporary table. I already use that method in another of his apps. A separate unbound form is definitely out of the question. I had the opportunity today to do a remote session with him and one of his best data entry persons. I could not even completely follow all they were doing, it all happened so fast - including looking up some info on the internet. If I did anything to noticeably slow them down, there would be a mob with torches at my door :). Yes, the structure is much more normalized than the current system, and a bit more than I would like. Part of that is their desire for better tracking and part is dictated by a planned sharing of data with Great Plains. -- AG Email: npATadhdataDOTcom "Banana" <Banana(a)Republic.com> wrote in message news:4C1947FE.8040900(a)Republic.com... > 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.
From: David W. Fenton on 17 Jun 2010 23:35 Banana <Banana(a)Republic.com> wrote in news:4C194189.1040903(a)Republic.com: > I don't know who Steve Jorgensen is, other than that he used to > post here long, long ago. Steve is one of the brilliant Access gurus of the past. I don't know what he ended up doing, but he really tried very, very hard to make ADPs work. > 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). That only works if you're *not* using an ADP! > 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. I wonder what will happen in the next couple of versions of Access. I really think they need to do something to serve the needs of SQL Server users. ODBC is really long in the tooth and needs to be replaced with something that is more current in terms of modern database functionality. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 17 Jun 2010 23:36 Banana <Banana(a)Republic.com> wrote in news:4C19402F.70204(a)Republic.com: > 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) Uh, when the client starts telling me how to do my job, then I tend to start looking for the exits. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 17 Jun 2010 23:39 "AVG" <NOSPAMagiamb(a)newsgroup.nospam> wrote in news:4c197673$0$4983$607ed4bc(a)cv.net: > If I did anything to noticeably slow them down, there would be a > mob with torches at my door :). I'm not at all convinced that adding in a separate unbound form would slow down the process at all. A client that insisted I implement a non-reliable solution would be one I'd be unhappy to work for. It's my job to determine the best solutions, not theirs, and if they insist on telling me how to do my job, I'm going to start looking for the exits. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: AVG on 18 Jun 2010 08:10
That's very noble, but sometimes one must look at the bigger picture and also consider the ripple effect. This particular issue is only one piece, of one large application, out of several applications for a large, and very good client. BTW, if I didn't mention it before, thanks for all of your input. -- AG Email: npATadhdataDOTcom "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message news:Xns9D9AF0C14BB4Af99a49ed1d0c49c5bbb2(a)74.209.136.90... > "AVG" <NOSPAMagiamb(a)newsgroup.nospam> wrote in > news:4c197673$0$4983$607ed4bc(a)cv.net: > >> If I did anything to noticeably slow them down, there would be a >> mob with torches at my door :). > > I'm not at all convinced that adding in a separate unbound form > would slow down the process at all. > > A client that insisted I implement a non-reliable solution would be > one I'd be unhappy to work for. It's my job to determine the best > solutions, not theirs, and if they insist on telling me how to do my > job, I'm going to start looking for the exits. > > -- > David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/ |