Xem mẫu

Microsoft SQL Server 2000 Desktop Engine 36.This creates the relationship with the Orders table, so now we`re ready to create the relationship with the Products table. To do so, click the New button. 37.Now change the Primary key table to show the Products table. Again, when you leave the field, the Selected relationship and Relationship name are automatically updated. The OrdersProducts table should still be showing as the Foreign Key table. Now select the ProductIdentifier in both the Primary Key and Foreign Key drop-down lists. At this point, you have created the relationship with the Products table too. 38.To see a list of existing relationships on the OrdersProducts table, select the Selected relationship drop-down at the top of the Relationships dialog to see all those currently defined: 25 Chapter 2 39.Notice that both the relationships we have just created appear in the drop-down list. Close this window and the OrdersProducts table by clicking on the cross in the top right corner of each so that you return to the tables view. If you are prompted to save table changes, click Yes. 40.Double-click on the Productstable and add some sample data. Remember that the ProductIdentifier field is generated by SQL Server, so you do not have to fill in this field yourself. Try adding the record without entering values for the required fields, just to see what happens. Then add a few valid records and close the datasheet view. 41.Then, open the Customerstable and add some new customers. Again, recall that you do not need to specify the CustomerNumber, as it is automatically generated by SQL Server. Try adding a record without specifying one of the required fields to see what happens. Then, add a couple of customers to the table and then close the datasheet view. 26 Microsoft SQL Server 2000 Desktop Engine 42.Open the Orders record and fill in some information there too. See what happens when you try to specify a CustomerNumber that doesn`t exist in the Customers table (the Foreign Key relationship we defined requires this value to exist in the Customers table before it may be added to the Orders table). Then, add a valid order that links to a CustomerNumber that does exist in the Customers table. 43.Last of all, open the OrdersProductstable and fill in some sample data. See what happens when you type in an OrderNumber or ProductIdentifierthat do not already exist in their respective main tables. Recall that we previously defined a Foreign Key relationship which requires that these values exist in their main tables (Orders and Products) before they may be used in the OrdersProducts table. Fill in some valid values designating existing OrderNumbers and ProductIdentifiers from their respective tables. Add the same OrderNumber multiple times with different ProductIdentifiers (more than one product per order) to verify that this is allowed. Congratulations! You have now successfully created a new database with four tables in your Desktop Engine / SQL Server database by using a Microsoft Access project. Now that we have learned the basics of database design and implemented our basic physical Desktop Engine database, we can begin working with a more complicated database that will be used for the majority of this book. We will use the Northwind sample database that comes with Access for this purpose. It is similar in many ways to the database we`ve just created, and has similar Products, Customers, Orders, and OrdersProducts tables. However, the Northwind database is more complex, contains additional tables, and is designed around the needs of a fictitious retailer called Northwind. In this next section, we look at how to use the Upsizing Wizard to convert the existing Northwind Access database into a SQL Server database. During this process, the wizard will automatically create a new Access project for us to link to the database. Once the Northwind database is converted to SQL Server, we will then be able to use it for the examples in the rest of this book. Upsizing an Existing Access Database to SQL Server 2000 Desktop Engine The Upsizing Wizard of Access 2000 and Access XP allows you to open an existing Access database and convert it to the equivalent SQL Server database. In this section, we are going to convert the sample Northwind database from Access to SQL Server. Once the conversion is complete, we will analyze the report produced to ensure that no errors occurred. We will then take a look at the resulting project file that allows us to view and manage the newly created SQL Server database. Note that Access 2000`s Upsizing Wizard works differently to the Upsizing Wizard in Access XP and, if you use the 2000 wizard, you may end up with slight differences in the resulting upsized database (such as different views, stored procedures, or functions). Try It Out – Upsizing the Northwind Database from Access to SQL Server 44.Open Microsoft Access XP. 27 Chapter 2 45.Select File | Open and browse to find the Northwind sample database (northwind.mdb) supplied with Access and click the Open button to open it up. A common location for this database file is C:\Program Files\Microsoft Office\Samples. This may not necessarily be its location on your machine if you installed the sample databases somewhere else. You can search for the file using the Start | Search menu option. If you still cannot find the Northwind database on your hard drive, then you can re-run Microsoft Access setup and install the sample databases. 46.Close the Main Switchboard of the Northwind application so that you are left with the database design view. 47.From the Tools menu, select Database Utilities | Upsizing Wizard, as shown below: 28 Microsoft SQL Server 2000 Desktop Engine 48.The Upsizing Wizard will begin, and the following screen will appear: 49.Select the Create new database option if it isn`t already selected and click the Next button so we can create a brand new SQL Server database from the existing Northwind Access database. 50.Fill in information about the SQL Server (which in our case will be the name of our Desktop Engine installation) that you want to create the new database on, as shown in the example below. A list of available servers should be in the drop-down list. In some cases, (local) will be the correct one if you are creating the database on a version of Desktop Engine on the current computer. For the User Id and Password, fill in information for a valid SQL Server user with create database permissions. By default, you can use "sa" (system administrator) with a blank password. Of course, when you have a database containing sensitive information, you really need to change these user details to something less obvious. Below the UserId and Password boxes, specify the name you want use when referring to the new Northwind database from Desktop Engine. I`m going to call mine NorthwindSQL and, to keep things simple, it`s a good idea if you do the same. Once all necessary information is filled in, click the Next button. Please note that, with Access 2000, you may receive an "Overflow" dialog box at this step. The solution is to apply a patch that can be downloaded from the Microsoft Web Site. There is a knowledge base article that discusses this issue. You can read this article at the following URL: http://support.microsoft.com/support/kb/ articles/Q272/3/84.ASP. 29 ... - tailieumienphi.vn
nguon tai.lieu . vn