Changes

THERE are a number of technology changes in the wind for Access developers, and they can be summed up in two phases: ADO.NET and Access 2002. It’s important to remember that none of these technologies make your current tools obsolete. You don’t have to upgrade to Access 2002, and you don’t have to install ADO.NET.

That might not sound like the typical advice from the editor of a technology-driven newsletter. However, technology should be your servant, not your master. For any particular technology, you should move to the tool only if it offers you real benefits in your current and future applications. The benefits are relatively easy to enumerate: increased developer productivity, greater stability, and more functionality for your clients. What does drive you to change technology, more often than not, are changes in the business that you support.

I’ve seen the next version of Access, and I’m impressed. Access 2002 impresses me because it does a much better job of moving from desktop to client/server applications. If you’re like me, you started creating Access applications using the Jet database. It was easy; it was fast; and you could create powerful applications very quickly. Jet remains the best of the “desktop databases,” the natural heir of dBase and Paradox. Over time, though, Jet has become less acceptable for production systems.

There are some inherent limitations in using Jet for systems that people depend upon. With Jet, every user on a separate computer loads their own personal copy of the Jet database engine. This means that a single MDB file is being beaten up on from many different sources. In this environment, there’s a whole bunch of things that you can’t do. Coordinated transaction management is out of the question, for instance, because a single transaction log can’t be maintained by all of the database engines. The absence of a single transaction log and the presence of multiple database engines limits your ability to manage database integrity and places an upper limit on performance.

As companies have become more and more dependent on their computer systems, and as the expectations that users have about those system have increased, desktop databases have become obsolete.

You’re going to have to move to creating client/server databases in the future.

With client/server databases, all of the copies of Access that share data send their requests to a single database engine, be it SQL Server or Oracle or anything else. That single database engine manages the data file and co-ordinates all requests from all clients. The engine can maintain a single transaction log, and performance can be optimized on the server computer. More importantly, the database engine typically runs on the same computer as the database file. As a result, data doesn’t have to be pulled over the network for most processing activities. In one test that I ran, switching from Jet to a client/server database reduced network requests by 66 percent and network data transfers by 75 percent.

What does this have to do with Access 2002 and ADO.NET? Both are technologies that are designed to work well in distributed environments. The big news for client/server development in Access is the change in Access Data Projects (ADP)—the Access client/server environment for SQL Server. Microsoft has been working very hard to make the ADP environment look as much like Jet as possible. The table design window now no longer looks like a visitor from another planet. You can set Cascade Delete in a Relationship without having to write a trigger.

I’m not going to tell you that working in an ADP is just like working with Jet. Client/server application development is different from desktop development, and the development environment has to reflect that. There are more and different things to learn. The fundamental principles don’t change but breaking them might be more painful. If you were basing your form on a table and using a filter to limit the display to the one record that you want—well, it’s foolish with Jet and probably fatal with SQL Server.

Access can still be your rapid application development tool for database applications. But I suspect that you’re going to have to change the kind of database that you’ll use in the next couple of years. It’s not hard. In fact, it’s kind of fun. And if your business pushes you to that change, Access 2002 might be a good choice for you. We’ll be reporting on Access 2002 over the coming months to help you make up your own mind.

About Garry Robinson

He is the guy who originally put this Smart Access web site together. Head to www.gr-fx.com.au for more about him and his company. His main claim to fame is winning the Microsoft MVP award for services to MS Access from 2006 to 2018.
This entry was posted in Editorials. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.