Access as a Presentation Layer Tool
SelecTViews
With Stephen Wynkoop
Watch as Stephen talks with Scott Gode from Azaleos talks about gaps between private cloud systems and Office 365.
Alvin writes:
I think most people writing to the editorials have already said it one way or another. There is no doubt that SQL Server is a more robust database platform than Access and it is definitely more scalable. And from the complaints I hear from many IT people stemming from the fact that many Access applications that are developed by non-developers using out of the box functionality. To take a different slant, I would argue that a developer can also make poor applications simply by using wizards within SQL Server to design a VLDB. I’ve seen developers develop ERDs with no foreign keys and return million row result sets instead of paging them. Quite simply this not a defect of the tools but a lack of understanding of database fundamentals. In that case, no tool can help you.
From my personal experience many years ago, I’ve inherited an Access 2.0 database application that functioned as an inventory management system went from low use to high use (> 1 million records). The company I used to work for basically boomed but the infrastructure struggled to keep up. There were two big problems that needed to be solved (1) improve query performance and (2) increase application development productivity using the constraint that no additional hardware purchases were involved.
The way I solved it was to migrate the Access backend to SQL Server to reduce locking issues and utilize server-side processing instead of client-side processing. Then I used Design Patterns to separate the logic into the classic 3-tier architecture. The model I used was based on the work of Rockford Lhotka’s CLSA design using classes and stored procedures. I also used real models like Google in how they deal with large record sets (i.e. paging 10 records at a time for very large recordsets) which reduced processing time and network utilization. Development goals were achieved because an Access developer can focus on application tier logic and data components are reusable (best practices: encapsulation, information hiding, etc). The resulting application was very fast in processing time and very quick future development time (months->weeks). Albeit that if the front-end were allowed to be migrated to IIS then the application would be faster still. However, I had a constraint and the solution was sufficient for the problem. And it survived 2 Access upgrades that required almost no changes so it was forwards compatible.
I know some people will still be resistant and say that the solution is still not infinity scalable compared to java or ASP.net which IS true. However, the point is that successful application development is more dependent on how you understand the problem and how you utilize the tool(s) rather than the tool itself. Access does have its limits BUT I would argue that the limit is much higher than people think.
I have also had similar experiences with MS Access. I love the report designer. I have even successfully used third party controls made for VB6 inside access. Access can easily be made to scale, just like any other thick client coding tool.
Alvin used the Component-based Scalable Logical Architecture (CSLA) framework providing robust business objects with persistence, rules, and many other features built in. For Alvin’s situation this option made sense.
The primary reason I would not choose Access as a front end tool, at least for new work, is the memory I have of hours lost when working on Access programs with a team larger than one person. Corrupted ADP files, inadequate granularity through version control, inability to merge changes have relegated it to single developer capabilities in my mind. I haven’t tried for 10 years, and the issues may have been resolved.
If you’re interested in looking into the CSLA framework, here are a couple links for the current Dot net versions.
• CSLA .NET for Windows
• CSLA .NET for Silverlight
One reader has commented that some of our latest topics have migrating toward a moderated list. Let me thank all of you have participated with your comments. We need to move away from the Access discussion in our news letter, and address other issues. If you have other comments you’d like to share, you always have our SSWUG FaceBook page , and the forums on our site. Or simply send your comments to btaylor@sswug.org to share with our readers.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Moving from Standard to Enterprise Edition, Step-by-Step
I was recently tasked with upgrading our production SQL Server Cluster from Standard Edition to Enterprise Edition. At first, I was surprised by how many posts that I saw online about how to do this assignment. I have even seen posts that suggest the upgrade can’t be done, and if you wanted to do it, you would need to reinstall from the ground up. A mistake on my side left me planning on a side-by-side upgrade.
Featured White Paper(s)
Query Tuning Strategies for Microsoft SQL Server
Written by Quest Software
When you’re looking for a reliable tool to diagnose … (read more)