Design Tips — Client Side

• Design forms so that a form does not require data to be retrieved from the server during the form-opening process. Add a command button to the form to fetch data to populate the form. In many cases, you may want to save the last set of form data in a local table, then load the form from the saved data the first time the user opens the form.

• Use Recordset objects of the Snapshot type if the result set contains relatively few columns and doesn't contain OLE Object or large Memo fields, and you don't need to update the server tables. Set the value of the AllowUdating property of the form to No Tables to create a Snapshot instead of a Dynaset. Pass-through queries always return Snapshot Recordset objects.

• Minimize the number of items in server-populated pick list combo boxes. Use Snapshot Recordset objects to populate the combo boxes. It's important to remember that as database sizes increase, certain solutions may become unworkable. For instance, a combo box of 30 records is reasonable for a user to browse and pick from. When the list of possibilities is in the hundreds, this list becomes unworkable.

• If pick list data changes infrequently, maintain a local copy of the server table that populates the combo boxes. Include only the field(s) you need for the pick list in the table. Create an index on the local table to speed pick list population. Provide the user a simple means of replacing the local tables.

• If decision-support application users need to compare multiple sets of data, consider storing the data returned by the server in temporary local tables. Provide a form in which the user can elect to use the previously stored data, or execute a new query. Adding the ability to quickly compare results of successive queries is especially important in applications that process financial information.

• Adhere to server-based naming restrictions from the beginning. The Upsizing Wizard will correct many common mistakes in this regard, but not 100 percent. It is best to adhere to SQL Server's restrictions from the beginning in order to assure a painless migration to the back end. SQL Server names must be 30 characters or less. The first character must be a letter or the symbol "@". The remaining characters may be numbers, letters, or the "$," "#," and "_" symbols. No spaces are allowed.

• In code, do not use table-specific commands such as OpenTable or the SEEK method. These operations are only supported on the local Jet tables and will break if they are suddenly referring to server-based tables.

Create ODBC Data Source

You need to make sure you have the ODBC data source you will need for upsizing. You should also decide which database you plan to upsize and make a backup copy of it.

The Upsizing Wizard requires that you log in to a SQL Server database. If you are creating a new SQL Server database, you should make sure you have an ODBC data source for the Master database for the SQL Server you want to upsize to.

If you are upsizing to an existing database, the Pubs sample database for instance, make sure you have an ODBC data source for Pubs.

You can create an ODBC data source by running the ODBC Administrator.