Upsizing Wizard startup in Microsoft Office

Encoding QR Code in Microsoft Office Upsizing Wizard startup

FIGURE 2-12

FIGURE 2-13

to Microsoft SQL Server nonclustered, unique indexes, and marks them as SQL Server primary keys Validation rules, which can be set up on the table fields, are uploaded as update and insert triggers in SQL Server And, referential integrity set up for the table relationships can be uploaded either as triggers or by using declared referential integrity (DRI) Triggers are a good option if you are using the cascading update or delete capability of Access In this case we are, so that option will be selected Additional details on how Access handles the upsizing of table attributes can be found in the Access Help documents The next option relates to whether you want to keep Access built-in timestamp data that tracks when a record has changed In general, you probably do not want to keep this data unless you are going to manage timestamp data explicitly in the enterprise application The last option is to indicate if only the table structure should be uploaded or if data should be uploaded as well The fact that Access can upload data provides a powerful tool for manipulating data in Access and then easily exporting it to SQL Server In this example we have no data to upsize, so the option will not be selected Figure 2-14 shows the Table Attributes dialog box

FIGURE 2-14

The next dialog box provides options for working with the new SQL Server database An Access client/server application or ADP front end to the database can be created The tables can be linked in Access, or no application can be created In this case, we are only interested in the creation of the new database in SQL Server So, no application changes should be selected Figure 2-15 shows the dialog box The last dialog box simply confirms that you are ready to upsize the database Click Finish to start the process When the upsizing is completed, a report is generated that indicates what was upsized and how it was upsized Tables created, fields, and any triggers that are created are all detailed in the report Now let s take a look at what was created by going into SQL Enterprise Manager Or, alternately, you can view the database tables in Visual Studio NET if you do not have the SQL Enterprise tools Figure 2-16 shows Enterprise Manager with the Models database expanded Note the highlighted table fields

FIGURE 2-15

FIGURE 2-16

Select the ModelXREFColor table, right-click, and select Design Table Remember that the primary key of the table is the two fields combined Note the Key icon next to each field in Figure 2-17 Finally, let s look at one of the triggers created Remember that we enforced referential integrity on all of the tables And, for the Colors and Models tables, we enforced a cascading delete in ModelXREFColor if a model or color was removed Right-click on the Colors table and select All Tasks, then select Manage Triggers In the Trigger window that pops up, select the T_Colors_DTrig trigger Figure 2-18 shows the trigger If you look at the other tables, you will note that triggers are in place that ensure a foreign key cannot be entered that doesn t exist in the related table The last thing to look at is the translation of data types from Access to SQL Server All of the text values are converted to nvarchar with the equivalent character lengths The primary keys are all converted to int data types In general, there isn t too much to worry about when converting data to SQL Server Note that the Upsizing Wizard does convert all text to Unicode format