CONVERTING EXISTING APPLICATIONS

After you decide to migrate an existing file-server application to client/server, you should review your data structure to ensure that you have a well-designed, properly normalized database design. Then you'll need to prepare any existing queries, forms, and reports for the migration.

Starting with a Well-Designed Database

When you're ready to convert your application to SQL Server, you should first step back and analyze your data model. Is it properly normalized? Do any changes need to be made before the migration?

Using Timestamp Fields

SQL Server and many other back-end servers support a timestamp data field. This field serves as a version column that the Access Jet database engine can use to determine whether a record has changed in a linked table. The absence of a timestamp necessitates a column-by-column comparison, which is less efficient than the alternative and causes unnecessary network traffic. Depending on the options you choose while upsizing, the Upsizing Wizard adds timestamp fields to SQL Server tables as needed.

Cleaning Up Queries

As mentioned earlier in the section “Using Access-Specific and User-Defined Functions,” you need to eliminate any Access or VBA specifics from your queries if you want them to run on SQL Server. But what if you really need to run that user-defined function on your data set and don't want to remove it from your query? The solution is to move the user-defined function down to the form level.

For instance, you might have a user-defined function called YourCommission() that calculates a complex commission amount for your sales force. You should eliminate this from the query and put the function on the appropriate form control. This is much more efficient; you still take advantage of the server data processing speed and then run the function in your front-end application.

The same holds true for reports. Remove any Access specificity from the query and move it down to the report level.

Unfortunately, even after removing all Access-specific functions, a query might not run on linked SQL Server tables and might lead to an ODBC call failed message. It's a good idea to test each query against the back-end server because it's possible that the query is still too complex for SQL Server. For instance, you might encounter a GROUP BY limitation of 255 characters. The easy solution here is to base some queries on queries to limit the GROUP BY clause.

Here are a few guidelines for query construction:

• Re-create indexes. Make sure that all indexes have been re-created on SQL Server for use by your queries. Any column used in a query JOIN should be indexed, as well as any column used for searches and in query WHERE conditions.

• Use equi-joins. These result in best performance if unique indexes are available.

• Avoid SQL keywords. If you're upsizing an application to an Access Project, the queries will be moved to server-side views and can't contain Jet syntax not known to SQL Server, including DISTINCTROW, PARAMETERS, and TRANSFORM.

• Avoid disjunctions and hard-to-optimize constructs. Using OR and IN constructs generally result in poor performance. NOT, <>, and != aren't useful to optimize for index selection.

Reworking Forms

Your form design is one area where you'll likely need to do the greatest rework in your conversion of file-server to client/server. Access makes it easy to build grandiose forms and subforms that are bound to your application's data. It's so easy: simply make a change on the form, and the underlying table is modified, too. The drawback of this approach is clear: The entire data set needs to travel across the network to populate the form. As your data set grows, this approach becomes less efficient and less practical, especially when you start talking about SQL-linked applications.

A better way to design forms is to severely limit the amount of data that's bound to the form. One approach is to provide users with a means of filtering the data, such as deciding what authors they want to view by last name. Figure 24.3 shows a series of toggle buttons that users can choose from. The selected letter of the author's last name will be used to restrict the data set.

This requires some VBA code behind the AfterUpdate event of the option group (see Listing 24.2). The code sets the record source based on your response and then unhides the detail and footer sections where you display the author information. If you want to test this, you'll need to attach to the authors table in the PUBS database that comes with SQL Server. This code can be found in Chap24.mdb on the CD-ROM that comes with this book.

Listing 24.2. Chap24.mdb: The AfterUpdate Subroutine for the Letters Option Group

Sub grpLetters_AfterUpdate()
    'Modify record source
    Me.Recordsource= "SELECT * FROM dbo_authors WHERE au_lname " & _
            LIKE '" & Chr$(Me![grpLetters]) & "*';"

    'Display selection
    If Not Me.Section(acDetail).Visible Then
         Me.Section(acDetail).Visible = True   'Show Detail section
         Me.Section(acFooter).Visible = True      'Show Footer section
         Me.NavigationButtons = True      'Show navigation buttons

         'Fit windows to form
         Docmd.RunCommand acCmdSizeToFitForm
    End If

