A backstage tour of SQL Server—related features
Microsoft Access 2002 is the version of Access that ships with Microsoft Office XP, and as we all know by now, XP stands for experience. When you're using Access projects in Access 2002, you might conclude that Access 2002 is Microsoft's second try to get the experience right—especially for SQL Server 2000. The experience is more friendly, and although Microsoft will undoubtedly continue to enhance Access in future versions, Access 2002 offers a broad range of new features that are likely to be significant for your organization if you're using or planning to migrate to SQL Server 2000.
Microsoft Access 2002 is the version of Access that ships with Microsoft Office XP, and as we all know by now, XP stands for experience. When you're using Access projects in Access 2002, you might conclude that Access 2002 is Microsoft's second try to get the experience right—especially for SQL Server 2000. The experience is more friendly, and although Microsoft will undoubtedly continue to enhance Access in future versions, Access 2002 offers a broad range of new features that are likely to be significant for your organization if you're using or planning to migrate to SQL Server 2000.
Access 2002 is compatible with SQL Server 2000, whereas Access 2000 requires remedial steps to work with SQL Server 2000. And Access 2002 integrates more tightly with SQL Server 2000 than Access 2000 does. In addition, Access 2002 introduces several new and improved visual designers and extended properties that enhance the power and simplicity of working with SQL Server from Access. If you want to start applying XML to accomplish some common database chores easily, Access 2002 introduces two new methods that dramatically simplify importing and exporting XML documents from SQL Server databases.
An Access project is a special kind of Access file that connects through an OLE DB connection to a SQL Server database. An Access project facilitates many SQL Server database definition functions (e.g., the creation of databases, tables, views, and stored procedures) and integrates tightly with Access forms, reports, and Web pages (which Microsoft calls data access pages). Access projects in Access 2000 require a couple of remedial steps for compatibility with SQL Server 2000. (For information about the steps needed for integration, see the Microsoft article "ACC2000: Incompatibility Issues Between Access 2000 Projects and SQL Server 2000" at http://support.microsoft.com/support/kb/articles/q269/8/24.asp.) However, Access 2002 is immediately compatible with SQL Server 2000 (as well as with SQL Server 7.0 and 6.5).
In Access 2002, the UI for the Access project has changed to accommodate new and improved functionality, which makes Access 2002 a better development tool for SQL Server than its predecessor. The UI changes are evident in menu options, the options available from the Database window, and visual designers for tables, stored procedures, and user-defined functions (UDFs).
Transfer or Copy Databases Between SQL Server Instances
Access 2002's Tools, Database Utilities command menu has two new items for copying SQL Server databases from one SQL Server instance to another: Transfer Database and Copy Database File. Both commands copy a database from one SQL Server instance to another, but they have different requirements and techniques. Both commands require the installation of SQL Distributed Management Objects (SQL-DMO) on the workstation that initiates a copy. Despite its name, the Transfer Database command doesn't remove a database from its original location. For a detailed comparison of the Transfer Database and Copy Database File commands, see the topic "About copying data and databases in an Access project" in Access Help.
Using the Transfer Database command is a fast, easy way to transfer all the database objects from one SQL Server instance to another—on the same computer or different computers. The source and destination computers must be running SQL Server 7.0 or later. You don't need a special role on the source instance of SQL Server, but you must be a member of the sysadmin role on the destination server.
Figure 1 shows an Access project's Data Link Properties dialog box. The Access project connects to the NorthwindCS database on the default SQL Server instance on the CABARMADA server. When you select the Transfer Database command, you see the Transfer Database dialog box that Figure 2 shows. This dialog box transfers the NorthwindCS database to the OFFICEDEVELOPER instance of SQL Server on the CAB2000 server. When you click Next, Finish, you launch the transfer of the NorthwindCS database. When the transfer is complete, the Access project remains connected to the NorthwindCS database on the CABARMADA server, and another copy of the database resides on the OFFICEDEVELOPER SQL Server instance on the CAB2000 server.
You can also use the Copy Database File command from the Tools, Database Utilities menu to copy databases between server instances. When you invoke this command, you copy the .mdf file from the database to which an Access project connects to a destination of your choice, which could be another computer or another folder on the same computer. Then you're responsible for attaching the copied .mdf file to another SQL Server instance. You can use T-SQL to attach the .mdf file to a server. For a code sample that you can use to accomplish this task, see sp_attach_single_file_db in SQL Server Books Online (BOL).
Prev. page
[1]
2
3
4
next page 
