Moving Access to the Client/Server Model Access 2003's Access data projects (ADP), also called Microsoft Access projects or Access client/server applications, let you connect to the Microsoft SQL Server 2000 Desktop Engine (MSDE) with Service Pack (SP) 3 on your PC; on a peer server running Windows XP or 2000 Professional, or NT 4.0 Workstation with SP 4+; or on a network server running Windows 2003/2000/NT 4.0 Server. You also can connect to networked SQL Server 6.5 with SP 5, 7.0, and 2000 databases. As in the previous chapters of this book, the term SQL Server refers to any of these three SQL Server versions, plus MSDE 1.0 and 2000. SQL Server 2000 is used when discussing new features that aren't supported by its predecessors. Note Office 2003 is limited to installation under Windows XP and 2000+, but you can install MSDE 2000 on PCs running Windows NT 4.0 Professional or Server, and even Windows Me or 98. MSDE installed under Windows Me or 98 uses SQL Server (login ID and password) security. This book's examples use MSDE running under Windows XP Professional or Windows 2000 Server with integrated Windows authentication.
Following are the most important characteristics of ADP: Like upsized Jet applications, ADP rely on SQL Server tables, but they don't use .mdb files to store database front-end forms, reports, and other application objects. ADP store application objects in a single .adp compound document file (docfile). Unlike upsized Jet front-end .mdbs, the .adp file doesn't contain queries; SQL Server stores SELECT queries as views. A view is a precompiled SQL SELECT query, which replaces conventional Access SELECT queries saved as Jet QueryDef (query definition) objects. SQL Server stored procedures replace Jet action queries. Like views, stored procedures are precompiled queries, but stored procedures aren't limited to SELECT queries. Stored procedures are especially efficient at processing INSERT, UPDATE, and DELETE operations, and managing transactions. Project tool windows substitute for Jet's Table and Query Design windows. The da Vinci windows perform functions similar to but differ in layout from their Jet counterparts. Table and Query Datasheet views are almost identical to those for Jet back ends.  SQL Server 2000 offers user-defined functions (UDFs), which you can use to return the equivalent of a table to an ad-hoc query, view, or stored procedure. User-defined functions support SQL Server's new linked servers feature to connect to other client/server RDBMSs, Active Directory, Index Service, and Exchange 2000's Web folders. For example, you can connect ADP to an Oracle database linked to MSDE. You also can use UDFs to return scalar (character or numeric) values.
SQL Server 2000 uses extended properties to support Jet's lookup field and subdatasheet features, so you don't lose these capabilities when migrating to the client/server model. Extended properties also support input masks, captions, and data display formatting. SQL Server includes system stored procedures to add, read, and remove custom extended properties from the database. SQL Server 2000 has its own panoply of new Extended Markup Language (XML) features, which are independent of those offered by Access 2003. For example, you can write an XML file that contains a query and add an XML Stylesheet Language transform (XSL/T) to return data directly from SQL Server to a fully formatted table in a Web page. ADP dispense with Jet, Open Database Connectivity (ODBC), and Data Access Objects (DAO), substituting OLE DB data providers and ActiveX Data Objects (ADO) for database connectivity and data manipulation, respectively. OLE DB and ADO are the subjects of Chapter 30, "Understanding Universal Data Access, OLE DB, and ADO." You design ADP in Access's standard Form and Report views and use the standard Toolbox to add native Access and ActiveX controls to forms and reports. You can import Access objects other than tables and queries from existing Jet databases.
You can use the Upsizing Wizard to convert a conventional Access .mdb application to an Access project, instead of retaining the Jet front end. In the ADP environment, you also can create DAP bound to SQL Server databases. Access 2003 lets you convert simple forms and reports you design in the ADP environment to DAP. ADP are best suited to the following types of Access 2003 applications: Front ends to new or existing SQL Server databases. Access 2003 is an effective rapid application development (RAD) tool for client/server front ends. Applications that you expect to upsize to SQL Server 2000 Standard or Enterprise Edition in the near future or even long term. Microsoft has made it easy to migrate ADP from MSDE on your PC to SQL Server running under Windows 2000+/NT Server. Using ADP, rather than Jet, assures a quick and seamless transition from a local MSDE database to SQL Server 2000 or its successor codenamed "Yukon" when this book was written. Projects that use two-way SQL Server 2000 replication, rather than Jet-to-Jet or Jet-to-SQL Server replication, to synchronize multiple copies of the database. SQL Server replication is more robust and flexible than the Jet version.
Users of your Access project must have Access 2000 or 2002 installed, unless you use the Access 2003 runtime version to create a distributable version of your ADP. The runtime version of MSAccess.exe is a member of the Access 2003 Developer Extensions. If your Access project requires a local SQL Server database, users also must install MSDE. If the application connects to an SQL Server 6.5, 7.0, or 2000 database (not the Desktop editions), users must have the requisite client licenses for Windows 2000+/NT Server and SQL Server. Tip You can use a local Jet database with ADP client applications, but you must write VBA code to connect to the local database and manipulate its contents. You can use either DAO or ADO to make the connection to the local .mdb, but using ADO is much more efficient. If you use DAO to connect to the local .mdb file, clients must load both DAO and ADO, which consumes additional resources.
|
