Microsoft Knowledge Base Wiki

This article shows you how to upsize a Microsoft Access
database to the Microsoft SQL Server or to the Microsoft Data Engine (MSDE) by
using the Access 2000 Upsizing Wizard. This article covers an introduction to
the Upsizing Wizard, what to check before you upsize, design considerations,
suggested troubleshooting techniques for common upsizing issues, and additional
resources for information.

Introduction

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 (including indexes, validation rules, defaults,
and relationships) and then copies your data to the new SQL Server database.
Additionally, the Upsizing Wizard tries to re-create your queries as SQL Server
views and as stored procedures.

You can select to upsize only your
database structure and your data, or, after you create the SQL Server back-end
database, you can select 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 then
  adding linked tables that connect to the upsized tables on the SQL
  Server.

  Your existing forms, reports, and data access pages use the
  newly linked tables as their data sources.

• By creating a new Access project file (.adp) and then
  copying the forms, the reports, the data access pages, the macros, and the
  modules from the current Access database, and then connecting that Access
  project file to the upsized tables on the SQL Server.

  The copied
  forms, reports, and data access pages that refer to the local database are
  converted to use the newly upsized SQL Server tables, views, and stored
  procedures as their data sources through the connection of the ADP file to the
  server. Data access pages that refer to databases other than the current
  database are unchanged after upsizing.

Note that when you run the Upsizing Wizard, this is not a
perfect process. There are differences and potential incompatibilities between
Access databases and SQL Server databases. Included are differences in SQL
dialects and data types. Although the Wizard can handle many of these
differences and still convert objects correctly, the Wizard cannot handle all
differences. Therefore, you may experience problems while you create your new
database and your new client application. If the Upsizing Wizard experiences a
problem during the upsizing process, the Wizard does not stop the process. The
Wizard records the error and then continues to work with the next object. After
the upsizing process is complete, the Wizard displays a report. The report
shows you the details of the process that include the name and the size of the
new database, the selections that you made while you ran the Wizard, and any
errors the Wizard experienced.

For more information about how to run
the Upsizing Wizard, follow these steps:

1. Click Microsoft Access Help on the Help menu.
2. Type Upsizing Wizard in the Office
   Assistant or the Answer Wizard.
3. Click Search to view the topics that are returned.

For more information about differences between Access syntax
and SQL Server syntax and how the Upsizing Wizard handles these differences,
follow these steps:

1. Click Microsoft Access Help on the Help menu.
2. Type Comparison of Microsoft Access and SQL
   Server syntax in the Office Assistant or the Answer
   Wizard.
3. Click Search to view the Work with a Microsoft Access Project subtopic.

What to Check Before You Upsize

• Make sure that you have a backup copy of your Access
  database file (.mdb). The method that you select to upsize may change your
  existing application design.
• Make sure that you know the name of the SQL Server or the
  computer that runs MSDE that you will connect to. Make sure that you know the
  logon information. For additional information about how to install MSDE, visit
  the “Installing MSDE” section of the MSDN Web site:
• To make upsizing go as smoothly as possible, make sure that
  you have the appropriate access permissions on the SQL Server to which you want
  to upsize. At minimum, you must have CREATE TABLE permission. If you want to
  build a new database from scratch, you must have CREATE DATABASE
  permission.
• To make sure that you have enough free space, calculate how
  much disk space upsizing will require by multiplying the size of your Microsoft
  Access database by two.
• If you plan to upsize to SQL Server 2000, install the
  Office 2000 SR-1 Update and the Access 2000 and SQL Server 2000 Readiness
  Update before you start.

  For additional information about obtaining and
  about installing the Office 2000 SR-1 Update, click the following article number to view the article in the Microsoft Knowledge Base:

  245025

  (http://support.microsoft.com/kb/245025/
  )

  How to obtain and install the Microsoft Office 2000 SR-1/SR-1a
  Update

   

Database Design Considerations

• You must follow server-based naming restrictions from the
  beginning. The Upsizing Wizard can correct many common mistakes, but not all
  mistakes. To make sure there is an easy migration to the back end, follow the
  SQL Server restrictions:
  • For SQL Server 6.5, names must be 30 characters or
    less. For SQL Server 7.0, this limit is expanded to a maximum of 128
    characters, and for SQL Server 2000 the limit is expanded to 249
    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 permitted. For SQL
    Server 7.0, spaces are permitted, but the name must be enclosed by quotation
    marks (“”) or by square brackets ([]).
  • The name must not be a Transact-SQL keyword. SQL Server
    reserves both the uppercase and the lowercase versions of keywords. For
    information about Transact-SQL keywords, see the SQL Server Books Online.
• The Upsizing Wizard does not convert certain items. Other
  items are converted, but require verification after the Wizard has completed.
  When you use the Upsizing Wizard, you must review all changes made to tables,
  to views, to forms, and to reports.

  Specifically, the Upsizing Wizard
  uses the following rules when databases are converted:

  • Duplicate column names are given an alias.
  • Date delimiters are converted.
  • Boolean constants are converted to integers.
  • String concatenation is converted from an ampersand
    (&) to a plus sign (+).
  • Wildcard characters are converted to their appropriate
    Transact-SQL equivalents.
  • WITH TIES is added to all TOP queries that contain an
    ORDER BY clause.

  The following Access SQL syntax elements are not supported
  by MSDE and are not converted. The Upsizing Wizard removes these items from SQL
  statements:

  • DROP INDEX
  • DISTINCTROW
  • OWNERACCESS
  • Table in UNION
  • ORDER BY in Unions
  • TRANSFORM
  • PARAMETERS
• The Upsizing Wizard does not convert your code of the
  Access solution. Code that works directly with Access objects may continue to
  work, but you must convert any Data Access Objects (DAO) code that works with
  tables and with queries to ActiveX Data Objects (ADO) code.

  For an
  overview of using ADO, visit the following MSDN Web site:

  For additional references about how you can use ADO, visit the
  following MSDN Web site:

Troubleshooting Upsize Issues

• Tables do not upsize.
  • Make sure that the default data file size for the Model
    database on the SQL Server is not larger than 1 MB.
  • Try to upsize just the table structure.
• “Overflow” Error Message when trying to upsize to SQL
  Server 2000
• Cannot upsize MDE to SQL Server or MSDE
• Combo Box or List Box Empty after upsizeFor additional
  information about issues you may experience when you upsize to SQL Server 2000,
  click the article number below to view the article in the Microsoft Knowledge
  Base:
  269824

  (http://support.microsoft.com/kb/269824/EN-US/
  )

  ACC2000: Incompatibility Issues Between Access 2000 Projects and SQL Server 2000

For additional information about upsizing, visit the
following Microsoft Developer Network (MSDN) Web sites:

For additional information about the Access 2000 Upsizing Tools White
Paper, click the following article number to view the article in the Microsoft Knowledge Base:

For additional information about the Access
2000 Upsizing Wizard, click the following article number to view the article in
the Microsoft Knowledge Base:

Article ID: 325017 – Last Review: August 10, 2012 – Revision: 4.0