For a review of MSDE functionality and how it compares with both Jet and SQL Server, see the sidebar "MSDE Primer for Access 2000," January 2000, InstantDoc ID 7720, and SQL Server/Office Integration, "What Is MSDE?" February 2000. Because MSDE 2000 ships as part of Office XP, it requires no Client Access Licenses (CALs). Besides having the same file format for databases as SQL Server 2000, MSDE 2000 shares the same T-SQL syntax and SQL-DMO object model with SQL Server 2000. This compatibility makes upgrading to SQL Server 2000 easy. Some of MSDE 2000's disadvantages are that its performance starts to degrade after five concurrent user connections, it has a maximum database size of 2GB, and it supports only dual processors when running on Windows 2000 or Windows NT. You must evaluate whether MSDE 2000's advantages outweigh these disadvantages for your circumstances.
MSDE 2000 doesn't install as a standard part of Office XP. The Access Help topic "Install and configure SQL Server 2000 Desktop Engine" describes a four-step process for installing MSDE 2000. First, if MSDE is installed, uninstall it. Second, open the \MSDE2000 folder on the Office XP CD-ROM or from the network location for Office XP. Third, run the setup.exe file in the \MSDE2000 folder. Fourth, restart your computer to complete the installation.
In Win2K and NT, the installation process creates an instance of MSDE 2000 that runs automatically at startup. With other systems (e.g., Windows 98), you need to manually select the autostart service. Open the MSSQLServer icon in the Windows task bar. Make sure that the SQL Server Service Manager dialog box shows SQL Server as its selected service. Click Start/Continue. Before closing the dialog box, select the Auto-start service when OS starts check box.
If you've installed an earlier release of MSDE and you follow this four-step process to install MSDE 2000, you'll orphan any databases associated with the MSDE server. You can copy the database files from the \Data folder for MSDE to the new \Data folder for MSDE 2000. Next, attach your MSDE databases to the MSDE 2000 server by invoking the sp_attach_db system stored procedure. BOL contains T-SQL code samples that you can adapt to attach the abandoned .mdf and .ldf files. Note that the sample for sp_attach_db is different from the sample you use to attach the .mdf file that the Tools, Database Utilities, Copy Database File command copies. Recovering the old files calls for sp_attach_db instead of sp_attach_single_file_db because your orphaned files probably have log files associated with them.
When you install MSDE 2000 as the Access Help file describes on either a Win2K or NT system, the setup.exe file invokes Windows authentication by default (provided MSDE 2000 runs on a computer supporting that authentication style). If you use a stored procedure from an Access project to reattach your database files to the MSDE 2000 server, you'll have to log in with integrated security as a member of the Windows local Administrators group. (Figure 1 shows the selection of integrated security.) You can run the setup.exe file in the \MSDE2000 folder to enable SQL Server security and the sa login with a NULL password. Microsoft recommends that you change the password from NULL immediately after starting MSDE 2000. For details about how to use the switches, see topics 3.1.4 and 3.1.23 in the readme.txt file of the \MSDE2000 folder. By using switches, you remove the need to use a Windows login that's a member of the Windows local Administrators group when you attach the abandoned MSDE database files to a new instance of MSDE 2000.
Because MSDE 2000 ships with any Office XP release that includes Access 2002, you might wonder which Office XP edition will work best for your needs. If you're planning to use Access 2002 to build SQL Server 2000 solutions, Microsoft Office XP Developer (MOD XP) makes sense. For the reasons why I recommend MOD XP, see the Web sidebar "Special MOD XP SQL Server Features," which you can read on the SQL Server Magazine Web site at http://www.sqlmag.com, InstantDoc ID 22738.
Access 2002 XML Functionality
Access 2002 introduces new features that take advantage of innovations in XML. You can export from Access and import to Access from an XML document by using the World Wide Web Consortium's (W3C's) most recent recommendations. The schema file for the XML document has an .xsd extension. You can also generate an Extensible Stylesheet Language Transformations (XSLT) file to display the XML document in a tabular format on a Web page. The XSLT file has an .xsl extension. Access 2002's new XML processing capabilities apply to Access projects, so you can export and import data from SQL Server databases that include Access projects. Access lets you import and export data manually and programmatically. Although the manual approach makes processing XML data easy, the programmatic capability facilitates transformations that the UI can't accomplish between multiple XML documents and either SQL Server or Jet record sources.
Prev. page
1
2
[3]
4
next page 
