How database objects get upsized

The following data and database objects get upsized:

Data and data types

For SQL Server version 7.0 or later databases, the Upsizing Wizard converts Access database text to Unicode by adding the Unicode string identifier to all string values and by adding the Unicode n prefix to all data types.

All Access database data types are converted to their equivalent in SQL Server.

Queries

For SQL Server 2000 databases:

Select queries that don't have an ORDER BY clause or parameters are converted to views.
Action queries are converted to stored procedure action queries. Access adds SET NOCOUNT ON after the parameter declaration code to make sure the stored procedure runs.
Select queries that only reference tables (also called base queries) that use either parameters or an ORDER BY clause are converted to user-defined functions. If necessary, the TOP 100 PERCENT clause is added to a query that contains an ORDER BY clause.
Parameter queries that use named parameters maintain the original text name used in the Access database and are converted either to stored procedures or inline user-defined functions.

For SQL server 7.0 and 6.5 databases:

Select queries are converted to views.
Sorted queries are converted to a combination of views and stored procedures to allow for nesting and sorting (Views can be nested but can't contain ORDER BY clauses; stored procedures can contain ORDER BY clauses but can't be nested).
Parameter queries, queries that depend on a parameter query, and action queries are converted to stored procedures.

Note For all SQL Server databases, you may need to manually convert queries that did not upsize, such as SQL Passthrough queries, data definition queries, and union queries. You may also have to manually upsize queries that were nested too deeply.

Forms, reports, and controls

For all SQL Server databases, SQL statements in RecordSource, ControlsSource and RowSource properties for forms, reports, or controls are kept in place and not converted to a stored procedure or user-defined function.

Data access pages

The Upsizing Wizard changes the OLE DB connection and the data binding information in the Microsoft Office data source control to work with the new SQL Server database, and copies the page's corresponding HTML file to the same location as the Access project, renaming the HTML file with a "_CS" suffix. The new pages in the Access project retain the original name, so that hyperlinks between the Access project Pages continue to work.

Customized command bars and startup properties

For all versions of SQL Server, the Upsizing Wizard upsizes any custom command bars as well as the following startup properties:

StartUpShowDBWindow
StartUpShowStatusBar
AllowShortcutMenus
AllowFullMenus
AllowBuiltInToolbars
AllowToolbarChanges
AllowSpecialKeys
UseAppIconForFrmRpt
AppIcon
AppTitle
StartUpForm
StartUpMenuBar
StartupShortcutMenuBar

Modules and macros

The Upsizing Wizard doesn't make any changes to modules and macro. Designing and building an optimized client/server application is different from designing and building an optimized file server application. Therefore, you will probably need to retrofit your application to take full advantage of SQL Server and an Access project. After you upsize, you need to manually convert code that uses recordsets from Data Access Objects (DAO) to ActiveX Data Objects (ADO) in your modules. You should also revise any table and query design code (The Upsizing Wizard does not upsize an SQL Data Definition Language). For more information on developing optimized client/server applications, see the Microsoft Office 2000/Visual Basic Programmer's Guide.