• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 25. Creating Access Data Project... > Downsizing Databases with the DTS Wi...

Downsizing Databases with the DTS Wizard

Installing MSDE also installs SQL Server's DTS Wizard for converting Jet and other supported database types to SQL Server 7.0 databases. You might need to downsize an SQL Server 7.0 database to a Jet 4.0 .mdb file. If so, follow these steps:

  1. Create a new Access database, NwindDS.mdb in the \Shared\Nwind folder, and then close Access.

  2. Choose Start, Programs, Msde, Import and Export Data to open the first DTS Wizard dialog. Click Next.

  3. In the Choose a Data Source dialog, accept in the Source list the default Microsoft OLE DB Provider for SQL Server item, accept (local) in the Server list, type sa in the Username text box, leave the Password text box empty, and select the NwindSQL database from the Database list (see Figure 25.33).

    Figure 25.33. Specifying the server and database from which to downsize.

  4. Click Next to open the Choose a Destination dialog. Select Microsoft Access as the data source type from the Destination list, click the Browse button of the File Name text box, navigate to the \Shared\Nwind folder, and double-click NwindDS.mdb.

  5. Type Admin in the Username text box, and leave the Password text box empty if you&err146;re working from the unsecured version of System.mdw (see Figure 25.34). Otherwise, type an administrative login ID and password. Click Next.

    Figure 25.34. Specifying the Jet 4.0 destination database.

  6. Accept the default Copy Table(s) from the Source Database option (see Figure 25.35), and click Next.

    Figure 25.35. Directing the Wizard to copy the NwindSQL tables to NwindDS.mdb.

  7. In the Select Source Tables dialog, click Select All to copy all tables from the source to the destination database (see Figure 25.36).

    Figure 25.36. Selecting all tables for copying from the source to the destination database.

  8. Select a table and click the Preview button to verify that the Wizard has the moxie to do its job. Pay particular attention to the Freight field of the Orders table (see Figure 25.37).

    Figure 25.37. Previewing the Wizard's proposed data format for a downsized Orders table.

  9. The Freight field appears in Preview without the currency symbol, so close the Preview dialog and click the Transform button for the Orders table to open the Column Mappings and Transformation dialog. Scroll to the Freight column and verify that the destination field data type is Currency (see Figure 25.38). Click Cancel to close the dialog, and click Next.

    Figure 25.38. Verifying the source database data type for the Freight field of the Orders table.

  10. In the Save, Schedule and Replicate Package dialog, make sure the default Run Immediately check box is checked (see Figure 25.39). Click Next.

    Figure 25.39. Scheduling the downsizing operation for immediate execution.

  11. Review the Summary text in the Completing the DTS Wizard dialog by scrolling through the list of table names (see Figure 25.40), and then click Finish to start the downsizing operation.

    Figure 25.40. The DTS Wizard's almost final dialog.

  12. The Wizard provides a report of the completion of each step in the downsizing process (see Figure 25.41).

    Figure 25.41. The Wizard's downsizing progress report.

  13. Click OK to dismiss the message box announcing completion of the Wizard's work, and then click Done to close the Transferring Data dialog.

  14. Reopen NwindDS.mdb in Access and verify that all tables transferred intact and that the required number of records are present.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint