Figure

A progress bar as tables are upsized.

Upsizmg Wizard

m

Exporting index 'ShippersOrders'...

Tables: «/10 [ jcan^j ]

Finally, an Upsizing Wizard report is created and opened in Print Preview; you can print this report to get detailed information about the upsizing process, with lists of table fields for the tables that were successfully upsized, and a report on the error that prevented upsizing for any tables that could not be upsized. Figure 18.37 shows the page for the tblCategories table, listing the Access and SQL Server fields in two columns for comparison.

FIGURE 18.37

The Upsizing Wizard report.

FIGURE 18.37

The Upsizing Wizard report.

The new SQL Server database is created in the SQL Server data folder, typically C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. The database has the .mdf extension, and there is also a matching transaction log file with the .ldf extension. The Access front end now has two sets of tables: the original (pre-upsizing) Access tables, renamed with the suffix _local, and the linked SQL server tables, as shown in Figure 18.38. The SQL Server tables are indicated by the arrow and globe icon.

FIGURE 18.38

Linked SQL Server tables in the upsized Access database.

FIGURE 18.38

Linked SQL Server tables in the upsized Access database.

In the Access front-end database, forms and reports should work as with local Access tables. The Orders form is shown in Figure 18.39, displaying data from the linked SQL Server tblOrders.

Two of my original reports, however, wouldn't open, with Error 3219: Invalid operation. This turned out to be because the reports (or their data source queries) used the FromDate() and ToDate() functions. I made copies of the queries and reports without these functions; you can compare rptInvoices and rptInvoicesDateRange, and rptEmployeeSalesByCountry and rptEmployeeSalesByCountryDateRange, in the Basic Northwind.accdb database.

An Access form displaying data from a linked SQL Server table.

OrdeilD: | 1024S| Order Date: 04-Aug-1994 Required Date: 01 -Sep--1994 Shipped Date: 16 Aug 1994

Unit Price- , Quantity ,

Pwduet

i$14.00

12

Si®

10

#

$98 oO;

£34.00

5

te

f 174 00

*

OSS

Total:

4440.00

™ 38

Í47Z-3B

OrdeilD: | 1024S| Order Date: 04-Aug-1994 Required Date: 01 -Sep--1994 Shipped Date: 16 Aug 1994

Creating a Client/Server Application

Compared with just converting the tables to SQL Server tables, and linking the Access database to them as a front end, creating an Access Project as a front end has several advantages: You can make design changes to SQL Server tables and views, some of which can't be edited from an Access front end. You can also create, edit, and use other SQL Server objects, such as database diagrams, stored procedures, and user-defined functions. In a linked Access front end, by contrast, you can't make design changes to any SQL Server objects, and you can only link to SQL Server tables and views.

To create a client/server application from an Access database, with an Access Project (.adp) as the front end to the SQL Server back end, proceed as in the previous section until you reach the screen offering a choice between creating a client/server application or linking to the SQL Server back end (this screen is shown in Figure 18.40); in this case, select the client/server application option, and edit the name of the new project as desired.

FIGURE 18.40

Selecting the client/server option for upsizing an Access database.

FIGURE 18.40

On the next screen, shown in Figure 18.41, you can choose whether to open the new ADP file, or keep the old Access database open.

0 0

Post a comment