Article ID: 330468
Article Last Modified on 5/8/2007
APPLIES TO
- Microsoft Access 2002 Standard Edition
This article was previously published under Q330468
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).
For a Microsoft Access 2000 version of this article, see 325017.
IN THIS TASK
SUMMARY
This article describes how to upsize a Microsoft Access database to the Microsoft SQL Server or to the Microsoft Data Engine (MSDE) by using the Access 2002 Upsizing Wizard. This article introduces the Upsizing Wizard, what to check before you upsize, design considerations, suggested troubleshooting techniques for common upsizing issues, and additional resources for information.
back to the top
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 when you create a new client server application.
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:
- Keep the current Access database file (.mdb) and then add 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.
- Create a new Access project file (.adp) and then copy the forms, the reports, the data access pages, the macros, and the modules from the current Access database, and then connect 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, the process is not perfect. 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:
- Click Microsoft Access Help on the Help menu.
- Type Upsizing Wizard in the Office Assistant or in the Answer Wizard.
- 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:
- Click Microsoft Access Help on the Help menu.
- Type Comparison of Microsoft Access and SQL Server syntax in the Office Assistant or in the Answer Wizard.
- Click Search to view the Comparison of functions between Visual Basic for Applications and SQL Server Transact-SQL 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 , Starting, and Stopping MSDE 2000" 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 you want to upsize on. 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.
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. 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:
Working with the Data Access Components of an Office Solution
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrworkingwithdataaccesscomponentsofofficesolution.asp
For additional references about how you can use ADO, visit the following MSDN Web site:
Programming in Access Client/Server Solutions
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrprogramminginaccessclientserversolutions.asp
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.
- Tables Unexpectedly Upsized to SQL Master Database. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
281950 ACC2002: Database Unexpectedly Upsized to SQL Server Master Database
REFERENCES
For additional information about upsizing, visit the following MSDN Web sites:
Using Microsoft Access 2002 with MSDE 2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_msdeintro.asp
Upsizing to SQL Server
http://msdn2.microsoft.com/en-us/library/ms952656.aspx
Converting an Access Database with the Upsizing Wizard
http://msdn2.microsoft.com/en-us/library/aa164896(office.10).aspx
For additional information about issues that you may experience when you upsize to SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
328319 ACC2002: Issues with the Access 2002 Upsizing Wizard
For additional information about the "Access 2002 Upsizing Tools" White Paper, click the following article number to view the article in the Microsoft Knowledge Base:
294407 ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download Center
Additional query words: acc2002 upsize
Keywords: kbdesign kbdatabase kbhowto KB330468
