Microsoft Office 2000/Visual Basic Programmer's Guide

Converting an Access Database with the Upsizing Wizard

You can use the Upsizing Wizard to convert an existing Access database (.mdb) to a client/server solution. The Upsizing Wizard creates a new SQL Server database structure by re-creating the structure of your Access tables (including indexes, validation rules, defaults, and relationships), and then copying your data into the new SQL Server database. Additionally, the Upsizing Wizard will attempt to re-create your queries as SQL Server views and stored procedures.

You can choose to upsize only your database structure and data, or, after creating the SQL Server back-end database, you can choose to create an Access front-end client application. The Upsizing Wizard can create the front-end client application in either of two ways:

• By keeping the current Access database file (.mdb) and adding linked tables that connect to the upsized tables on SQL Server. Your existing forms, reports, and data access pages will use the new linked tables as their data sources.
  
  
• By creating a new Access project file (.adp) and copying the forms, reports, data access pages, macros, and modules from the current Access database, and then connecting that Access project file to the upsized tables on SQL Server. The copied forms, reports, and data access pages that refer to the local database will be converted to use the new upsized SQL Server tables, views, and stored procedures as their data sources through the .adp file's connection to the server. Data access pages that refer to databases other than the current database will be unchanged after upsizing.

It's important to note that running the Upsizing Wizard isn't necessarily a perfect process. There are differences and potential incompatibilities between Access and SQL Server databases, such as differences in SQL dialects and data types. Although the wizard can handle many of these differences and still convert objects correctly, it can't handle all differences, and so may encounter problems while creating your new database and client application. If the Upsizing Wizard encounters a problem during the upsizing process, it won't halt the process. It records the error and continues working with the next object. After the upsizing process is complete, the wizard will display a report that shows details of the process, such as the name and size of the new database, the selections you made while running the wizard, and any errors it encountered.

For details about how to run the Upsizing Wizard, search the Microsoft Access Help index for "Upsizing Wizard." For information about differences between Access and SQL Server SQL syntax and how the Upsizing Wizard handles these differences, see the "Comparison of Microsoft Access and SQL Server syntax" topic in Microsoft Access Help.

Database Design Considerations Before Upsizing

Before upsizing a solution to SQL Server, there are several important design issues you should take into consideration. The following tips describe the most common issues and strategies.

• If you are upsizing to an Access database (.mdb) linked to SQL Server, design forms so that the form doesn't require data to be retrieved from the server during the form-opening process. Instead, add a command button to the form to fetch data to populate the form. 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. If you are upsizing to an Access project file (.adp) linked to SQL Server, you can't save data to local tables in the file, but you can limit the number of records that are opened at one time by using the MaxRecords, ServerFilter, and ServerFilterByForm properties of the form after your database is upsized.
  
  
• Use an ADO Recordset object with a client-side, Static cursor that specifies read-only locking (CursorLocation = adUseClient, CursorType = adOpenStatic, and LockType = adLockReadOnly) if you don't need to update the server tables. For forms, you can achieve the same result by setting the form's RecordsetType property to Snapshot. If you do need to update server tables, use an ADO client-side cursor (CursorLocation = adUseClient) with a Static cursor (CursorType = adOpenStatic) and use batch optimistic locking (LockType = adLockBatchOptimistic) so that you can perform batch updates by using the BatchUpdate method. For forms, you can achieve a similar result by setting the form's RecordsetType property to Updatable Snapshot.
  
  
• Minimize the number of items in server-populated combo boxes. Use an ADO Recordset object with a Forward-only cursor for server-populated 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 numbers in the hundreds, using a combo box becomes awkward.
  
  
• If combo box data changes infrequently, maintain a local copy of the server data that populates the combo boxes. If you are using an Access database (.mdb) linked to SQL Server, you can save data in local tables. If you are using an Access project (.adp), you can't store local tables, but you can use the Save method of the ADO Recordset object to save server data to a local file. You can't use the saved Recordset file directly as the setting for the combo box's RowSource property, but you can use the Open method of the ADO Recordset object to open the file, loop through the Recordset object to generate a text string formatted as a value list, and then populate the combo box from that list. Include only the field(s) you need for the combo box in the table. For local Access tables, you can create an index on the local table to speed population of the combo box. Provide the user with a simple means of replacing the local data with current data from the server. Alternatively, you can populate a combo box by setting the RowSourceType and RowSource properties of the combo box to use a stored procedure or an SQL statement to select the values to display.
  
  
• If decision-support application users need to compare multiple sets of data, consider storing the data returned by the server in temporary local tables or files. Provide a form in which the user can elect to either use the previously stored data or run 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 corrects many common mistakes in this regard, but not all. In order to ensure a painless migration to the back end, it is best to adhere to the following SQL Server restrictions from the beginning:
  • For SQL Server 6.5, names must be 30 characters or less. For SQL Server 7.0, this limit has been expanded to a maximum of 128 characters.
    
    
  • The first character must be a letter or the "at" sign (@).
    
    
  • The remaining characters may be numbers, letters, the dollar sign ($), the number sign (#), or the underscore (_).
    
    
  • For SQL Server 6.5, no spaces are allowed. For SQL Server 7.0, spaces are allowed, but the name must be surrounded by double quotation marks (") or square brackets ([ ]).
    
    
  • The name must not be a Transact-SQL keyword. SQL Server reserves both the uppercase and lowercase versions of keywords. For information about Transact-SQL keywords, see the Transact-SQL Reference in the Microsoft Access Help or in SQL Server Books Online.

Converting Your Solution's Code to ADO

The Upsizing Wizard doesn't convert your Access solution's code. Code that works directly with Access objects should continue to work, but you will need to convert any DAO code that works with tables and queries to ADO code. For an overview of using ADO, see Chapter 14, "Working with the Data Access Components of an Office Solution." For additional references on using ADO, see the following section, "Programming in Access Client/Server Solutions."