Working with SQL Server Objects from Access

The advantage of using an Access Project to work with SQL Server is that you can use various graphical designer tools from within Access to manipulate SQL Server objects. For example, you can view and create SQL Server tables, stored procedures, views, and functions. You’ll look at each of these in detail in the sections that follow.

SQL Server Tables

You can view and manage SQL Server tables from the Tables category of the Navigation Pane in your Access Project. Figure 10-11 is an example of a SQL Server table opened from within Access.

image from book
Figure 10-11

From looking at the data in Figure 10-11, you would not know that it physically resides in a SQL Server database. This screen looks very similar to the other screens you have seen before with local Access tables. Now, if you open a SQL Server table in design view, a screen similar to the one shown in Figure 10-12 appears.

image from book
Figure 10-12

The table designer is a bit different from the one you have worked with before. Notice how some additional columns for Length and Allow Nulls have been added. SQL Server also has some data types other than the standard Access tables.

Try It Out-Creating a New SQL Server Table

It’s your turn to try your hand at creating a new SQL Server table.

Create a new table, named tblProducts. To do so, select the Create tab and click on Table Design in the Tables ribbon. Fill in the table elements as shown in Figure 10-13.

Figure 10-13
After adding all the table columns and their respective data types, set the ProductId to the primary key. To set the primary key, select the ProductId field, right-click, and select the Primary Key option in the pop-up list.
Now, add some data to the table, such as that shown in Figure 10-14.

Figure 10-14

How It Works

The preceding example illustrates how to create a new table from the Access table designer that physically resides in the SQL Server database. You will now learn how to create stored procedures.

SQL Server Stored Procedures

Stored procedures are procedures that are stored in the SQL Server database. Their purpose is to enable you to take frequently used SQL statements and save them for reuse. You can then execute a stored procedure when you need it. A stored procedure is similar in concept to the VBA procedures and SQL statements you have written so far in this book. However, stored procedures (stored in the SQL Server database) are more efficient than passing SQL statements to the database on the fly because they are pre-compiled.

You can view stored procedures from the Queries category in the Navigation Pane. In fact, the Queries node will display all stored procedures, views, and functions that are stored in the SQL Server database. Figure 10-15 illustrates several stored procedures, as well as one view called titleview.

image from book
Figure 10-15

When you open the byroyalty stored procedure in Figure 10-15, a designer window like the one shown in Figure 10-16 appears.

image from book
Figure 10-16

Notice how a table is displayed as well as a grid showing the columns to be output when the stored procedure executes. You can view the SQL code for the stored procedure by clicking the SQL button in the lower right-hand corner of the window; a screen similar to the one shown Figure 10-17 will appear.

image from book
Figure 10-17

In the example of Figure 10-17, the stored procedure accepts a parameter called @percentage. When you call the stored procedure from VBA code, you must specify the parameter. You might call the stored procedure using the following code:

  Dim cmdCommand As ADODB.Command Dim prmPercent As ADODB.Parameter 'Create a new command object Set cmdCommand = New ADODB.Command cmdCommand.ActiveConnection = CurrentProject.Connection 'Specify the stored procedure to run cmdCommand.CommandType = adCmdStoredProc cmdCommand.CommandText = "byroyalty" 'Create the percentage parameter Set prmPercent = cmdCommand.CreateParameter("@percentage", adInteger, adParamInput) prmPercent.Value = 100 cmdCommand.Parameters.Append prmPercent 'execute the Stored Procedure cmdCommand.Execute

In the preceding example, notice how the ADO Command object is used to specify that a stored procedure should be executed. The ADO Parameter object is used to specify the parameters that should be passed to the stored procedure. The Execute method of the Command object is then executed to run the stored procedure.

Try It Out-Creating a New SQL Server Stored Procedure

Let’s create a new stored procedure in your Ch10CodeExamples Access Project.

Select the Create tab and then click on the Stored Procedure button on the Other ribbon.
Select the Add button on the Add Table window to add tblProducts. Click the Close button.
Fill in the columns and values as shown in Figure 10-18. The stored procedure outputs all but the discontinued column based on criteria where discontinued equals 1.

Figure 10-18
Save the stored procedure, and name it spDiscontinuedProducts.
Click on the SQL button to see the SQL statement for the procedure. A screen similar to the one shown in Figure 10-19 is displayed.

Figure 10-19
To run the stored procedure, select the View icon on the toolbar. A screen similar to the one shown in Figure 10-20 is displayed.

image from book
Figure 10-20

How It Works

In this example, you created a new stored procedure using the Access designer to specify the criteria for the procedure. You then viewed the SQL statement that Access created automatically from the criteria specified in the designer. You could have alternatively started with the SQL statement and Access would have generated the table and grid elements automatically from the SQL statement. You will see how to create a view in this manner momentarily.

