Access Data Projects and SQL Server 2000

It's clear that Microsoft envisions the future of Access as a general-purpose development tool for creating SQL Server-based applications. The inclusion of SQL Server 7.0 Data Engine (MSDE 1.0) with Access 2000 and the upgraded SQL Server 2000 Desktop Engine (MSDE 2000) supplied with Access 2002 and Access 2003's MSDE with Service Pack (SP) 3 provide the benefits of a robust client/server relational database management system (RDBMS) without per-user license fees.

MSDE is a distributable component that you can include with Access runtime applications. Runtime Access applications don't require users to have a copy of Access 200x on their machines, but you must purchase a license for the Access 2003 Developer Extensions, which is part of the Visual Studio Tools for the Microsoft Office System add-on, to create and distribute runtime versions of your applications.

New SQL Server 2000 Features

Microsoft offers six editions of SQL Server 2000: Enterprise, Standard, Developer, Personal, Windows CE, and MSDE. All SQL Server 2000 versions share the same basic database feature set, but the Personal, Windows CE, and MSDE editions don't include Analysis Services (formerly OLAP Services) for online analytical processing. The next section describes MSDE limitations.

Access 97 users upgrading to Access 2003 ADP must become familiar with Visual Studio 6.0's da Vinci database design tools for SQL Server, which emulate but don't duplicate Access's design tools for Jet databases. Members of Visual Studio 6.0, such as Visual Basic 6.0, also use the da Vinci toolset, whose name is derived from the beta version of the tools. After you learn SQL Server terminology for data types and T-SQL syntax for creating views, functions, and stored procedures, working with the da Vinci toolset also called the project designer is almost as easy as using Access's Jet design tools.

For more information on the da Vinci toolset, see "Working with SQL Server Tables in the Project Designer," p. 810.


graphics/2002_icon.gif

Much of Microsoft's SQL Server 7.0 to 2000 upgrade development effort went into conforming ADP and Jet functionality, which makes it easier for Access users to migrate from Jet to SQL Server back ends. Following is a list of the new SQL Server 2000 features that are of most interest to Access developers:

  • Extended properties enable SQL Server 2000 to support features previously restricted to Jet databases, such as lookup fields, subdatasheets, master-child table relationships, text for data validation messages, data-entry masks, and column formatting. When you run the Upsizing Wizard to move a Jet database to SQL Server, a new "Creating extended properties" message appears for each table you export.

  • Functions substitute for views and stored procedures that return a single Recordset. Unlike views, you can specify the name of an inline, table-returning function as the data source for a SELECT query. Functions also can return numeric and character (scalar) values.

To learn how to write SQL Server functions in T-SQL, see "Taking Advantage of In-line Functions," p. 825.


  • Updatable views and functions correspond to updatable Jet queries. The rules for creating updatable Jet queries now apply to data sources for ADP. If the query is updatable in Jet, it's usually updatable in SQL Server.

  • Full Declarative Referential Integrity (DRI) support for cascading updates and deletions, which finally matches Access's support for enforcing the referential integrity of Jet databases. In a da Vinci Database diagram, you right-click a relationship between two tables of the database, choose Properties to open the Properties dialog for the database, and click the Relationships tab. By default, DRI enforces relationships between primary and foreign key field values for replication, and INSERT and UPDATE operations. You can enable cascading updates and deletions by marking the two corresponding check boxes (see Figure 1.13).

    Figure 1.13. The Relationships properties page lets you set DRI properties for a relationship. In this case, referential integrity is enforced for replication, INSERT, UPDATE, and DELETE operations, including cascading UPDATEs and DELETEs.

    graphics/01fig13.jpg

To learn more about SQL Server database diagrams and how they differ from the Jet Relationships window, see "Diagramming Table Relationships," p. 835.


  • Copying or moving database (.mdf) and log (.ldf) files to another SQL Server installation (also called an instance) is simplified by the new Transfer Database dialog.

