| Microsoft designed ADP as the entry point for building Access front ends for client/server database back-ends. ADP also offer the opportunity for Access users and first-time developers to gain insight into the benefits and drawbacks of client/server computing. Technically, ADP aren't limited to use with SQL Server. As mentioned earlier, it's possible to link other RDBMS servers to SQL Server 2000. This chapter's content is limited to creating ADP that connect to MSDE and other SQL Server 2000 editions. The following two sections are of interest primarily to readers who are upgrading from Access 97 or 2000 to Access 2003. Access 2003 makes no significant changes to Access 2002 ADP. Access 2000 Features Added for SQL Server 7.0 Conformance Previous releases of Access required modifications to assure compatibility with the then-current version of SQL Server. Providing an upgrade path to ADP required Access 2000 to add the following features to support SQL Server 7.0: Unicode support for localization Unicode defines a two-byte character set for all languages supported by Windows 98, Me, NT, and 2000. Using two bytes per character for all languages eliminates the need for double-byte character set (DBCS) installation for pictographic languages, such as Chinese and Korean. Jet uses Unicode compression to minimize the increase in the size of databases having large, text-heavy tables. MSDE lets you choose between conventional code pages and Unicode, but doesn't offer Unicode compression. Decimal data type Jet 4.0's Decimal data type corresponds to the SQL Server decimal numeric data type that lets you set specific precision and scale values. The precision is the maximum number of digits in the number; scale is the maximum number of digits to the right of the decimal point. Jet 4.0's maximum precision or scale is 28, which is the default maximum precision for MSDE and SQL Server; MSDE and SQL Server support precision or scale up to 38, but you must start MSDE or SQL Server with the /p command line parameter to get the extra 10 digits. ANSI SQL conformance Jet 4.0 offers two SQL syntax flavors a new version more compliant with ANSI-92 and T-SQL, and a backward-compatible (legacy) version that supports prior versions of Access, Jet, and VBA. You can set SQL Server Compatible Syntax (ANSI 92) for the current database by choosing Tools, Options, and opening the Tables/Queries page.
Caution Specifying SQL Server Compatible Syntax for existing Jet databases causes all queries that use Jet wildcard characters (* and ?) to fail. T-SQL and ANSI-92 SQL use % to represent all characters and _ to represent a single character. You must use ANSI-92 wildcards in queries you execute from VBA code in ADP, but it isn't necessary to mark the SQL Server Compatible Syntax check box to do this.
New SQL Server 2000 Features to Support Access 2002 and 2003 ADP Chapter 1, "Access 2003 for Access 97 and 2000/2002 Users: What's New," includes descriptions of new SQL Server 2000 features that apply to ADP, so the following list provides only a brief summary: Extended properties support lookup fields, subdatasheets, master-child table relationships, text for data validation messages, data-entry masks, and column formatting. Functions complement views and stored procedures that return Recordsets. You can bind forms, reports, and pages to tables returned by functions. Updatable, sortable views and functions correspond to updatable Jet QueryDef objects. If you add a TOP 100 PERCENT modifier to the CREATE VIEW or CREATE FUNCTION statement, you can sort the view or function with an ORDER BY clause. Cascading updates and deletions bring SQL Server's declarative referential integrity features to parity with Jet 4.0. Copying or moving databases is simplified by SQL Server's Copy Database Wizard. ADP provide similar capabilities when you choose Tools, Database Utilities, Copy Database or Transfer Database.
Later sections of this chapter cover all the preceding new features. The emphasis of this chapter is on the ADP user interface. The next chapter, "Moving from Jet Queries to Transact-SQL," delves into the T-SQL code that supports these features.  For additional details on items in the preceding list, see "New SQL Server 2000 Features," p. 34.
Exploring the NorthwindCS Sample Project Access 2003 includes a sample project, NorthwindCS.adp, which emulates Northwind.mdb. NorthwindCS.adp uses a T-SQL script, NorthwindCS.sql, to create a new MSDE database. A T-SQL script is a text file containing a series of T-SQL queries and other instructions to be executed by SQL Server. You must have MSDE installed or network access to another SQL Server 2000 instance to run the NorthwindCS project. The term instance refers to a particular installation of SQL Server 2000, because SQL Server 2000 lets you install more than one server on a single machine. Follow these steps to install the NorthwindCS database on your local computer: Open the NorthwindCS.adp project in the ...\Office11\Samples folder. If you haven't started MSDE, VBA code in the Startup module starts MSDE for you. The first time you open the project, you receive the message shown in Figure 20.1. Click OK to run the NorthwindCS.sql script on your local MSDE instance. Figure 20.1. You receive an Install Database message when opening NorthwindCS.adp for the first time after installing MSDE from the Office 2003 distribution CD-ROM.  After a few seconds, you receive a "Created database on SQL Server" message. Click OK to close the message and open the Northwind Traders splash screen. Mark the Don't Show This Screen Again check box, click OK to get rid of the splash screen, and close the Main Switchboard. The Database window's Tables page appears as shown in Figure 20.2. Figure 20.2. Table3 icons for the SQL Server database don't have the link arrow of Jet front ends linked to SQL Server with ODBC. 
The Database window pages for Access Data Projects differ considerably from the conventional Access Database window with linked table connections. The most important alterations are as follows:  Tables stored in SQL Server databases appear in the Tables page as though they are local tables. The right-pointing arrow symbol, which indicates a linked table of any type, including client/server tables, is missing. Opening an SQL Server table, such as Orders, in Datasheet view displays the same lookup fields and subdatasheet views as its Jet counterpart.
 Queries replaces Access 2000's Views and Stored Procedures items in the Objects list. The Access 2003 Queries page displays saved views, functions, and stored procedures (see Figure 20.3).
Figure 20.3. The Queries page includes all views, functions, and stored procedures in the SQL Server database. The NorthwindCS database created by NorthwindCS.sql doesn't include functions.   Views (in the Queries page) use the Access select query symbol, because view most closely correspond to simple Jet QueryDefs.
 Functions (in the Queries page) that return tables are similar to views, but support parameters. T-SQL Views and functions require an explicit field list to support SQL Server's extended properties. If your query contains SELECT * FROM TableName, subdatasheets and lookup fields don't appear in the view or function. Recordsets returned by stored procedures don't support extended properties. |
 Stored Procedures (in the Queries page) execute parameterized and action queries as precompiled Transact-SQL statements. Stored procedures provide a substantial performance improvement over direct execution of complex SQL statements.
 Database Diagrams serve the same purpose as Access relationships (and have the same icon) but differ considerably in their visual presentation.
The remaining Access application objects forms, reports, pages, macros, and modules are identical, with a few exceptions, to the corresponding objects of conventional Access applications that employ .mdb files for storage. The exceptions primarily are minor changes to form and report properties; as an example, you can set the Record Source property of a form or report to a view, function, or stored procedure. Note The primary difference between the Access 97 and 2000 or 2002 file structure is that Access 200x stores all application objects (forms, reports, pages, macros, and modules) in individual streams of a single compound document file, called a docfile. A conventional Access application saves its application object docfile within the application .mdb; an Access Project stores its docfile directly on disk as an .adp file.
|
