Building the Database and Database Objects

In this section, you will build the database in an Access project, which stores the data tables in SQL Server, and the user interface in the Access project itself.

Tip 

You should have a version of Microsoft SQL Server installed in order to create this application. Please refer to Chapter 10 for more information on obtaining and setting up SQL Server.

Try It Out-Building the CustomerServiceSQL Database

Now, let’s build the Access Project and SQL Server database that will store the data tables. You will also build the SQL Server stored procedures used by your application.

1. Create a new Access project and SQL Server database by selecting the Office Button New. Click on the Browse button. Specify a file name of CustomerService, in the Save As type drop-down list select the Microsoft Office Access Projects (*.adp) option, and press the OK button. You will be returned to the original window; press the Create button.

2. Select “No” when Access asks you if you want to connect to an existing database.

3. The Microsoft SQL Server Database Wizard will appear, as shown in Figure 14-5. Specify the name of the SQL Server database on your computer. Also, identify whether Windows integrated security (Use Trusted Connection) should be used. If your SQL Server database is not set up for Windows integrated security, you must specify a valid SQL Server login ID and password. Finally, specify that the name of the SQL Server database is CustomerServiceSQL.

   
   Figure 14-5

4. Click the Next button, and then the Finish button. The wizard shows a dialog box detailing its progress for a few seconds while it creates the new database and then returns to the newly created Access project file.

5. The tables for this database are shown in Figures 14-6 through 14-9. Note that, for your convenience, some details about the primary key for each table are specified in the Description field. In each table, you also need to add primary keys as indicated by the key icon.

   
   Figure 14-6

   
   Figure 14-7

   
   Figure 14-8

   
   Figure 14-9

6. Add the tblCustomers table, as shown in Figure 14-6. This table is the master customers table for storing customer information. Note that the fields in this table map to the fields on the prototype screen for View/Manage Customer Accounts.

7. Add the tblCustomersPlanHistory table as shown in Figure 14-7. This table maps to the plan history fields on the View/Manage Customer Accounts.

8. Add the tblPlans table, as shown in Figure 14-8.

9. Add the tblPlansStatus table, as shown in Figure 14-9.

10. Add the data shown in Figure 14-10 to the tblPlans table. To do so, open the Database window, and select the table from the list of tables.

    
    Figure 14-10

11. Add the data shown in Figure 14-11 to the tblPlansStatus table.

    
    Figure 14-11

12. At this point, your CustomerService Access project should have the tables, as shown in Figure 14-12.

    
    Figure 14-12

13. Select the Create ribbon in the Database window to add the stored procedures. Select the Stored Procedure option in the Other group.

14. Select the Design ribbon View SQL View, as shown in Figure 14-13.

    
    Figure 14-13

15. Replace the entire default text of the stored procedure with the following code. This stored procedure is called spInsertCustomer and is responsible for inserting a new record into the customer database based on the values passed to it. The procedure also inserts an entry into the PlanHistory table to keep track of the history of the new plan that was just added.

      CREATE PROCEDURE dbo.spInsertCustomer (@LastName nvarchar(50), @FirstName nvarchar(50), @MiddleName nvarchar(50), @Company nvarchar(50), @Address1 nvarchar(100), @Address2 nvarchar(100), @City nvarchar(50), @Region nvarchar(50), @PostalCode nvarchar(25), @WorkPhone nvarchar(15), @HomePhone nvarchar(15), @CellPhone nvarchar(15), @Email nvarchar(50), @CurrentPlanId int, @RepName nvarchar(50)) AS DECLARE @CustomerId int INSERT INTO dbo.tblCustomers                       (LastName, FirstName, MiddleName, Company, Address1, Address2, City, Region, PostalCode, WorkPhone, HomePhone, CellPhone, Email,                        CurrentPlanID) VALUES     (@LastName, @FirstName, @MiddleName, @Company, @Address1, @Address2, @City, @Region, @PostalCode, @WorkPhone, @HomePhone,                        @CellPhone, @Email, @CurrentPlanId) SELECT @CustomerId = CustomerId FROM dbo.tblCustomers WHERE LastName = @LastName and FirstName = @FirstName and CurrentPlanId = @CurrentPlanId INSERT INTO tblCustomersPlanHistory (CustomerId, PlanId, RepName, DateChanged) VALUES (@CustomerId, @CurrentPlanId, @RepName, CURRENT_TIMESTAMP) 

    Tip 

    Because there are multiple statements in the spInsertCustomer stored procedure, you will not be able to view the stored procedure graphically from the Access Designer.

