THERE’S one question that I get asked a lot when I’mworking with Access developers: I call it “The Question.” Back in December, forinstance,I was in England and was invited to drop in and do a presentation atthe London Access User Group. I had a great time (thanks, people!). The firstquestion I was asked was The Question. The next month, I was spending threedays with a corporate client who was moving from desktop to client/serverdevelopment. Before the end of the first day, I was asked The Question. TheQuestion is:

Will SQL Server replace Access?

The short answer is “No.” However, I have a page tofill, so there’s also a long answer.

To appreciate why SQL Server won’t replace Access, youneed to be clear on what each product does. Access is a tool for creatingdatabase applications rapidly. A tool for creating database applications isn’tmuch good without a database management system, so Access has always come withat least one DBMS. Since Access 1.0, Access has come with the Jet DBMS, aperfectly good desktop product. In fact, each upgrade of the Jet DBMS coincidedwith 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 somewhereto store the objects that you created (the forms, reports, macros, andmodules). Access stored those outputs in a Jet database, along with the Jetobjects (table and query definitions) and the application’s data. Again, it washard to tell Jet and Access apart. That’s what Access is and why it gotconfused with a DBMS like Jet.

SQL Server is a database management system. Now thatthat’s taken care of, you can see why SQL Server won’t replace Access. SQLServer needs frontend development tools like Access to be really useful. Accessneeds back-end tools like Jet and SQL Server to be used at all.

Could the SQL Server package be enhanced to be its ownfront-end development tool? Sure: Add a report writer, a forms designer, a codedevelopment package, and—hold it—isn’t this Access? Microsoft’s strategy seemsto be that, rather than develop a new front-end development tool for SQLServer, Microsoft is going to make Access work well with SQL Server. It’salways been possible to use SQL Server (or any other DBMS) with Access.However, Access, Jet, and DAO all stood between Access and SQL Server, imposingenough overhead to make SQL Server a poor choice for Access developers. Access2000 and 2002 provided a new way of working with SQL Server through Access DataProjects (ADPs). With those versions, Access also started shipping with twoDBMS systems: Jet and a cut-down version of SQL Server called the MSDE (now SQLServer Desktop Edition).

SQL Server is clearly a better database than Jet foralmost any purpose. With Jet, every user got their own copy of the databaseengine; with SQL Server, a single database engine handles all users. Benefitsinclude better performance, better security, better transaction management, andmore.  ome things are easier to do with Jet (for example, try mailing someone aSQL Server database), but, for almost everything else, you’re better off withSQL Server. So why aren’t we all building ADPs? Why are most Access developersstill building Jet applications? I think that there are two answers.

The first answer is pretty simple: Until Access 2002and SQL Server 2000, Access 2002 wasn’t as good a development platform as“Access-with-Jet.” While there might have been significant benefits at runtimewith SQL Server, creating SQL Server applications in Access 2000 was a pain.Access 2002 with SQL Server (especially with SQL Server 2000) makes creatingSQL Server applications almost as easy as Jet applications.

The second answer is also obvious: We’re all good atcreating Jet applications. Given a problem to solve with Jet, you probably havea pretty clear idea of where to start and where to go next. You know thestandard moves and the standard tools. With SQL Server, that’s not true. It’s adifferent world with different tools, processes, and attitudes required. If youdon’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 likemuch of a choice, especially if your client or manager is pressing fordelivery.

I’m not sure that we’ve been really helpful here atSmart Access. We’ve had a lot of good articles on how to do clever things withADP solutions. What we haven’t done is walk you through the process of creatingyour initial SQL Server application. So, with this issue, Martin Reid begins aseries of articles that will walk you through the process of creating an AccessData

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.