I get asked quite frequently (just last week, for instance) if SQL Server will replace Access. As I've discussed here before, the question is wrong-headed: Access is a tool for creating database applications; SQL Server is just a database. A better question would be, "Is Jet going to be replaced by SQL Server?" This question is harder to answer. But I think that I've seen a new future for Jet in Access 2003.
If you're like most Access developers, you have a great fondness for Jet. And, while we here at Smart Access have been doing our best to prepare you for SQL Server, Jet is still your favorite database. Among other reasons for staying with Jet, you suspect that the learning curve for SQL Server is far more expensive than the payback. And, as Steven Forte puts it, "Sometimes you just need to mail a database."
For complex SQL Server upsizing projects, Garry Sells the MUST upsizing tool.
Find out more on upsizing from Access to SQL Server here http://www.vb123.com.au/up/
Still, it occasionally seems like Microsoft doesn't care as much about Jet as we Access developers do. You have the feeling that if you visited Microsoft's head office and asked if you could meet the Jet development team, someone would say, "Sure. Jeff, come over here and meet this Access developer." I've met Access developers who have talked with Microsoft and come away with the impression that the future belongs to SharePoint. That may all be true, but it doesn't make me want to give up what I can do with Access and Jet.
Strangely enough, the XML support in Access 2003 may mark the resurgence of the Jet database engine. If XML becomes as important to data activities as I think it will, the ability of Access to convert almost any XML document into data in a relational table is going to be important. Most of our business systems are based around relational data structures, not hierarchical XML structures. This isn't a trivial distinction: As C. J. Date and Hugh Darwen point out in The Third Manifesto, the relational data model is the most capable and flexible of all possible data structures. As XML becomes more important, converting XML data into relational data becomes an essential activity.
Of course, the reverse is true. If XML is how data will be communicated, then converting relational data to XML data will be equally essential. ADO has provided a way to save records in an XML format since early versions. Access has had the ability to save data in XML for a shorter time but has made XML conversions available from the Access user interface. Unfortunately, the ability to save data as XML has been of limited use because your data was saved in a very specific format. Almost every application that uses XML has its own peculiar XML format. Access's ability to save data in an XML format would only be useful if the data could be saved in the XML format required by other applications.
But that's only the tip of the iceberg. As I've discussed in two earlier articles, in Office 2003 both Word and Excel documents can be saved in an XML format (see "Creating Spreadsheets without Excel" in our September 2003 issue and "Access and Word 2003" in the August issue). This means that Jet can now become the central repository for all Office data. Word or Excel XML documents could be stored in Access as text fields, of course, but the results wouldn't be very satisfactory. However, XSLT stylesheets can convert Word and Excel documents into a format that's Access/Jet compatible. Similarly, XSLT stylesheets can convert relational data into XML formats that are Word or Excel compatible. There's the potential for Jet to become the ultimate repository for all data in all formats for the world's most popular desktop toolset.
Will you have to learn XSLT? Maybe, in the same way that you've learned SQL. However, you can also expect a set of standardized XSLT spreadsheets to become available (like the ones included with my article in this month's issue). You can also expect more powerful tools to appear that, in the same way the Access query designer helps you create SQL statements, will help developers create XSLT statements. The range of SQL knowledge among Access developers begins with "little-or-none" and goes all the way up to "expert," touching all the bases in between. With the appropriate tools, Access can support the same range of XSLT knowledge. Of course, like SQL knowledge and relational database design skills, the more you know about XSLT the more you'll be able to do and the easier it will be to build great applications.
And, if you love Jet, keep using Jet forever.