End Sub

You should apply this same idea to your combo boxes to limit selection. To accomplish this, you can choose to replace your combo boxes with pop-up forms that provide a means of limiting data.

Developing Advanced Applications

After upsizing your data to SQL Server (see “Upsizing Access Databases” later in this chapter), you can take advantage of advanced SQL Server features to further improve the performance of your application. These include SQL Pass-Through queries, server views, and stored procedures.

Using SQL Pass-Through

The term SQL Pass-Through literally means that SQL statements are passed directly through to the server. SQL Pass-Through queries bypass any ODBC and Jet manipulation. They provide a good option for Access-to–SQL Server client/server development when using a SQL-linked application; because saved queries aren't available in an Access Project, you use stored procedures or VBA code against ADO to achieve the same result. SQL Pass-Through queries execute more quickly than queries against linked tables and are the means of tapping into SQL Server's stored procedures mentioned later in this chapter. SQL Pass-Through statements can be directed at any ODBC data source and aren't limited to SQL Server.

SQL Pass-Through queries generally are easier to debug because they're passed to the server unaltered. When queries are processed through ODBC, you need to consult some tracing mechanism to know what message is really passed to SQL Server.

Some costs are associated with using SQL Pass-Through. SQL Pass-Through queries are always non-updateable, but you can program updates to the underlying tables via VBA if necessary.

To create a SQL Pass-Through query, follow these steps:

Using Views

In Microsoft Access, developers often use a query as a form's record source to limit the amount of data presented and to enforce security. Queries set up as RWOP (Run With Owner's Permission) allow the end user to modify the underlying tables with the owner's permissions, even if the end user has no permission on the underlying table.

In SQL Server, you can consider a view as an Access query. Views, like queries, provide security by controlling what columns and rows of data the end user can see. For client/server applications, you need to pay particular attention to how much data travels across the network. You also need to remember that views are not updateable by default, unlike their Access counterparts. And you must include all fields that comprise the primary key if you want to make the view updateable.

To create a view in SQL Server, you have three options:

• Use the CREATE VIEW statement.
  
  
• Use the View Designer in an Access Project connected to a SQL Server database.
  
  
• Use the Manage Views window in the SQL Server Enterprise Manager. (Choose the database that you want to manage views for from the database objects list. Right-click on Views, choose New View from the shortcut menu, and enter a view statement in the View Designer.)
  
  

To create a basic SQL Server view by using SQL syntax, use the following code:

CREATE VIEW all_titles
AS
SELECT title, type, price
FROM titles

After the statement is executed, SQL Server creates the view. You can create more complex views to restrict which rows are returned. The following statement creates a view that shows only books priced over $20:

CREATE VIEW expensive_titles
AS
SELECT title, type, price
from TITLES
WHERE price > $20

You can actually link SQL Server views in your SQL-linked application and use them as though they were tables. If you want the ability to update the underlying table, you need to create an index for the view. If you attach a view in Access 2000, you're prompted to select the unique key for index creation. You can create other indexes as needed. To create a local index called CustID for a server view where the unique key is the field CustomerID for the Customers table, follow these steps:

In an Access Project, views are manipulated directly from the Database window, so you don't need to use SQL syntax to create, alter, or delete a view in a Project. In fact, if you need to create server views that are used by a SQL-linked application (.mdb file) as described earlier, you can create an Access Project connected to the target SQL Server database, and use the design tools in the .adp (Project) file to help with your database development.

Using Stored Procedures

As in Access, SQL Server has its own programming language, Transact-SQL, that's far more powerful than simple query execution. SQL Server has a control-of-flow language that allows you to program looping constructs, conditional statements, variable usage, and much more.

To optimize performance for your client/server application that uses SQL Server, you might want to take advantage of stored procedures. As a general rule of thumb, any process that your application runs frequently is a good candidate for a stored procedure.

A stored procedure is a precompiled set of SQL statements and control-of-flow language that greatly improves performance of your applications. One type of stored procedure is a trigger, which is ready to execute when a certain event happens, such as an insertion, deletion, or modification of data. Another type is a row-returning stored procedure, which returns one or more data recordsets to the calling application in much the same way as a server view.

