In the Real WorldStrategic or Not?

In the Real World Strategic or Not?

ADP and SQL Server represent Microsoft strategic migration path to client/server applications. Only those technologies that Microsoft deems strategic receive significant development funding. As an example, when Microsoft decided in 1998 that SQL Server was its strategic database, Jet development ground to an immediate halt with the release of Access 2000. Jet 4.0 is the end of the line for this venerable database engine, other than for Windows CE developers. (The Windows CE version of SQL Server is based on Jet technology.)

Similarly, Data Access Objects (DAO) and Open Database Connectivity (ODBC) aren't strategic; DAO is terminal at version 3.6 and, when this book was written, the last update to the ODBC page on Microsoft's Web site (http://www.microsoft.com/data/odbc/default.htm) was March 17, 1999. Microsoft Data Access Components (MDAC), which include ActiveX Data Objects (ADO) and OLE DB, are strategic, but not as strategic as .NET. Office 2003 installs MDAC 2.7. You can expect MDAC to receive a few updates, whereas Jet, DAO, and ODBC languish in maintenance-only purgatory. Most of Microsoft's data connectivity efforts are directed to the .NET Framework and Visual Studio .NET.

Circumstances might dictate that you upgrade your Access application from Jet to SQL Server. A common reason for upsizing is that your application has become critical to the continued success of your organization, so management wants to put it under the aegis of the information technology (IT) department. In this case, you and the IT department must decide whether to link the Jet application to the server or upsize the front-end .mdb to an .adp. Most linked Jet applications consume several simultaneous ODBC connections per user to SQL Server, although ADP consume only one or two. Poorly designed Jet queries can require the server to send thousands of rows to clients when only one or a few rows suffice for the application. If your application has many users, it's likely that the SQL Server database administrator(s) will insist on the more efficient .adp approach.

If you're using Access as a RDBMS learning tool, and want to expand your weltanschauung from the desktop to the client/server world, Access 2003 is your best bet. The project designer makes it easy to design views, functions, and stored procedures based on SELECT statements. After you've mastered the transition from Jet SQL to T-SQL, you can experiment with writing increasingly complex stored procedures from scratch. Like a working knowledge of VBA, expertise in stored-procedure programming boosts your career potential. What's more, unlike the transition from Visual Basic 6.0 to Visual Basic .NET, T-SQL in the next version of SQL Server, codenamed "Yukon" when this book was written, is certain to be fully backward-compatible with SQL Sever 2000.