To learn how to copy or move an SQL Server 2000 database, see "Transferring the Project's Database to a Server," p. 839.


SQL Server 2000 Desktop Engine

MSDE shares executable code with SQL Server 2000, but Microsoft imposes restrictions on MSDE's use to prevent direct competition with the licensed versions of SQL Server. In the simplest terms, MSDE is a bare-bones, detuned version of SQL Server 2000. The only administrative tool that MSDE installs is the command-line Osql.exe utility, which replaces Isql.exe of earlier SQL Server versions.

For more information on using the OSQL utility, see "Adding User Logins with the OSQL Utility," p. 776.


The primary differences between MSDE and SQL Server 2000 Standard Edition are as follows:

  • Database files are limited to a maximum size of 2GB. SQL Server database files are limited by the amount of physical disk storage available to the server.

  • graphics/access_2002_new_icon.gif The Setup.exe program that installs MSDE is silent; the setup dialogs of the prior version no longer appear. You use Control Panel's Services tool, an Administrative Tools menu choice, to change the Windows XP/2000+ account under which the SQL Server and related services run.

  • MSDE allows a maximum of five simultaneous batch operations, a number that Microsoft considers acceptable for a shared departmental database. (A batch operation is execution of a query, view, function, or stored procedure.) SQL Server 2000's auto-tuning feature optimizes performance for hundreds or, with the Enterprise Edition, thousands of simultaneous connections. Batch operations execute very quickly on servers having 667-MHz or faster Pentium III+ CPUs and adequate RAM. MSDE can handle at least 25 simultaneous users running a combination of decision support and transaction processing applications.

  • The Northwind and pubs sample databases aren't included in MSDE. Access 2002's NorthwindCS ADP includes code that runs a T-SQL script (NorthwindCS.sql) to create the NorthwindCS sample database.

  • graphics/2002_icon.gif Installing MSDE doesn't add MSDE 1.0's Msde choice and its submenus to the Programs menu. The only server management tool installed by MSDE is the SQL Server Service Manager.

  • graphics/2002_icon.gif SQL Server 2000 supports up to 16 named instances of SQL Server on a single server, but MSDE doesn't. You can install a single named instance of MSDE if you want to continue to use MSDE 1.0 alongside MSDE. Multiple instances of SQL Server are especially useful for application service providers who rent time and storage space to organizations who want to outsource their RDBMS requirements.

For instructions on running MSDE 1.0 and 2000 side-by-side on your computer, see "Installing a Named Instance of SQL Server 2000," p. 1386.


  • English Query, a natural-language query generation tool, Analytical Services (OLAP), and Data Mining features aren't included.

  • SQL Server 2000 Enterprise Manager and other database management tools, such as Query Analyzer, aren't installed, nor is the Books Online documentation for SQL Server available.

Tip

graphics/globe.gif

You can download SQL Server 2000 Books Online (updated for Service Pack 3) from http://www.microsoft.com/sql/downloads/. Service Pack (SP) 3 was the latest SP for SQL Server when this book was written.


Microsoft licensing terms for the SQL Server management tools don't permit their use with MSDE unless you have an SQL Server 2000 license.

Changes to ADP Features

graphics/2002_icon.gif

