Move Access data to a SQL Server database by using the Upsizing Wizard – Access – Office.com

How database objects get upsized

The following data and database objects get upsized:

Data and data types All Access database data types are converted to their equivalent in SQL Server. The 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.

Queries

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.

Note You might need to manually convert queries that did not upsize, such as SQL pass-through queries, data definition queries, and crosstab queries. You might also have to manually upsize queries that were nested too deeply.

Forms, reports, and controls SQL statements in RecordSource, ControlsSource and RowSource properties for forms, reports, or controls are kept in place and are not converted to stored procedures or user-defined functions.

Startup properties The Upsizing Wizard upsizes the following startup properties:

StartUpShowDBWindow

StartUpShowStatusBar

AllowShortcutMenus

AllowFullMenus

AllowBuiltInToolbars

AllowToolbarChanges

AllowSpecialKeys

UseAppIconForFrmRpt

AppIcon

AppTitle

StartUpForm

StartUpMenuBar

StartupShortcutMenuBar

Modules and macros The Upsizing Wizard does not make any changes to modules or macros. You might need to modify your application to take full advantage of SQL Server’s features. For more information, see the MSDN article Optimizing Microsoft Office Access Applications Linked to SQL Server.

via Move Access data to a SQL Server database by using the Upsizing Wizard – Access – Office.com.