THERE’S one question that I get asked a lot when I’m working with Access developers: I call it “The Question.” Back in December, for instance,I was in England and was invited to drop in and do a presentation at the London Access User Group. I had a great time (thanks, people!). The first question I was asked was The Question. The next month, I was spending three days with a corporate client who was moving from desktop to client/server development. Before the end of the first day, I was asked The Question. The Question is:
Will SQL Server replace Access?
The short answer is “No.” However, I have a page to fill, so there’s also a long answer.
To appreciate why SQL Server won’t replace Access, you need to be clear on what each product does. Access is a tool for creating database applications rapidly. A tool for creating database applications isn’t much good without a database management system, so Access has always come with at least one DBMS. Since Access 1.0, Access has come with the Jet DBMS, a perfectly good desktop product. In fact, each upgrade of the Jet DBMS coincided with a release of Access— which made it difficult to tell Access and Jet apart. Adding to the confusion is that Access (the development tool) needed somewhere to store the objects that you created (the forms, reports, macros, and modules). Access stored those outputs in a Jet database, along with the Jet objects (table and query definitions) and the application’s data. Again, it was hard to tell Jet and Access apart. That’s what Access is and why it got confused with a DBMS like Jet.
SQL Server is a database management system. Now that that’s taken care of, you can see why SQL Server won’t replace Access. SQL Server needs frontend development tools like Access to be really useful. Access needs back-end tools like Jet and SQL Server to be used at all.
Could the SQL Server package be enhanced to be its own front-end development tool? Sure: Add a report writer, a forms designer, a code development package, and—hold it—isn’t this Access? Microsoft’s strategy seems to be that, rather than develop a new front-end development tool for SQL Server, Microsoft is going to make Access work well with SQL Server. It’s always been possible to use SQL Server (or any other DBMS) with Access. However, Access, Jet, and DAO all stood between Access and SQL Server, imposing enough overhead to make SQL Server a poor choice for Access developers. Access 2000 and 2002 provided a new way of working with SQL Server through Access Data Projects (ADPs). With those versions, Access also started shipping with two DBMS systems: Jet and a cut-down version of SQL Server called the MSDE (now SQL Server Desktop Edition).
SQL Server is clearly a better database than Jet for almost any purpose. With Jet, every user got their own copy of the database engine; with SQL Server, a single database engine handles all users. Benefits include better performance, better security, better transaction management, and more. Some things are easier to do with Jet (for example, try mailing someone a SQL Server database), but, for almost everything else, you’re better off with SQL Server. So why aren’t we all building ADPs? Why are most Access developers still building Jet applications? I think that there are two answers.
The first answer is pretty simple: Until Access 2002 and SQL Server 2000, Access 2002 wasn’t as good a development platform as “Access-with-Jet.” While there might have been significant benefits at runtime with SQL Server, creating SQL Server applications in Access 2000 was a pain. Access 2002 with SQL Server (especially with SQL Server 2000) makes creating SQL Server applications almost as easy as Jet applications.
The second answer is also obvious: We’re all good at creating Jet applications. Given a problem to solve with Jet, you probably have a pretty clear idea of where to start and where to go next. You know the standard moves and the standard tools. With SQL Server, that’s not true. It’s a different world with different tools, processes, and attitudes required. If you don’t know that world, you have a choice when confronted with a new problem: Build it with Jet or try to figure out how to do it with an ADP. Doesn’t sound like much of a choice, especially if your client or manager is pressing for delivery.
I’m not sure that we’ve been really helpful here at Smart Access. We’ve had a lot of good articles on how to do clever things with ADP solutions. What we haven’t done is walk you through the process of creating your initial SQL Server application. So, with this issue, Martin Reid begins a series of articles that will walk you through the process of creating an Access Data
Project. When we’re done, if you follow through the series, you should be as comfortable with creating an ADP application as you are with Jet.
And it’s time to get started with SQL Server.