A useful feature of stored procedures is that SQL Server can run them without involving the client machine, which isn't the case when your data is stored in Access. With Access, all processes must be completed by the front-end application.

The following sample code works with the pubs sample database and shows a stored procedure called myprocedure, which accepts two input parameters: @lname and @fname, the author's first and last name. By default, if you run myprocedure, you'll get a listing of all authors and their books who have a first name beginning with A and a last name beginning with R.

CREATE PROCEDURE myprocedure @lname varchar(35)='R%', _
       @fname varchar(15)='A%'
AS SELECT au_lname, au_fname, title
FROM authors, titleauthor, titles
WHERE au_fname LIKE fname
AND au_lname LIKE lname
AND authors.au_id = titleauthor.au_id
AND titles.title_id = titleauthor.title_id

In an Access application, stored procedures are created, modified, deleted, or executed by using SQL Pass-Through queries or VBA code. In an Access Project, stored procedures are manipulated directly from the Database window by using a stored procedure editor. In both cases, the stored procedure syntax is the same, but the editor and the model for binding to the database are slightly different.

Working with Current Access Security

If your existing applications have security implemented, security is another area that requires some conversion. Yes, your Access security might work, but it protects your data only if users access the data from your Access front-end application. When your data is on the server, you need to have it protected on the server for the same reason you secure data in your Access data database and not the end-user application.

SQL Server security was discussed earlier in the section titled simply “Security.” Although it takes some effort, you can re-create some or all of your Access data security on SQL Server. You might consider writing a function, relying heavily on ADO, that automatically reads in your current Access users and re-creates them on the server.

Groups, however, are a different issue. In Access, you can belong to as many groups as you want, but in SQL Server you inherit rights from your user account and the one or more roles assigned to it. A SQL Server logon maps to a specified user account, which gains you certain permissions. Permissions can then be grouped into roles, and you can be assigned multiple roles. This provides a model for fine-tuning database permissions, but because this model is different from Access's approach, it's almost assured that you will make some changes to your Access application's security after you upsize it.

Upsizing Access Databases

In general, when developers talk about upsizing their databases, they're referring to moving their data to a more robust and efficient back-end server, such as SQL Server. In Access 2000, after you move a database to SQL Server, you can connect to the data through a SQL-linked application or a Project (see the earlier section “Planning for Client/Server”).

The basic steps involved when moving to an Access application are as follows:

These steps are explained in more detail in the following sections.

Preparing to Upsize

Before you can upsize to SQL Server, you must have appropriate server permissions. To upsize into an existing database, you need CREATE TABLE and CREATE DEFAULT permissions for that database. If you will be creating a new database, you also need CREATE DATABASE rights on the server. If you want to create a new database, you must be granted CREATE DATABASE permission and SELECT permissions on system tables in the master database. If you want to use an existing database, you need only CREATE TABLE and CREATE DEFAULT permissions. You might need to have your SQL Server administrator set up a new device and database for your application tables. You also need the appropriate permissions in Access to proceed. For any object that you will upsize, you need read/design permission on that object.

Finally, the Upsizing Wizard saves its status report as an Access report snapshot. Therefore, you must have a default printer installed and specified on the computer that will be used to upsize (choose Printers from the Windows Start menu to do this).

Setting Up the ODBC Data Source

Before beginning the upsizing process, you need to ensure that ODBC is installed properly and then set up a data source for SQL Server. To set up the data source, follow these steps:

For some applications, you might want to set this up automatically for your end users. See the later section “Programmatically Setting Up an ODBC Data Source.”

Exporting Tables

The next step is to re-create your Access tables on the back-end server. You must first determine whether you can upsize to an existing database or if you need to create a new database on your server. If this is the first time you've upsized, you need to create a new database. Consult your SQL Server documentation for more information.

Next, identify which tables you want to upsize. You might decide to have some tables remain locally rather than on the back-end server, or you might want to have some temporary local tables that are populated periodically by SQL Server. See the earlier section “Using Local Tables for Static Information” for more information.

With Access 2000, you can export tables directly to an ODBC data source. This method is generally slow with large data tables, but ensures that your data adheres to all the server-based rules. Using bulk-copy routines is another option that's significantly faster for large data sets, but it doesn't bother with verifying your data.