SQL Server Views

Views are virtual tables that allow you to view information in a different way than you see it in the underlying tables. They are really just queries that have been saved to the SQL Server database that can be accessed in the same way as you would a table - only by specifying the view name instead of the table name. The capability to create an easily retrievable virtual table out of your most commonly used SQL statements is one advantage to using views.

Another advantage of views is that they provide the capability to implement row- and column-level security. Row-level security means restricting the values that a particular user can see down to the record level. Column-level security means restricting which fields a user can see. A common example of row-level security is the case where an employee can see his own personal information but not anyone else’s. A common example of column-level security is a case where no one outside of the Personnel Department is permitted to see any columns that contain personal information, such as salary, but employees can see the nonpersonal information, such as job title.

Figure 10-21 illustrates the design view that retrieves data from multiple tables in a database.

image from book
Figure 10-21

To view all three portions of the display shown in Figure 10-21, you must first click on the Design tab and select enable the Diagram, Grid, and SQL statement icons in the tool ribbon.

After a view has been created, you can reference it in all the places where you could otherwise use a table. For example, you could have an ADO recordset that is based on a SQL statement such as the following:

  SELECT * FROM titleview

The previous SQL statement causes the SQL behind the view (as shown in Figure 10-21) to run and to return the various columns selected. The view of Figure 10-21 will be similar to the screen shown in Figure 10-22 if opened in view mode from within Access.

image from book
Figure 10-22

Try It Out-Creating a New View

It’s your turn to create a view.

From the Database Window, click on the Create tab and then click on Query Wizard in the Other ribbon. Select Design View and click OK. Select the Close button to close the Add Tables dialog. Click the Design tab and select the SQL button on the tool ribbon to ensure that the SQL view is displayed. Add the following SQL statement:
```
  SELECT     ProductName, ProductDesc, UnitPrice FROM         dbo.tblProducts 
```
Select the Save button on the toolbar to save the view and name it vwProductPrice. Close and reopen the view in design view. Notice that from the SQL statement you typed that Access determines and displays the table diagram and column grid that corresponds to the SQL statement, as shown in Figure 10-23.

Figure 10-23
Select the Save button on the toolbar to save the view. Name the view vwProductPrice.

Select the View button on the toolbar to execute the view. A screen similar to that shown in Figure 10-24 is displayed to show you the result of running the SQL statement behind the view.

image from book
Figure 10-24

Tip

Do not confuse the View button on the toolbar with the concept of a view. The View button on the toolbar allows you to open various objects, including stored procedures, tables, and views to see the data. This is a different concept than a view itself.

How It Works

In the preceding example, you created a new view by starting with a SQL statement. Microsoft Access then updated the designer to display the table and grid to graphically represent the SQL statement. The SQL statement selects the ProductName, ProductDesc, and UnitPrice fields from the tblProducts table.

SQL Server Functions

SQL Server comes with many built-in functions such as RTrim, GetDate, and many others. You can also create user-defined functions and call those functions as if they were built-in functions of SQL Server. User-defined functions can return a single value, such as the result of a calculation, or they can return an entire table of results.

You may be wondering how functions differ from stored procedures. User-defined functions have a lot in common with stored procedures because both are just SQL statements stored on the SQL Server. However, a user-defined function, unlike a stored procedure, can be embedded within a basic SQL statement, such as the following:

  SELECT FormatDescription(ProductDesc) FROM tblProducts

In this instance, the function is called FormatDescription and will be called for each record selected in the SQL statement. If this code were contained in a stored procedure, a loop would have to be executed to call the stored procedure for each record.

When you open the designer from Access to add a Function, the Add Table dialog box appears, as shown in Figure 10-25. In this example, the function is based on the vwProductPrice view that you created in the prior example.

image from book
Figure 10-25

A designer similar to the other designers you have seen so far appears, as shown in Figure 10-26. In this example, the function will select the products that have a unit price greater than $500.

image from book
Figure 10-26

When you run the preceding function, all products that are greater than $500 are displayed, as shown in Figure 10-27.

image from book
Figure 10-27

At this point, you do not have to be an expert on determining when to use SQL Server stored procedures, view, or functions. It is a complicated topic that is beyond the scope of this introductory chapter. However, if you want to see more examples of how to use stored procedures, views, and functions in your applications, please consult the comprehensive case study in Chapter 14 that uses an Access Project with a SQL Server database.

Now that you have a basic familiarity of Access Projects, you can walk through the several steps involved in upsizing an existing Access database to an Access Project and SQL Server database.