Access 2007 bug with SQL Server Back End
in [Help]
|
From: Banana on 18 Jun 2010 08:14 On 6/17/10 8:35 PM, David W. Fenton wrote: > 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. Aha, and thus the significance of quoting him. Thanks. > That only works if you're *not* using an ADP! Okay, but nobody here is using ADP anyway. > 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. 1) Are you aware that Access team asked about Access 15 + SQL Server integration few months ago on their blog? So at least they're looking into this matter. It's not clear as of yet what they'll do with it and I just hope it doesn't mean return of ADP. 2) ODBC certainly has a long history but I don't think I would characterize it as 'really long in the tooth'. For one thing, Microsoft released new ODBC specifications & API with some enhancements. Next, SQL Server implements ODBC natively rather than a wrapper atop their native libraries. Thirdly, pick any random RDBMS and chance are it that there's an official ODBC driver that's mature and supported compared to OLEDB driver which may be supplied by a third party and require a separate fee. If I were to guess at prevalence, I'd think that ODBC is in front, JDBC behind, and OLEDB third. This is not to say that OLEDB doesn't have its benefits, just that ODBC remains very much viable as OLEDB and I hope that Access team will continue to develop Jet as a good ODBC client among OLEDB. 3) Honestly, I don't know what they'll do with the "ADO replacement". A right step in the direction probably would be to actually support OLEDB linked tables. I suppose it falls into three possibilities: a) Enhance DAO to support those new functionality. Note that we basically already had that in form of ODBCDirect, now deprecated, which I think floppped due to the fact that like ADO, it had no UI element - it had to be done in VBA and making a new workspace makes it more complicated for no good reason. There may be other problems with it but I don't know because among data access technologies, it's very very sparsely documented and rarely mentioned in various venues which further hints at its failure. b) Write a new data access library, perhaps building atop OLEDB but native to Access. I think that's foolish because that just means more of Access team's budget get sucked up developing this library and will end up keeping up with the Joneses with the rest of the development world. I certainly hope not; I hate to see a new project come in only to die a few years later (e.g. JRO, RDO and all other silly duplicating libraries). c) Take in an existing library and integrate it. Right now, the only blessed data acces library I know of from Microsoft besides ADO (which may not be deprecated and supported but certainly not actively developed) and those in MDAC is ADO.NET. Because it's a part of .NET framework, it may very well mean a large change to the Access' codebase is going to be needed to work with it well enough or at least avoid the unwanted overhead of COM Interop. Because there's already large investment backing ADO.NET, I think the ROI for Access team is better since they can just ride the wave but whether the change necessary to integrate into Access justify the investment is a question I can only speculate on.
From: David W. Fenton on 18 Jun 2010 18:40 Banana <Banana(a)Republic.com> wrote in news:4C1B6343.6060908(a)Republic.com: > On 6/17/10 8:35 PM, David W. Fenton wrote: >> 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. > > Aha, and thus the significance of quoting him. Thanks. He tried and tried and tried through all three versions, and eventually threw up his hands in despair. [] >> 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. > > 1) Are you aware that Access team asked about Access 15 + SQL > Server integration few months ago on their blog? So at least > they're looking into this matter. It's not clear as of yet what > they'll do with it and I just hope it doesn't mean return of ADP. Oh, yes. On StackOverflow.com, I frequently point those who are asking about the future of ADP to that post. > 2) ODBC certainly has a long history but I don't think I would > characterize it as 'really long in the tooth'. For one thing, > Microsoft released new ODBC specifications & API with some > enhancements. Next, SQL Server implements ODBC natively rather > than a wrapper atop their native libraries. Thirdly, pick any > random RDBMS and chance are it that there's an official ODBC > driver that's mature and supported compared to OLEDB driver which > may be supplied by a third party and require a separate fee. If I > were to guess at prevalence, I'd think that ODBC is in front, JDBC > behind, and OLEDB third. This is not to say that OLEDB doesn't > have its benefits, just that ODBC remains very much viable as > OLEDB and I hope that Access team will continue to develop Jet as > a good ODBC client among OLEDB. 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. > 3) Honestly, I don't know what they'll do with the "ADO > replacement". A right step in the direction probably would be to > actually support OLEDB linked tables. I suppose it falls into > three possibilities: > > a) Enhance DAO to support those new functionality. Note that we > basically already had that in form of ODBCDirect, now deprecated, > which I think floppped due to the fact that like ADO, it had no UI > element - it had to be done in VBA and making a new workspace > makes it more complicated for no good reason. There may be other > problems with it but I don't know because among data access > technologies, it's very very sparsely documented and rarely > mentioned in various venues which further hints at its failure. 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). > b) Write a new data access library, perhaps building atop OLEDB > but native to Access. I think that's foolish because that just > means more of Access team's budget get sucked up developing this > library and will end up keeping up with the Joneses with the rest > of the development world. I certainly hope not; I hate to see a > new project come in only to die a few years later (e.g. JRO, RDO > and all other silly duplicating libraries). OLEDB is COM, and I don't think it's wise to build anything on a COM platform. > c) Take in an existing library and integrate it. Right now, the > only blessed data acces library I know of from Microsoft besides > ADO (which may not be deprecated and supported but certainly not > actively developed) and those in MDAC is ADO.NET. Because it's a > part of .NET framework, it may very well mean a large change to > the Access' codebase is going to be needed to work with it well > enough or at least avoid the unwanted overhead of COM Interop. > Because there's already large investment backing ADO.NET, I think > the ROI for Access team is better since they can just ride the > wave but whether the change necessary to integrate into Access > justify the investment is a question I can only speculate on. Seems to me that the .NET enabling of Access is the future, as COM can't go on forever in the face of MS's push for code security. It's also "odd man out" in terms of MS's current development platforms. VBA goes along with that, i.e., another COM component that Access is dependent on, and that, I believe, eventually has to be replaced with something .NET-based. But the other alternative is a deprecation of VBA in favor of the new macros, which are pretty full-featured in terms of flow control/logic and error handling. But that means no ad hoc integration with outside components. That's inflexible but it means Access code execution is safer. I hope they go with something as flexible as VBA rather than crippling Access with nothing but macros. But I don't know what their thinking is on these issues. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 18 Jun 2010 18:41 "AVG" <NOSPAMagiamb(a)newsgroup.nospam> wrote in news:4c1b620a$0$31263$607ed4bc(a)cv.net: > 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. Have you tried convincing them to do it in a way that avoids the problem? > BTW, if I didn't mention it before, thanks for all of your input. I'm playing devil's advocate. Use this feedback with the client to make yourself look good... ;) -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: AVG on 18 Jun 2010 19:52 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. 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. -- AG Email: npATadhdataDOTcom "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message news:Xns9D9BBE6DC9ABEf99a49ed1d0c49c5bbb2(a)74.209.136.99... > "AVG" <NOSPAMagiamb(a)newsgroup.nospam> wrote in > news:4c1b620a$0$31263$607ed4bc(a)cv.net: > >> 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. > > Have you tried convincing them to do it in a way that avoids the > problem? > >> BTW, if I didn't mention it before, thanks for all of your input. > > I'm playing devil's advocate. Use this feedback with the client to > make yourself look good... ;) > > -- > David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Banana on 19 Jun 2010 21:16
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. > 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. > OLEDB is COM, and I don't think it's wise to build anything on a COM > platform. For some reasons, I thought ADO.NET built atop OLEDB despite it being a COM but now I can't find anything to substantiate that. > Seems to me that the .NET enabling of Access is the future, as COM > can't go on forever in the face of MS's push for code security. It's > also "odd man out" in terms of MS's current development platforms. > VBA goes along with that, i.e., another COM component that Access is > dependent on, and that, I believe, eventually has to be replaced > with something .NET-based. I would love to see a .NET-enabled Access even though I know that not many developers are big fan of .NET for various reasons. But as you say, it's the future for better or worse. > I hope they go with something as flexible as VBA rather than > crippling Access with nothing but macros. I hope not, too. That'd pretty much make it no better than FileMaker and even FileMaker has been improving their products to support a wider range of scripting not to mention Alpha Five bragging that their development team develop Alpha Five in same language (xBasic) as the developers use for their A5 application, if I understand things correctly. It'd be utterly backward of Access team to not provide a VBA-like replacement. |