PLANNING FOR CLIENT/SERVER

It's important to plan for client/server applications as early in the development process as possible. Your initial decision of data structure and your approach to application development affect your entire migration process. Even before considering the migration path to client/server, you need to think about what your data storage needs are and what effect they will have on your network. Can your network handle the traffic demands that your application might have?

The following sections cover many server-specific rules that could trip up your client/server applications, as well as various development issues that you need to be aware of.

Microsoft wants developers to believe that migration from Access file-server applications to client/server using SQL Server is an easy task. Well, not quite. True, it might be easy if the process is well planned from the beginning, but too many times this doesn't happen. The following sections stress many items to keep in mind as you develop your Access applications. It's always possible that someday your applications will be migrated to client/server. Even if you think it's not probable, why eliminate the possibility?

If you have an Access application, it will run perfectly when your tables are upsized to a SQL Server database—right? Unfortunately, this isn't the case. You need to be aware of and take into consideration a number of important server-specific rules as you plan your applications. SQL Server has a number of differences from Access, including different limitations on field and table names, an increased number of reserved words, case sensitivity, and how it processes query requests.

As you plan for upsizing, you must consider which upsizing option you will use. Access lets you upsize an application in two ways:

• Upsize the tables. If you choose to upsize only the data tables to SQL Server with the Upsizing Wizard, each table in your application is moved to the server and linked back to your application. This is the only upsizing behavior available in previous versions of Access. This option creates an application that's not truly client/server; instead, the application follows a high-end file server model with SQL Server as the back end. We will refer to this structure as a SQL-linked application in this chapter.

• Create a Project. The Upsizing Wizard can now convert your application into an Access Project, where no Jet components exist and all tables and queries in your application become tables, views, and stored procedures on the SQL server. A Project provides a true two-tier client/server application where the server is used for all data processing of records returned to the client.

Field and Table Names

Isn't it great that Access allows spaces in field and table names and lets the developer use an unwieldy number of characters to name objects? Well, it's not so wonderful after you venture into the world of client/server development. As the developer, you need to have the foresight to prepare for the possibility of migration to SQL Server or another back-end database.

SQL Server 6.5 doesn't allow field and table names longer than 30 characters, so limit your object names to this length if you plan to upsize to SQL Server 6.5. SQL Server 7.0 supports the same 64-character object name limit that's allowed in Access.

If you want to use a different back-end database in the future, adhere to the name length limit of that back end. For example, some back-end databases have a 10-character maximum field name length. Always consider the lowest common denominator among back-end database restrictions when naming objects.

Next is the issue of embedded spaces in field names or table names. SQL Server didn't allow this until version 7.0. If you're upsizing to a SQL Server 6.5 database, the Upsizing Wizard will change the spaces to underscore characters (_) in your object names, which might impact how your code runs or application objects behave. If you are expecting to upsize your application to SQL Server 6.5 or any other back end that doesn't support spaces, don't use them in your object names.

Reserved Words

SQL Server has even more reserved words to beware of than Access does. In your first conversion from Access to SQL Server, you might not understand why the Upsizing Tools renames one of your fields from DESC to DESC_. You'll later realize that although Access accepted this field's name, DESC is a reserved word in SQL Server to indicate descending sort order. That's life. In any case, this is something to consider when beginning development of your Access database.

Case Sensitivity

By default, SQL Server is installed with a sort order of Dictionary Order, Case Insensitive. This option means that the characters a and A are equivalent for the purpose of sorting and comparisons, which is the same behavior found in Access using Jet. The sort order determines not only how SQL Server sorts query results, but also is used in processing queries that involve character comparisons.

Query Processing on the Server

To take full advantage of your back-end server, you'll want to do as much processing on the server as possible. Remember, SQL Server specializes in processing large amounts of data. In most cases, processing queries on SQL Server is faster than processing queries on the client application.

Moreover, if the back-end server can't process your request for information, it sends a large amount of data back to Access across the network lines and lets Access come up with an answer. Ensuring that you're sending requests that the server can understand—either through ODBC or SQL Pass-Through queries—is a very important step that shouldn't be taken lightly.

Fortunately, if you have an extremely complex query that SQL Server can't fully understand, Access still sends limited portions of the query that SQL Server can deal with. A good example is an Access crosstab query. SQL Server doesn't understand these queries, but can still do the initial select query and send the results back to Access. Access takes those query results and completes the crosstab query.

Sometimes you might not know where a query is actually being processed. You can use some third-party tools to monitor the communication to and from the server to determine whether the server sent back the full result set, partial result set, or nothing at all.

You also have the option of setting ODBC tracing to on, which logs all ODBC calls to a text file. For developers, this log file can provide invaluable information; you really know what ODBC calls are being made behind the scenes, which might help you determine why your application isn't performing as you would expect. To turn ODBC tracing on, follow these steps:

1.	Double-click the ODBC Data Sources icon in the Windows Control Panel (note that this icon has a different name in each of the latest Windows versions—95, 98, and NT).
2.	On the Tracing page, select a different log file, if desired. By default, the ODBC calls are logged to SQL.log.
3.	Click the Start Tracing Now button (see Figure 24.2). Figure 24.2. Turn ODBC tracing on with the ODBC Data Source Administrator.