16. The next time you open the procedure, the CREATE PROCEDURE statement will be changed to an ALTER PROCEDURE statement, as shown in Figure 14-14.

    
    Figure 14-14

17. Add a stored procedure called spRetrieveAllCustomers. To do so, you can use either the designer or the SQL View. This procedure retrieves all customers from the database, such as when the View/Manage Customer Accounts is opened independently of the Customer Search screen.

18. To use the designer to create spRetrieveAllCustomers, from the Add Tables dialog box, select the tblCustomers table and click the Add button. Then, fill in the stored procedure design as illustrated in Figure 14-15.

    
    Figure 14-15

19. To specify the SQL code in Design ribbon View SQL View, add the following code:

      CREATE PROCEDURE dbo.spRetrieveAllCustomers AS SELECT     dbo.tblCustomers.* FROM       dbo.tblCustomers 

20. Add the stored procedure spRetrieveCustomerHistory, as shown in the Designer window of Figure 14-16 or in the code listing that follows. This procedure retrieves the customer history record that will be displayed on the View/Manage Customer Account screen in the Plan History list box.

    
    Figure 14-16

      CREATE PROCEDURE dbo.spRetrieveCustomerHistory (@CustomerID int) AS SELECT    dbo.tblCustomersPlanHistory.PlanID, dbo.tblPlans.PlanName, dbo.tblPlans.PlanDescription, dbo.tblCustomersPlanHistory.DateChanged,                       dbo.tblCustomersPlanHistory.RepName FROM         dbo.tblCustomersPlanHistory INNER JOIN                       dbo.tblPlans ON dbo.tblCustomersPlanHistory.PlanID = dbo.tblPlans.PlanID WHERE     (dbo.tblCustomersPlanHistory.CustomerID = @CustomerId) 

21. Add the stored procedure spRetrievePlans, as shown in the Designer window of Figure 14-17 or in the code listing that follows. This procedure retrieves a list of the active plans from the database to be displayed in the plan combo box on the View/Manage Customer Account screen.

    
    Figure 14-17

      CREATE PROCEDURE dbo.spRetrievePlans AS SELECT     dbo.tblPlans.PlanID, dbo.tblPlans.PlanName FROM         dbo.tblPlans INNER JOIN                       dbo.tblPlansStatus ON dbo.tblPlans.StatusID = dbo.tblPlansStatus.StatusID WHERE     (dbo.tblPlansStatus.StatusDesc = 'Active') 

22. Add the stored procedure spRetrieveSelectedCustomer, as shown in the Designer window of Figure 14-18 or in the code listing that follows. This procedure retrieves information for the selected customer from the database.

    
    Figure 14-18

      CREATE PROCEDURE dbo.spRetrieveSelectedCustomer (@CustomerId as Integer) AS SELECT     dbo.tblCustomers.* FROM         dbo.tblCustomers WHERE dbo.tblCustomers.CustomerID = @CustomerId 

23. Add the stored procedure spUpdateCustomer, as shown in the code listing that follows. This procedure updates an existing customer record and also inserts a corresponding history record into the PlanHistory table if the plan has changed.

      CREATE PROCEDURE dbo.spUpdateCustomer (@CustomerId int, @LastName nvarchar(50), @FirstName nvarchar(50), @MiddleName nvarchar(50), @Company nvarchar(50), @Address1 nvarchar(100), @Address2 nvarchar(100), @City nvarchar(50), @Region nvarchar(50), @PostalCode nvarchar(25), @WorkPhone nvarchar(15), @HomePhone nvarchar(15), @CellPhone nvarchar(15), @Email nvarchar(50), @CurrentPlanId int, @RepName nvarchar(50)) AS DECLARE @OldPlanId  int SELECT @OldPlanId = CurrentPlanId FROM dbo.tblCustomers WHERE CustomerId = @CustomerId UPDATE    dbo.tblCustomers SET              LastName = @LastName, FirstName = @FirstName, MiddleName = @MiddleName, Company = @Company, Address1 = @Address1, Address2 = @Address2, City = @City, Region = @Region, PostalCode = @PostalCode, WorkPhone = @WorkPhone, HomePhone = @HomePhone,                       CellPhone = @CellPhone, Email = @Email, CurrentPlanID = @CurrentPlanId WHERE CustomerId = @CustomerId IF @OldPlanId <> @CurrentPlanId BEGIN   INSERT INTO tblCustomersPlanHistory (CustomerId, PlanId, RepName, DateChanged) VALUES (@CustomerId, @CurrentPlanId, @RepName, CURRENT_TIMESTAMP) END 

24. At this point, you should see the stored procedures listed in Figure 14-19 in the database window of the Access project.

    
    Figure 14-19