Rebuilding Indexes and Relationships

Because indexing is critical to performance, you must also re-create any necessary indexes for your database. Be sure to index on all primary keys, foreign keys, and any field you believe will be used frequently for searches. Also, rebuild relationships between data tables as necessary. In SQL Server, this also includes creating triggers, as discussed in the next section.

If you aren't sure what indexes, defaults, and referential integrity rules exist on your Access database, use the Database Documenter to get a complete listing. To run the Database Documenter, follow these steps:

Re-creating Defaults, Rules, and Triggers

Any defaults, validation rules, and referential integrity also need to be set up manually on the back-end server. Fortunately with SQL Server, you can set up referential integrity via the user interface rather than have to write trigger upon trigger. For more information on creating SQL Server objects, see your SQL Server documentation.

Attaching to Server Tables

After your server tables are set up properly, you need to create a link to each table in your Access database and delete the original tables, if applicable, after transferring any data over to the server tables. To link your SQL Server tables to an Access application, follow these steps:

You can choose to create some alias queries for all upsized tables to ensure that your SQL-linked application continues working. For instance, your old tables might have included fields with embedded spaces, such as Customer Name. Because SQL Server doesn't allow that, you likely created a Customer_Name field on your back-end server. You can choose to change any query that referenced these fields, or simply create an alias query that's used in all queries and acts as the base table. An alias query is an actual query saved under the name of the original table but referencing the newly created tables. In this alias query, columns would be aliased, as in Customer Name: Customer_Name.

Using the Upsizing Tools

In my opinion, one of the greatest time-saving products Microsoft ever developed is the Upsizing Wizard. When your application is ready for the client/server world, upsizing your data table to SQL Server is quite simple.

Looking at the Upsizing Wizard

The Upsizing Wizard is an alternative to manually upsizing a database. If you've ever upsized a database manually, you'll certainly appreciate these tools. The steps in this section upsize the Northwind.mdb application found in your Program FilesMicrosoft OfficeOfficeSamples folder.

First, locate the Upsizing Wizard (choose Database Utilities from the Tools menu). Next, create a copy of the Northwind database to use for this experiment, name it NorthwindSQL.mdb, and place it in the same folder as the original.

Now you can put the upsizing tools to use.

The wizard continues past any errors that occur after you acknowledge the message box. Each table name is displayed as the table is upsized or exported to the SQL Server database. When it's complete, you've upsized your application and created a front-end/back-end application (in which you've upsized the data and created table links) or a true client/server application (in which you've created a new Access Project). The code described in Chapter 27, “Startup Checking System Routines Using ADO,” relinks tables when the application is started from the front end to the new SQL Server back end.

Creating a Project from an Application

When you tell the Upsizing Wizard to convert your Access application to an Access Project by using the Create New Access Client/Server Application option, the wizard creates a new Access Project file and upsizes objects from the source database to the Project like this:

• Queries. Queries are converted to either views or stored procedures, depending on their function. Select queries without ORDER BY are converted to views. Select queries with ORDER BY must be converted to a query called from a stored procedure; this is because SQL Server views can't contain the ORDER BY clause. Queries that contain parameters (include other queries that do so) are also converted to stored procedures, as are update and delete queries. A few types of queries (SQL Pass-Through queries, data-definition queries, union queries, and queries with many nesting levels) aren't directly supported by SQL Server and aren't upsized; you must contrive a programmatic alternative to these queries.

• Forms and reports. The wizard might need to modify form and report properties that contain the name of a table, query, or field or contain SQL syntax. The changes mostly involve updating the ControlSource, RecordSource, and RowSource properties to use the names of new views, table or field name aliases, or stored procedures created during upsizing.

• Data Access Pages. The wizard reviews your Data Access Pages and updates connection and data binding information in them to use the new SQL Server database. A copy of each changed Data Access Page file is made to the same location as the Access Project with a _CS added to the end of the filename. The newly created pages retain the original name in the Access Project so that hyperlinks between the pages work correctly.

• Macros and modules. The wizard doesn't change macro commands or module code. After upsizing to a Project, you should review your code that uses Recordset objects from DAO and change the code to use ADO instead. See Chapter 6, “Introducing ActiveX Data Objects,” for ADO information.