Moving from Jet to SQL Server Page Data Sources

Creating a DAP with SQL Server 2000 as the data source follows the same process as DAP that connect to Jet 4.0 databases. The only difference is that you create the page in an .adp file instead of an .mdb file, and you use views, functions, or stored procedures in place of Jet queries.

The NorthwindCS.adp project doesn't include sample DAP, but you can convert existing pages to work with SQL Server 2000 databases that contain the tables and queries on which your pages depend. The most common approach is to import the PageName_cs.htm files, which the Upsizing Wizard creates, into an Access project, but you also can change the data source for pages linked in an .mdb file.

To import sample pages based on Northwind.mdb tables and queries into NorthwindCS.adp as an example, do the following:

  1. graphics/data_access_using_wizard.gif Open NorthwindCS.adp and click the Database window's Pages shortcut. Alternatively, open the .mdb file for the page, open the page to convert in Page Design view, and skip to step 4.

  2. Click the Edit Web Page That Already Exists shortcut to open the Locate Web Page dialog.

  3. Double-click any sample PageName_cs.htm file in the ...\Office11\Sample folder such as Review Products.htm to close the dialog and open the page in Page Design view. Using the PageName_cs.htm file prevents overwriting the original PageName.htm file.

  4. Right-click the page's title bar, choose Page Properties to open the Page: PageName window, and click the Data tab.

    Note

    Importing the page into a project doesn't alter the ConnectionString property value that points to the pages Jet data source ...\Samples\Northwind.mdb, for this example.

  5. graphics/builder.gif Click the Builder button to open the Data Link Properties dialog, and click the Provider tab.

  6. Select Microsoft OLE DB Provider for SQL Server in the list, and click the Next button.

  7. In the Select or Enter a Server Name text box type the name of the server or select the SQL Server instance from the drop-down list. This example uses the OAKLEAF-XP1 instance.

  8. Select the Integrated Windows Security option.

  9. Select NorthwindCS in Select the Database on the Server list, click Test Connection, and click OK to dismiss the "Test connection succeeded" message.

  10. Click OK to close the dialog and change the ConnectionString property value to point to the NorthwindCS database.

  11. graphics/subform.gif Change to Page view and confirm that the page behaves as expected.

  12. Choose File, Save As, and accept or change the default link name. Click OK to open the Save As Data Access Page dialog.

    Tip

    Change the page name in the Save As dialog if you're using the.mdb file to avoid overwriting the original link.

  13. Give the page a different name such as ReviewProductsSQL.htm to avoid overwriting the original version, and click Save to save the page with the SQL Server connection.

Tip

If you've deployed Jet back-end databases specifically for use as data sources for DAP, use the Upsizing Wizard to create a new SQL Server database from the tables and queries of the .mdb file.




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