Share via

Choose how to upsize your application

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

You can select different ways to upsize your Microsoft Access database application. This topic provides reference information about each application changes option:

No application changes

Link SQL Server tables to existing application

Create new Access client/server application

Save password and user ID

No application changes

Select No application changes if you only want to upsize your data and not make any other changes to your existing Access database application.

Return to top

If you select Link SQL Server tables to existing application, the Upsizing Wizard can modify your Access database so that your queries, forms, reports, and data access pages use the data in the new Microsoft SQL Server database rather than the data in your Microsoft Access database.

The Upsizing Wizard renames the Microsoft Access tables you upsize with the suffix "_local." For example, if you upsize a table named Employees, the table is renamed Employees_local in your Access database. Then, the Upsizing wizard creates a linked SQL Server table named Employees.

Queries, forms, reports, and data access pages based on the original Employees tables will now use the linked SQL Server Employees table. Many of the properties of the fields in the original local table are inherited by the new local table including, Description, Caption, Format, InputMask, and DecimalPlaces. Learn about linked table properties.

Return to top

Create new Access client/server application

If you select Create new Access client/server application, the Upsizing Wizard creates a new Microsoft Access project. The Upsizing Wizard prompts you for a name, which defaults to the current Access database name, adds a "CS" suffix, and then stores it in the same location as the existing Access database.

The Upsizing Wizard creates the Access project file, displays the Data Link Properties dialog box so that you can connect to an SQL Server database, and then upsizes all the database objects from the Access database to the Access project:

  • Queries   The Upsizing Wizard changes queries by converting them to either views or stored procedures and upsizing Access SQL syntax to use SQL Server SQL syntax. 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). Parameterized queries, queries that depend on a parameterized query, and action queries are converted to stored procedures. You may need to manually convert queries that did not upsize (such as those that were nested too deeply). SQL Passthrough queries, data definition queries, and union queries are not upsized.

  • Forms, reports, and controls The Upsizing wizard modifies RecordSource, ControlsSource and RowSource properties by changing table, query, SQL statement, or field names to the equivalent SQL Server table, view, stored procedure, or field names.

  • **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 SQL Server Pages continue to work.

  • **Macros and modules  ** The Upsizing Wizard doesn't make any changes to these database objects. 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, and 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

Return to top

Save password and user ID

By default, the wizard creates linked tables in the existing application or creates an Access project without saving the username and password. This means that users are prompted for a username and password each time they log on to an SQL Server database.

If you select Save password and user ID, users can access an SQL Server database without logging in. If you select Create new Access client/server application, the Access project stores the username password in the OLE DB connection string.

Note This option is disabled for the No application changes option if a linked SQL Server table is configured with an MSysConf table to deny saving passwords.

Return to top