Following are the primary differences, excluding the da Vinci toolset features described earlier, between ADP functionality in Access 2000 and 2003:

  • graphics/query.gif A single Queries button replaces the Views and Stored Procedures buttons in the Objects list of the Database window. Access 2003 represents SQL Server functions, views, and stored procedures as query subtypes.

  • The Linked Table Wizard lets you link SQL Server databases to other databases for which you have OLE DB providers. Linking creates an SQL Server view of the tables in the linked database. Access 2003 includes support for linking to Jet and other SQL Server databases. If you link to an SQL Server for which you have a per-seat license, you need a CAL for each user of the linked database. If you have the required OLE DB driver, you can link to Oracle, IBM DB2/Informix, Sybase, mySQL, and other client/server RDBMSs.

  • The Tools, Database Utilities, Transfer Database and Copy Database commands open wizards to move or copy an MSDE database and its log files from, for example, the local MSDE instance to another computer running any version of SQL Server 2000. These tools replace the missing Msde, Import and Export menu choice of MSDE 1.0.

  • You can change your login password if your connection to the database uses SQL Server security, which requires a username and password. You receive a message that you can't change the password if your connection uses integrated Windows security.

  • SQL Server Recordsets can serve as the Row Source property of combo and list boxes.

  • ADP supports disconnected Recordsets for any ADP object that has Record Source and Row Source properties. A disconnected Recordset is stored locally on the client (cached) and doesn't need to maintain a connection to the database server. Changes made to the cached data when disconnected apply to the database when the client reconnects. You must be proficient in writing VBA code to take advantage of disconnected Recordsets.

    Tip

    Using disconnected Recordsets as the Record Source for forms enable MSDE to support substantially more than 25 simultaneous users running ADPs that update SQL Server tables.


    For an example of VBA code that connects and disconnects Recordsets, see "Taking Advantage of Disconnected Recordsets," p. 1312.


  • MSDE installs with Windows integrated security as the user authentication method. By default, the local Administrator account is the only authorized SQL Server login and database user account. MSDE 1.0's setup program lets you choose between integrated security and SQL Server security. SQL Server security uses a password-protected sa (system administrator) account for database access and administration.

  • Access 2000's SQL Server Security and Replication dialogs are missing in Access 2003. Microsoft expects Access users to type arcane T-SQL statements at the OSQL prompt to add user (or group) logins and database user accounts, and then assign permissions to the accounts. Setting up SQL Server replication with OSQL is a thankless and almost impossible task.

graphics/power_tools.gif

The lack of graphical MSDE security tools is a serious impediment to widespread adoption of SQL Server as the back-end for Access 2003 client/server front ends. The User Login and Permissions Manager for MSDE 2000 tool, which is located in the \Seua11\UserMan folder of the accompanying CD-ROM, is a Visual Basic 6.0 program that lets you add SQL Server logins and database user accounts, and assign user roles for databases or apply specific user permissions to any database object. You can use the program with any MSDE or SQL Server 2000 instance for which you have system administrator privileges. Figure 1.14 shows the tool's Server Logins/Database Users page.

Figure 1.14. This Visual Basic 6.0 program lets you secure your ADP by adding server logins and database user accounts with built-in roles. The Database Object Permissions page lets you set specific permissions for a user account.

graphics/01fig14.gif

For User Login and Permissions Manager operating instructions, see "Securing Projects with the MSDE 2000 Login/User Tool," p. 936.


Live Web Reports

graphics/2002_icon.gif

You can export most Access reports to static XML-based reports, which are formatted to resemble their counterparts in report Preview mode. Static HTML reports are similar to report snapshot (.snp) files that users who don't have Access can open in the Snapshot Viewer; you must update static reports periodically to deliver current information. A live report displays current data and eliminates the need to manage the update process.

ADP have the capability to export live Web reports as ASP that run under IIS 5.0+. Exporting the Sales Total by Amount report to XML as Top100Orders with the Live Data and Run from Server options specified generates the live Web report shown in Figure 1.15.

Figure 1.15. The live Web report is a close facsimile of the original Access Sales Total by Amount report, except for the values of the Counter column. Live Web reports don't support the Running Sum property.

graphics/01fig15.gif

Note

In Access 2000, Microsoft abandoned the attempt made in Access 97 to generate ASP that emulate Access forms. If you export an Access 200x form to .asp, you get an HTML table containing the entire content of the form's underlying Record Source. Exporting tables or queries to .asp produces the same result.


To learn more about creating live Web reports, see "Exporting Live Web Reports," p. 967.




Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net