Russell Christopher
Microsoft Corporation
October 1999
Summary: Microsoft® Access Client/Server makes it easy for designers to create client/server applications. The quickest and easiest way to move data residing in an Access database to Microsoft SQL Server™ version 7.0 or the Microsoft Data Engine (MSDE) is by using the Upsizing Tools, which are built directly into Access 2000.
The Microsoft Access 2000 Upsizing Tools allow you to migrate tables and common queries in your Microsoft Access (Jet) database to SQL Server or MSDE. In addition, you can use the wizard to create a basic client/server application using the original forms, reports, macros, and modules from your Access database. This article will show you how to use the Access 2000 Upsizing Tools. (31 printed pages)
Introduction
Version Support
New Features and Enhancements
The Upsizing Process
Preparing to Upsize
Running the Upsizing Tools
Examining the Upsizing Report
Re-creating Objects on SQL Server or MSDE
Learning More About SQL Server 7.0 and MSDE
The introduction of Microsoft® Office 2000 marks the first time that Microsoft Access allows the developer to create client/server solutions in a unified development environment. Using an Access project connected to Microsoft SQL Server™ version 7.0 or the Microsoft Data Engine (MSDE), a programmer can seamlessly develop both the "back-end" and user interface portions of an application.
Microsoft Access Client/Server has made it easier for designers to create client/server applications; and as a result, more users will use and migrate applications to SQL Server and MSDE. The quickest and easiest way to move data residing in an Access database to SQL Server or MSDE is by using the Upsizing Tools, which are built directly into Microsoft Access 2000.
The Microsoft Access 2000 Upsizing Tools allow you to migrate tables and common queries in your Microsoft Access (Jet) database to SQL Server or MSDE. In addition, you can use the Upsizing Wizard to create a basic client/server application that uses the original forms, reports, macros, and modules from your Access database.
The Microsoft Access 2000 Upsizing Tools support the following database servers:
You can use the Upsizing Tools with all versions of SQL Server 7.0, including the following:
You can use all versions of the Microsoft Data Engine (MSDE) with the Microsoft Access 2000 Upsizing Tools. Note that MSDE can be installed from sources other than the Microsoft Office 2000 Suite. Both independent software vendors and developers may freely distribute MSDE as the data engine for their third-party applications.
If MSDE is already installed on your computer as the result of setting up a third-party application, you may encounter problems accessing or using MSDE via Microsoft Access Client/Server or the Upsizing Tools. If this occurs, contact the software manufacturer of the third-party application to determine how MSDE was configured during installation:
The Upsizing Tools can upsize to SQL Server 6.5 patched with service pack 5. If your SQL Server is not patched to at least service pack 5, the Upsizing Tools will not work.
You cannot use SQL Server versions 4.x or 6.0 with the Microsoft Access 2000 Upsizing Tools.
The following sections describe changes and additions made to the Upsizing Tools in Microsoft Access 2000.
The Microsoft Access 2000 Upsizing Tools leverage new features added to Access itself, such as the Microsoft Access project.
You can automatically create a new Access project when you upsize your Access database by selecting the "Create a new Access client/server application" option of the wizard. When you do so, the Upsizing Wizard creates a new Access project, connects it to the newly created SQL Server database, and then exports all objects in your Access database to the .adp file. Tables, queries, forms, reports, data access pages, macros, and modules are all exported.
Two changes have been made to the Upsizing Wizard to allow large databases to be upsized while unattended.
When the upsizing process encounters an error during the upsizing of your database, it is not designed to stop and report an error condition as in earlier versions. Instead, any errors that occur are logged and described in the Upsizing Report created at the end of the upsizing process.
If the name of a table in your Access database already exists in the SQL Server or the MSDE database to which you are upsizing, the upsized table is made unique by appending a number to the end of the table name.
When you upsize your Access database, all inserts to SQL Server and MSDE are recorded in the Transaction Log of the server. If you upsize large databases to SQL Server 6.5, it is common to receive SQL Server error message 1105 (in this case, a full Transaction Log) when using earlier versions of the Upsizing Tools.
The new version of the Upsizing Wizard automatically truncates the Transaction Log after each table is upsized, as long as you are upsizing to a new database on the server. After the upsizing process is complete, the automatic log truncation is turned off.
Note You can still fill the log when you upsize very large tables. If this occurs, the table will be skipped, and an error will be logged in the Upsizing Report.
You can use the Microsoft Access 2000 Upsizing Tools to copy tables linked to SQL Server and MSDE. However, you should not use this functionality as your primary method of moving indexed sequential access method (ISAM) data and Open Database Connectivity (ODBC)-based data to your SQL Server. Instead, use the Data Transformation Services available with MSDE and SQL Server 7.0.
In earlier versions of the Upsizing Tools, error messages returned by triggers were created and stored in SQL Server's sysmessages table. The Microsoft Access Client/Server Upsizing Tools now store message text in the body of the trigger itself, making it easier for you to customize it as you see fit.
Earlier versions of the Upsizing Tools assume that you want to copy tables to an existing database. User feedback pointed to problems with this approach in cases where the names of tables being upsized already existed in the current database. As a result, the wizard now defaults to Create New Database.
The Upsizing Tools use the new GetHiddenAttribute method added to Microsoft Access 2000 to determine whether objects in your database should be copied to an Access project if you choose to create a new Access client/server application.
After the upsizing process is finished, you are presented with an Upsizing Report that details the work done by the wizard. The report has been improved to offer you more information.
When objects in your database are not upsized successfully, the Upsizing Report returns more detailed information as to why the error occurred. In some cases, the report will specify how to fix the problem.
Feedback from users indicated that they often wanted to refer to the Upsizing Report after closing it, but had no way to do so. As a result of this feedback, when the Upsizing Report is generated, it is not only displayed to you, but also saved as a snapshot report in the same folder as the database being upsized.
SQL Server 7.0 table identifiers may have spaces in their names. When upsizing a table named "My Table" to SQL Server 6.5, the object will be created as "My_Table." However, if you upsize the table to SQL Server 7.0 or MSDE, the Upsizing Tools can create it as "My Table."
The Upsizing Tools can be used to migrate data quickly from one or more tables to SQL Server or MSDE. However, if you intend to convert your Access database to a working client/server application, you should view the conversion as a four-step process:
Before you run the Upsizing Wizard to create a client/server application, take time to examine your database to find and correct possible trouble spots.
Jet (the database engine used by Microsoft Access) and SQL Server/MSDE implement security in substantially different ways: Microsoft Access does not load Jet when you open an Access project because SQL Server or MSDE acts as your database engine. As a result, the Access Upsizing tools do not migrate any security settings. To learn more about implementing security on SQL Server, refer to SQL Server 7.0 Books Online, which can be downloaded from http://support.microsoft.com/download/support/mslfiles/sqlbol.exe.
When you upsize a database, you must have at least Read Design permission on the objects that you choose to migrate. If you try to upsize objects that you do not have correct permissions on, the Upsizing Wizard will skip them. No errors will be logged in the Upsizing Report when objects are skipped because of permission issues.
It is important to keep in mind the permission chains that may exist in your database. For example, though you may have Read Design permissions on a query that you want to upsize, you must also have permissions on all the tables or queries that make up the data sources of the query for it to be properly upsized.
If your Access database contains a Visual Basic® for Applications (VBA) project protected by a password, the Upsizing Tools will prompt you for a password when you try to upsize it. Until you supply a password, the wizard will be unable to copy any code-bearing objects to the .adp file that is created as a result of using the "Create a new Access client/server application" option of the wizard. If you choose the "No application changes" or "Link SQL Server tables to existing application" option, you should not be prompted for a password.
You also may not upsize Microsoft Access databases saved as an MDE to a new client/server application. When you save a Microsoft Access database as an MDE, all modules and code within the database are compiled and removed. Your Visual Basic for Applications code will continue to run, but the code may not be viewed or edited. The Upsizing Tools will try to access these objects in order to migrate them, and will fail because they have been removed. If you want to upsize an MDE, you should choose the "No application changes" or "Link SQL Server tables to existing application" options of the wizard, or use a copy of the database before it was saved as an MDE.
You should carefully inspect the DefaultValue and ValidationRule properties for each field in your tables. Although there is much functional parity between Transact-SQL (also known as TSQL—the standard language for communicating between applications and SQL Server) and Visual Basic for Applications, not all Visual Basic for Applications functions have TSQL equivalents.
When you upsize a table, the Upsizing Wizard tries to "map" Visual Basic for Applications functions in your DefaultValue and ValidationRule properties to an equivalent TSQL function. If this attempt is not successful, the validation rule or default will be skipped by the Upsizing Wizard. Consider the following:
Table 1 lists Visual Basic for Applications functions that may be used in the DefaultValue or ValidationRule property of your fields.
Table 1. Upsizeable functions
| asc() | ltrim$() | str() |
| ccur() | ltrim() | time() |
| cdbl() | mid$() | trim$() |
| chr$() | mid() | trim() |
| chr() | minute() | ucase$() |
| cint() | mod | ucase() |
| clng() | month( | weekday() |
| csng() | now() | year() |
| cstr() | right$() | |
| cvdate() | right() | |
| date() | rtrim$() | |
| day() | rtrim() | |
| hour() | second() | |
| int() | sgn() | |
| lcase$() | space$() | |
| lcase() | space() | |
| left() | str$() |
ValidationText is upsized only if its accompanying ValidationRule is successfully migrated. Avoid the use of apostrophes ( ' ) in your validation text because they will be displayed as quotation marks ( " ) when upsized.
SQL Server and MSDE have no equivalent to the Format or InputMask property in Microsoft Access 2000. As a result, neither property will be upsized when it is encountered by the Upsizing Wizard, nor will any errors be reported in the Upsizing Report.
Example:
You create a table in your Access database and insert a Text field in Design view. You also add ">" to the Format property of this field so that anything you type into this field will be displayed in uppercase.
You type the following text into the field: "UlUlUl." When you move away from the field, the text is displayed as "ULULUL."
Then, you upsize the table using the Upsizing Tools. You choose to create a new Access client/server application. After the wizard completes its work and you open the upsized table, the data that you typed earlier is displayed as "UlUlUl" (just as you had originally typed it).
However, if you select the "Store Data with symbols in the mask" option, all formatting will be saved.
Example:
You create a table in a Microsoft Access database to track home and work telephone numbers of your employees. You open the table in Design view and add an input mask to the home and work telephone number fields using the Input Mask Wizard.
For the homephone field, you select the Phone Number input mask and choose to store your data with symbols in the mask. After you complete the wizard, the following string is stored in the InputMask property of the homephone field:
!(999)000-000;0;_
(The final zero between the semicolons in this example indicates that the symbols in the mask will actually be saved with the data.)
For the workphone field, you again select the Phone Number input mask in the Input Mask Wizard, but this time you choose to save your data without storing the symbols. Your InputMask property will look as follows when you complete the wizard:
!(999)000-000;;_
You run the Upsizing Tools on your table, and then open the table in an Access project. Telephone numbers stored in the homephone column look like (###)###-####, and numbers stored in the workphone column look like ##########.
When you add new telephone numbers to the upsized table, no input mask is created for either column.
The Caption property is ignored by the Upsizing Tools. The true column name of a field will always be upsized, regardless of what the caption for that field may read.
The value that you select for the AllowZeroLength property determines whether zero length strings ("") may be inserted into a field. Currently, the Upsizing Wizard does not create a constraint or trigger against an upsized table to enforce this rule. Instead, you must manually create a Check Constraint on the columns once the upsizing process is complete. For more information about this topic, see the following article in the Microsoft Knowledge Base: "Q227211—ACC2000: Upsizing Wizard Doesn't Create Constraint for AllowZeroLength Property."
The Required property of a field determines whether the field will allow null values after upsizing. If Required is set to Yes in Microsoft Access, the upsized field will not allow null values. If Required is set to No, null values are acceptable.
The Indexed property (or the Indexes dialog box, which is available on the View menu of a table in Design view) determines what columns in your tables are indexed when you upsize them. The following Table 2 describes the behavior that you should expect, based on the selections you make for the Indexed property of a field.
Table 2. Indexing and the Indexed Property
| Indexed property | Index created by Upsizing Tools |
| No | None |
| Yes (Duplicates OK) | Non Clustered Index |
| Yes (No Duplicates) | Unique, Non Clustered Index |
A field that you have selected as a primary key in Access is re-created as a unique, non-clustered index when upsized. If you want to create the primary key as a clustered index, you must manually modify the index using SQL Server Enterprise Manager, TSQL statements or the Access Client/Server table designer.
Note If you choose specific Indexed and Required property values for a column in your table that also contains more than one null value, the Upsizing Wizard will migrate the structure of your table, but not the data in the table. If you want to make sure that data in all your tables will automatically be upsized to SQL Server, inspect each table for the following conditions:
If necessary, temporarily modify either property of the column or modify the null values that it contains; then upsize your database. For more information about this behavior, see the following article in the Microsoft Knowledge Base: "Q225993—ACC2000: Data Not Upsized in Table with Index."
When you run the Upsizing Wizard, it maps data types in your Access database to equivalent SQL Server and MSDE data types. Not every Jet data type has a counterpart in SQL Server, and vice-versa.
Table 3 details how the Upsizing Tools map data types during migration.
Table 3. Jet to SQL data type mapping
| Jet data type | SQL Server/MSDE data type |
| AutoNumber—Number (Long) | int (w/ IDENTITY property) |
| Byte | smallint |
| Currency | money |
| Date/Time | datetime |
| Hyperlink | ntext (hyperlinks inactive) |
| Memo | ntext |
| Number (Decimal) | decimal |
| Number (Double) | float |
| Number (Integer) | smallint |
| Number (Long) | int |
| Number (ReplicationID) | uniqueidentifier |
| Number (Single) | real |
| OLE Object | image |
| Text | nvarchar |
| Yes/No | bit |
Unicode Data Types—The Upsizing Tools map many of the Jet data types shown in Table 3 to Unicode equivalents on SQL Server (a Unicode data type is denoted by a leading "n" in the data type description). If you want, you can manually change the data type of the upsized columns to a non-Unicode data type.
Hyperlink Data Type—SQL Server and MSDE do not support an equivalent to the Jet Hyperlink data type. As a result, Hyperlink fields will be upsized (as ntext), but you will lose the hyperlink jump functionality in an Access project.
Furthermore, Jet uses hyperlink parts to save and display additional information in a hyperlink value such as Display Text, Sub Addresses, and ScreenTips. In Access, these hyperlink parts are generally hidden. When you upsize a table with hyperlink parts beyond address, they are visible in an Access project.
Example:
In your Access database, you have a table that contains a Hyperlink field. In the table are two records, each with a hyperlink value pointing to a different location:
Run the Microsoft Access 2000 Upsizing Tools and choose to create a new Access client/server application. After the wizard is finished, view the data contained in what used to be your Hyperlink field. Note that the information no longer functions as a hyperlink jump, and that additional information is visible that wasn't before:
Microsoft#http://www.microsoft.com##Click me to jump to MS web site
Form1##Form Form1#Click me to open Form1
With Microsoft Access, you can use Lookup fields to display values selected from an existing table or query in a table, or from a predefined list.
The most common Lookup list displays values selected from a related table. For example, the SupplierID field in the Products table of the Northwind sample database displays supplier names from the supplier table.
This list is created by looking up the SupplierID values in the Suppliers table and displaying the corresponding Supplier names. Picking a value from a Lookup list sets the foreign key value in the current record (SupplierID in the Products table) to the primary key value of the corresponding record in the related table (SupplierID in the Suppliers table).
Because SQL Server and MSDE include no Lookup field functionality, the Microsoft Access 2000 Upsizing Tools migrate the values stored in a Lookup field, not the corresponding value associated with the data.
Example:
In the Products table of the Northwind sample database, you examine the Suppliers field. When you click the combo box, you are presented with a list of possible supplier names to select for a particular product. When you select a supplier name from the combo box, the name is displayed in the Supplier field.
You upsize the Northwind database and open the Products table that now resides on SQL Server. When you do so, you note that the Suppliers column no longer displays the full company name that you saw in your Access database. Instead, the primary key for the Suppliers table is displayed.
This section discusses how queries are migrated by the Upsizing Tools, and how you must prepare your queries so that they can be successfully migrated.
When you choose to Create a new Access client/server application using the Upsizing Tools, the wizard tries to convert all queries in your database to SQL Server views or stored procedures. The conversion process transforms a Jet syntax query into a workable equivalent that will run on SQL Server.
Invariably, some of your queries will not upsize. The Upsizing Wizard does not upsize certain types of queries, and other queries may prove to be too complex to be migrated or may be created in such a way that prevents the Upsizing Tools from parsing and translating the SQL in the query.
Modifying your queries can often resolve problems that originally caused them to be skipped by the Upsizing Wizard (or caused their migration to fail). Because you are likely to run the Upsizing Wizard several times before all of your queries have been "tuned" to your satisfaction, it generally is a good idea to choose the Only create table structure; don't upsize any data option the first few times that you run the wizard. The majority of time that the Upsizing Wizard uses is spent upsizing your data: don't bother migrating your data until you have all your queries the way that you want them.
The Upsizing Tools do not try to upsize every type of Microsoft Access query that you may have in your Access (Jet) database. The following varieties of queries will not upsize:
You must manually re-create queries that the Upsizing Tools do not migrate.
The properties list of an Access query is fairly extensive. Not all of these properties will upsize; some are not supported by SQL Server, and others are not necessary. Table 4 details which properties can be upsized and which cannot.
Table 4. Upsizeable Query Properties
| Access property name | Upsized as |
| Output All Fields | SELECT * |
| TOP VALUES | TOP X, TOP X% |
| Description | Won't Upsize |
| Unique Values | Won't Upsize |
| Unique Records | Won't Upsize |
| Run Permissions | Won't Upsize |
| Source Database | Won't Upsize |
| Source Connect String | Won't Upsize |
| Record Locks | Won't Upsize |
| Recordset Type | Won't Upsize |
| ODBC Timeout | Won't Upsize |
| Filter | Won't Upsize |
| Order By | Won't Upsize |
| Max Records | Won't Upsize |
| Subdatasheet Name | Won't Upsize |
| Link Child Fields | Won't Upsize |
| Link Master Fields | Won't Upsize |
| Subdatasheet Height | Won't Upsize |
| Subdatasheet Expanded | Won't Upsize |
When you upsize queries that contain Visual Basic for Applications functions, the Upsizing Tools try to map them to equivalent TSQL functions. If the mapping process is unsuccessful, the query will not be migrated to SQL Server.
The Upsizing Wizard only tries to map functions contained in the SELECT list of a query. If functions of any kind are placed in the WHERE clause (the Criteria row of the query design grid), the query will not be upsized.
Table 5 lists functions that should be successfully mapped by the Upsizing Tools when migrating a query.
Table 5. Functions that will Upsize
| avg() | max() |
| ccur() | mid$() |
| cdbl() | mid() |
| chr$() | min() |
| chr() | minute() |
| cint() | month() |
| clng() | right$() |
| count() | right() |
| csng() | rtrim$() |
| cstr() | second() |
| cvdate() | space$() |
| day() | str$() |
| hour() | sum() |
| lcase$() | ucase$() |
| lcase() | ucase() |
| left() | weekday() |
| len() | year() |
| ltrim$() |
Queries are upsized as views or stored procedures on MSDE and SQL Server, depending on the actions performed by the query itself. Generally, select queries are upsized as views, while action queries, such as append, delete, update, and make table queries, are upsized as stored procedures.
Select Queries—The Upsizing Tools classify and divide select queries into one of two categories: base or nested select queries. Base queries reference only tables that exist in the database itself. Nested queries use either queries or a combination of tables and queries as a data source. Generally, any query that depends on another query for its data must be upsized after all its dependent queries.
Whenever possible, select queries are upsized as views. Because SQL Server views do not support all the features of Jet queries (such as the use of the ORDER BY clause), the Upsizing Tools may at times upsize a single query as either:
The following section discusses the situations in which select queries are upsized as views or stored procedures.
Base Select Queries—Base queries are upsized as views unless they contain parameters or an ORDER BY clause.
Examine the following simple Microsoft Access query. It selects data from several fields inside the Employees table.
SELECT
[Employees].[EmployeeID],
[Employees].[LastName],
[Employees].[FirstName],
[Employees].[Region]
FROM
Employees;
The Upsizing Tools transform this query to a view with the following TSQL view syntax:
CREATE VIEW MyQuery
AS
SELECT
Employees.EmployeeID,
Employees.LastName,
Employees.FirstName,
Employees.Region
FROM
Employees
When the Upsizing Tools encounter a query that uses parameters, it must create a stored procedure to handle the incoming information from the user that is used in the WHERE clause. The following query is a standard Microsoft Access parameterized query that prompts the user to enter a region from which to select employees:
SELECT
[Employees].[EmployeeID],
[Employees].[LastName],
[Employees].[FirstName],
[Employees].[Region]
FROM
Employees
WHERE
((([Employees.Region] = [Enter Region])));
The Upsizing Wizard transforms this query into a stored procedure with a single input parameter declared as a variable length character data type up to 255 characters long:
CREATE PROCEDURE "MyParameterQuery" @Enter Region varchar(255)
AS
SELECT
[Employees].[EmployeeID],
[Employees].[LastName],
[Employees].[FirstName],
[Employees].[Region]
FROM
Employees
WHERE
((([Employees.Region] = @Enter Region[)))
SQL Server views do not support the use of ORDER BY (unless accompanied by the TOP keyword). As a result, if you upsize a Microsoft Access query that sort the results on one or more fields, the Upsizing Tools will create two objects:
The following Access query selects several fields from the Employees table, and then sorts the results by the LastName field:
SELECT
[Employees].[EmployeeID],
[Employees].[LastName],
[Employees].[FirstName],
[Employees].[Region]
FROM
Employees
ORDER BY
[Employees].[LastName];
When presented with this query, the Upsizing Wizard will first create a similar view that does not try to sort the results:
CREATE VIEW MyQueryWhichSortsView
AS
SELECT
Employees.EmployeeID,
Employees.LastName,
Employees.FirstName,
Employees.Region
FROM
Employees
Then, the wizard creates a stored procedure that uses ORDER BY against the MyQueryWhichSortsView object:
CREATE PROCEDURE "MyQueryWhichSorts"
AS
SELECT * FROM
MyQueryWhichSortsView
ORDER BY
MyQueryWhichSortsView.LastName
In an Access project, you will run the stored procedure to obtain the same result that you receive from running the query inside your original Access database.
Nested Select Queries—Nested queries reference at least one query as a row source. For the purposes of this discussion, the base query is considered the "final" query that you run to return your results. Nested queries are the SELECT statements that provide data to the final base query. If any of your nested queries fail to upsize, the final base query cannot be migrated.
The Upsizing Wizard always tries to migrate your queries to views, but will create stored procedures under the same conditions that it uses when it creates stored procedures to upsize base select queries.
When upsizing nested select queries, it is important that all queries that provide data to the "final" query:
Views may not use stored procedures as a source of data, so if the Upsizing Wizard upsizes any nested query as a stored procedure, your "final" query will not be able to access the data it returns. As a result, the base query will be skipped by the Upsizing Wizard altogether.
In the following example of how the Upsizing Tools handle a nested, parameterized query, the base query, "QryFinal," selects any employee with a last name of 'Davolio' from the results of the nested query "QryNested." QryNested also includes a parameter that prompts the user to specify what region the employees should live in before they are selected. This combination of queries will run in an Access database with no problems.
QryNested [Incorrect]:
SELECT
Employees.EmployeeID,
Employees.LastName,
Employees.FirstName,
Employees.Region
FROM
Employees
WHERE
(((Employees.Region)=[Enter Region]));
QryFinal [Incorrect]:
SELECT
QryNested.EmployeeID,
QryNested.LastName,
QryNested.FirstName,
QryNested.Region
FROM
QryNested
WHERE
((QryNested.LastName)= 'Davolio'));
The Upsizing Wizard always works with nested queries before their associated base queries. Therefore, QryNested will be upsized first. Because QryNested contains parameters, it will be upsized as a stored procedure.
When the tool turns its attention to QryFinal, the upsizing process will fail: QryFinal must pull data from the QryNested view, and no such object exists.
By modifying the queries above before you try to run the Upsizing Wizard, you can make sure that they are successfully migrated:
QryFinal [Correct]:
SELECT
Employees.EmployeeID,
Employees.LastName,
Employees.FirstName,
Employees.Region
FROM
Employees
WHERE
(((Employees.LastName)= 'Davolio') AND
((Employees.Region) = [Enter Region]));
The quickest solution to the problem above is to simply eliminate the nested query, QryNested. The only useful work QryNested does is to prompt you for a Region; this parameter can easily be moved to QryFinal, which is then upsized as a stored procedure.
Action Queries—Microsoft Access will upsize the following types of queries as a stored procedure:
You can generally upsize base action queries (action queries which only use tables as a data source) with few modifications (this assumes that you are not using functions in your query). The Upsizing Tools do not support upsizing action queries that use other nested queries as a source of data.
The Upsizing Wizard can migrate append and make-table queries to an Access project by re-creating them as stored procedures on your server. Although the wizard can successfully transfer these objects to your Access project, these objects may not run, depending on settings specific to your database or tables.
Upsized Make-Table Queries—Upsized make-table queries use the SELECT INTO statement to create a new table, and then add records to it. For the SELECT INTO statement to successfully execute against a user table, the select into/bulkcopy option for the database hosting the user table must be set to on. By default, SQL Server and MSDE create databases with this option set to off. Use the system-stored procedure sp_dboption to resolve this issue.
Upsized Append Queries—When you upsize a table that contains an AutoNumber field, the Upsizing Wizard converts it to an integer column that uses the IDENTITY property in the upsized table. Using the IDENTITY property with an integer column on SQL Server or MSDE mimics the auto-incrementing behavior of an AutoNumber field in a Jet database.
By default, any column that uses the IDENTITY property on SQL Server or MSDE may not be directly edited (or have new values added to it). As a result, upsized append queries that try to place data in an IDENTITY column will fail unless the IDENTITY_INSERT option for the table in question has been set to on.
For more detailed information on these situations, see the following article in the Microsoft Knowledge Base: "Q229681—ACC2000: Can't Use Upsized Append and Make-Table Queries in an Access Project."
When you choose to use the Create new client/server application option with the Upsizing Tools, all forms in your Microsoft Access 2000 database are copied to a new Access project. Unlike the process the Upsizing Wizard uses to upsize queries, your forms will always be copied into a new Access project, even if errors occur during the migration process.
Forms in Access databases and projects share most properties. Access projects support all form properties found in an Access database and add the following additional form properties shown in Table 6:
Table 6. Access Project-specific form properties
| Max Rec Button | Unique Table |
| Max Records | Resync Command |
| Server Filter | Input Parameters |
| Server Filter by Form |
The function of the properties in Table 6 is detailed in Microsoft Access Help, but this is also discussed in this article, where appropriate.
When the Microsoft Access 2000 Upsizing Wizard migrates a Jet database to an Access Client/Server project, it copies forms in the original database to the new Access project. As the wizard does so, it examines the RecordSource property of the form and the RowSource property of any data-bearing controls (such as combo boxes) on the form itself.
Issues you may encounter while upsizing forms in your database will likely be caused by information in the RecordSource property of your form, or the RowSource property of any data-bound controls on the form. You should pay special attention to the values that you have selected for these properties, because the Upsizing Wizard will behave differently depending on what it finds there.
The Upsizing Tools automatically create a new view or stored procedure and substitute it for the SELECT statement or query name that you originally provide in the form RecordSource or control RowSource property. The only exception to this rule is when your form is bound directly to a table.
When the Upsizing Wizard creates a new view or stored procedure, you should expect it to return the same information that the initial statement or query did in your Access database.
Table 7 describes how the Upsizing Wizard operates when presented with different values in the RecordSource or RowSource property.
Table 7. How the Upsizing Tools migrate the RecordSource and RowSource properties
| Value contained in record/ row source property |
Upsizing wizard action |
| SELECT statement, no ORDER BY | Replace w/ View |
| SELECT statement using ORDER BY | Replace w/ Stored Procedure |
| Query object that does not sort results | Replace w/ View |
| Query object that sorts results | Replace w/ Stored Procedure |
| Query with parameters | Replace w/ Stored Procedure |
| Table name | None, property value is unchanged |
Under certain circumstances, the Upsizing Wizard will fail to correctly replace the RecordSource property of a form while the form is being migrated. As a result, when you try to activate the upsized form in an Access project, an error will be returned and the form will not open.
You will encounter this behavior if the RecordSource property of the form is set to a SELECT statement but does not reference column names in both the SELECT list and ORDER BY clause using the [tablename].[column name] format.
The following SELECT statements will be upsized incorrectly:
SELECT [CategoryId], [CategoryName] FROM [Categories] ORDER BY [CategoryID];
SELECT [Categories].[CategoryId], [Categories].[CategoryName] FROM [Categories] ORDER BY [CategoryID];
SELECT [CategoryId], [CategoryName] FROM [Categories] ORDER BY [Categories].[CategoryID];
Because the following statement uses the [tablename].[column name] format to specify columns in the SELECT list and ORDER BY clause, it can be upsized successfully:
SELECT [Categories].[CategoryId], [Categories].[CategoryName] FROM [Categories] ORDER BY [Categories].[CategoryID];
If a form or subform in your database is based on a SELECT statement, and you also use the OrderBy property to sort your results in the form (rather than including an ORDER BY clause in your SELECT statement), when you upsize the form, the value stored in OrderBy property will be discarded. It is recommended that you include the ORDER BY clause in your SELECT statement rather than use OrderBy. After the form has been upsized, you can specify the fields that you want to sort by in the OrderBy property again if you want.
The Upsizing Wizard handles the Filter property in the same way it does Order By. If the record source of a form or subform uses a SELECT statement, this property is ignored during the upsizing process.
In certain cases, data-bearing controls that worked in your database will not function correctly when upsized. Instead, when you try to activate the combo box or view values in a list box, you receive the error message: "The recordsource 'ut_qry##' specified on this form or report does not exist."
If a combo box or list box contains any of the following values in its RowSource property, you must modify the property manually after the form is upsized:
Example:
The following SELECT statement is generated by the AutoForm functionality of Microsoft Access 2000 and is based on the Products table in the Northwind sample database. The specific statement in the example is created for the SupplierID field (a lookup field) of the Products table.
SELECT DISTINCTROW [SupplierID], [CompanyName] FROM Suppliers ORDER BY
[CompanyName];
This statement will not successfully upsize because each field name is not prefaced with its parent table name. For this statement to upsize properly, it should be modified to read:
SELECT DISTINCTROW [Suppliers].[SupplierID],
[Suppliers].[CompanyName] FROM Suppliers ORDER BY
[Suppliers].[CompanyName];
For more information about this behavior, see the following article in the Microsoft Knowledge Base: "Q229755—ACC2000: Combo Box or List Box Empty After You Use Upsizing Tools."
When you upsize a form in which you have inserted a chart object, the upsizing tool incorrectly migrates the RowSource property of the chart. As a result, charts in your upsized database will appear blank, and you will receive the following two error messages when you open an upsized form that contains a chart:
To resolve this issue, copy the value in the RowSource property of the chart object in your original database and manually paste it into the RowSource property of the form in the Access project.
If you choose to create a new client/server application while upsizing your Microsoft Access 2000 database, the Upsizing Wizard will process reports in much the same way it does forms. In most cases, the wizard automatically replaces the value in the report's RecordSource property with a reference to either a view or stored procedure that is dynamically constructed during the upsizing process.
Any reports in your Microsoft Access 2000 database will be copied to a new Access project, regardless of whether errors occur during the upsizing process. See Table 8.
Table 8. How The Upsizing Tools migrate the RecordSource of a report
| Value contained in RecordSource property |
Upsizing Wizard action |
| SELECT statement, no ORDER BY | Replace w/ View |
| SELECT statement using ORDER BY | Replace w/ Stored Procedure |
| Query object that does not sort results | Replace w/ View |
| Query object that sorts results | Replace w/ Stored Procedure |
| Query with parameters | Replace w/ Stored Procedure |
| Table name | None, property value is unchanged |
As a developer, the report-related upsizing issues you should be aware of are similar to the issues you face when upsizing forms:
After checking your reports for the elements just described, examine the following properties.
In reports, you may set the OrderByOn and FilterOn properties in Design view. When upsizing a report in which these properties are set, OrderByOn will be ignored if the record source of the report is a SELECT statement. However, the value specified in the FilterOn property will be copied, regardless of what is specified in the RowSource property of the report.
In a report that uses grouping, you may not use the Filter property to perform client-side filtering. If you group on a column, specify a value in the Filter property, and set the FilterOn property to Yes, the report will not open, and you will see the following error message:
IRowsOffset would position you past either end of the rowset, regardless of cRows value specified; cRowsObtained is 0
Use the ServerFilter property to implement filtering in a grouped report.
The Microsoft Access Upsizing Tools automatically copy all modules in your database to the new Access project that the Upsizing Tools automatically create. With few exceptions, any upsized code should run normally in your Access project.
It is important to keep in mind that when you work in an Access project, the Jet database engine is not loaded. Therefore, any Data Access Objects (DAO) code that uses the default (Jet) workspace will no longer function. If you need to programmatically open recordsets, or create and manipulate server objects such as tables, you should use the ActiveX® Data Objects (ADO) and Microsoft ADO Ext. 2.1 for DLL and Security (ADOX) libraries, instead of DAO.
For information about the ADO programming model, visit the following Microsoft Web site
www.microsoft.com/data/ado/prodinfo/progmod.htm
or refer to the Microsoft ActiveX Data Objects Help file (Ado210.CHM).
Before you begin upsizing, check all objects in your database and verify that they don't contain an apostrophe (') in their name. If the Upsizing Wizard encounters any object with an apostrophe as part of its name (or as part of its field name), it will not upsize tables in your database. For more information on this behavior, see the following article in the Microsoft Knowledge Base: "Q216218-ACC2000: Tables Not Upsized When Query or Table Name Contains Apostrophe."
If errors prevent the Upsizing Wizard from migrating data from any table in your original database to SQL Server or MSDE, the tool will automatically continue to upsize the structure of all remaining tables in your database. However, it will no longer continue to upsize data. You can use the Data Transformation Services (DTS) Wizard installed by SQL Server or MSDE to transfer remaining data in your original database to the empty tables on SQL Server.
The following sections discuss the process of using the Upsizing Wizard to migrate your data to SQL Server or MSDE.
Before you run the Upsizing Wizard, examine the following "to-do" list:
To run the Upsizing Tools, open the database that you want to upsize. Then on the Tools menu, point to Database Utilities, and click Upsizing Wizard. The wizard will prompt you to make several decisions as follows.
On the first screen of the Upsizing Wizard, select whether you want to use an existing database or create a new database to host the data you are about to upsize.
If you choose to use an existing database, you must provide a Data Source Name for the database in question.
When you create a new database, you will be asked to specify the following information:
Next, select the tables that you want to migrate to SQL Server or MSDE. Unless you have an overriding reason not to do so, select all the tables in your database.
After you have selected which tables to upsize, specify what attributes of the tables should be moved to SQL Server. Normally, you should upsize all table attributes.
Indexes—When you select the Indexes check box, any indexes on your Access tables are re-created on SQL Server or MSDE.
All primary keys on your Access tables are converted to primary keys (non-clustered, unique indexes) on SQL Server. If you want to re-create a primary key as a clustered index, you must do so manually after the Upsizing Wizard completes its work.
Upsized indexes retain their original names unless characters that cannot be used in SQL Server Identifiers are used in the original database. Any "illegal" characters are replaced with the underscore (_) symbol by the Upsizing Wizard.
Note If you need to update or add data to upsized tables from an Access project or via linked ODBC tables, the table in question must have either a primary key or a unique index.
Validation Rules—When you select the Validation Rules check box, all table, record, and field validation rules are upsized as UPDATE and INSERT triggers by the Upsizing Wizard.
Defaults—Any defaults for fields in your Access tables are upsized if you select this option. Note that defaults are upsized as default constraints (using Declarative Referential Integrity, or "DRI"). Defaults are not upsized as SQL Server default objects, which must then be bound to individual columns or user-defined data types.
Table Relationships—You can upsize table relationships in one of two ways:
Adding Timestamps—Generally, you should allow the wizard to decide whether to add timestamps to your tables.
If you use a timestamp column in a table, each time that a record is modified, its timestamp column is also updated with a value that represents the last time the record was changed. Adding timestamp fields can enhance performance, especially with tables that contain floating-point numbers, OLE Object fields, or Memo fields.
Note The value stored in a timestamp column is not visible inside an Access project, and you cannot directly edit the value stored in the column itself.
Create Table Structure Only—By default, the Upsizing Tools automatically migrate your table structure and data to SQL Server. Under certain circumstances (such as running out of room in your transaction log), you may find it useful to upsize the structure of your tables only, and then migrate your data to SQL Server later.
When you run the Upsizing Wizard, you can simply upsize your data to a SQL Server or MSDE database. However, the wizard offers additional options that are useful for the developer of a client/server application.
No Application Changes—The No Application Changes option is the default option. When you select it, data in your database is moved to SQL Server or MSDE, and no changes are made to your original database.
When you select the No Application Changes option, only tables in your database are copied to SQL Server or MSDE.
Link SQL Server Tables to Existing Application—When you select the Link SQL Server Tables to Existing Application option, tables in your database are first upsized to SQL Server. Then, the wizard links your newly migrated tables that now reside on SQL Server or MSDE back to your database using ODBC.
Your forms, reports, queries, and data access pages use the linked tables as their data source rather than the local tables.
The Upsizing Tools automatically rename local tables in your database by adding "_local" to the end of the original object name, so that you can easily identify the objects that exist locally and those that exist on the server.
When you choose the Link SQL Server Tables to Existing Application option, only the tables in your database are upsized.
You cannot modify the design of linked tables from an Access database. If you plan to make adjustments to your tables, select the Create A New Access Client/Server Application option.
Creating a New Access Client/Server Application—The final option offered by the wizard, Creating a New Access Client/Server Application, is the most useful. If you select it, both tables and queries in your database are upsized to SQL Server or MSDE. After your tables and queries are copied, forms, reports, macros, modules, and data access pages are also copied to a new Access project created by the wizard.
Save Password and User ID—By default, the User ID and Password that you use to connect to SQL Server is not saved in your database or project. If you select the Save Password and User ID option, the following occurs:
Generally, you should not save your Password and User ID. Doing so weakens security on your server and database.
Note If you try to upsize to an existing SQL Server or MSDE database and it contains the MSysConf table and the configuration value that prevents local storage of the Login ID and password for linked tables, this option will be disabled. For more information about the MSysConf table, see Microsoft Access Help.
After you specify which options the Upsizing Wizard should use, click Finish. The wizard will begin migrating your tables to SQL Server.
Use the Upsizing Report as a permanent record of the work completed by the wizard or as a troubleshooting tool to help you determine why particular objects in your database failed to upsize.
You can immediately print the Upsizing Report or use the snapshot of the report, which is automatically created and saved for you.
It is not uncommon for the Upsizing Wizard to fail to upsize some objects in your original Access database. When this happens, you can re-create the objects on your server. Use different methods to re-create each type of object on your server, as follows.
When the Upsizing Wizard fails to migrate a particular table in your database, you can re-create it using one of two methods:
The quickest way to manually re-create a table on SQL Server or MSDE database is to use Data Transformation Services (DTS). Both MSDE and SQL Server include a wizard that allows you to take advantage of DTS without needing to have advanced knowledge of the technology. The wizard is named "Import and Export Data" and can be found in the MSDE or SQL Server 7.0 program group.
Note DTS does not try to migrate all information about your table. DTS simply creates the "base" structure of a table on your server; a primary key, indexes, validation rules, default values, and so on, are not re-created on the server. Because DTS ignores all properties of each field in your tables except for data type, it is a useful tool if you don't care to examine each table that failed to upsize to find out why the Upsizing Tools couldn't upsize it.
Keep in mind that if you use DTS to copy a table to your server, you must manually create constraints, indexes, defaults, and so on, on the table.
Generally queries that do not successfully upsize fall into one of three categories:
Queries That the Upsizing Wizard Is Not Designed to Upsize—The "Queries That Are Not Upsized" topic earlier in this article discusses several types of queries that the Upsizing Wizard does not try to upsize. Use Table 9 to determine how you can re-create an equivalent object on SQL Server or MSDE.
Table 9. Recreating Queries on SQL Server
| Access object | How to create a functional equivalent on SQL Server or MSDE |
| Crosstab query | View the SQL Server 7.0 Books Online Help topic "Crosstab reports." |
| Action query that takes parameters | View the SQL Server 7.0 Books Online Help topic "CREATE PROCEDURE." Use input parameters and either an UPDATE, INSERT, or DELETE statement together in a stored procedure. |
| Action query that contains nested queries | View the SQL Server 7.0 Books Online Help topic "CREATE PROCEDURE." Reference view names inside a SELECT statement contained in a stored procedure. |
| SQL pass-through query | Not Applicable |
| SQL DDL query | View the SQL Server 7.0 Books Online Help topic "CREATE PROCEDURE." Use DDL statements inside a stored procedure. |
| Union query | View the SQL Server 7.0 Books Online Help topic "Combining Results with UNION." |
| Query that takes a form references as parameters | View the SQL Server 7.0 Books Online Help topic "CREATE PROCEDURE." Use a stored procedure with input parameters in conjunction with the InputParameter property of an Access form. |
Queries That Use Visual Basic for Applications Functions with no Simple Transact-SQL Equivalent—Many Visual Basic for Applications functions have equivalent Transact-SQL functions on SQL Server to carry out the same or similar work. The Upsizing Wizard will "map" these functions whenever possible. If the tool successfully maps a Visual Basic for Applications function to Transact-SQL, the query will likely upsize. If the wizard fails to map a Visual Basic for Applications function, the query will not upsize.
The following Tables 10 and 11 contain TSQL functions that you can use inside a stored procedure to return the same information as its Visual Basic for Applications counterpart. Note that the lists include only functions that the Upsizing Wizard will not successfully map automatically.
Table 10. Equivalent String Functions
| Visual Basic for Applications function |
TSQL equivalent | Parameters | Result |
| ASC() | ASCII | (char_expr) | ASCII code value of leftmost character. |
| INSTR() | CHARINDEX | ('pattern', expression) | Returns the starting position of the specified pattern. |
| LOWER() | LOWER | (char_expr) | Converts to lowercase. |
| LTRIM() | LTRIM | (char_expr) | Data without leading blanks. |
| STRING() | REPLICATE | (char_expr, integer_expr) | Repeats a character expression a specified number of times. |
| STRREVERSE() | REVERSE | (char_expr) | Returns the reverse of char_expr. This function takes a constant, variable, or column as its parameter. |
| SPACE() | SPACE | (integer_expr) | Returns a string of repeated spaces. The number of spaces is equal to integer_expr. If integer_expr is negative, a null string is returned. |
| STR() | STR | (float_expr [, length [, decimal]]) |
Returns character data converted from numeric data. The length is the total length, including decimal point, sign, digits, and spaces. The decimal value is the number of spaces to the right of the decimal point. |
| UPPER() | UPPER | (char_expr) | Converts lowercase character data to uppercase. |
Table 11. Equivalent Date/Time Functions
| Visual Basic for Applications function |
TSQL equivalent | Parameters | Results |
| DATEADD() | DATEADD | (datepart, number, date) | Adds the number of dateparts to the date. |
| DATEDIFF() | DATEDIFF | (datepart, date1, date2) | Number of dateparts between two dates. |
| FORMAT() | DATENAME | (datepart, date) | Specified datepart for the listed date, returned as an ASCII value (for example, July). |
| DATEPART() | DATEPART | (datepart, date) | Specified datepart for the listed date, returned as an integer value (for example, 7). |
| NOW(), DATE() | GETDATE | ( ) | Current date and time in internal format. |
Queries That Use Nested Queries as a Record Source—If a nested query fails to upsize, the base query that uses its results will not upsize either. Normally, the wizard is able to complete some of the work necessary to create the final query. You can use or modify the existing "intermediate" objects created by the wizard rather than re-create everything on your own.
Example:
QryNested:
SELECT
Employees.EmployeeID,
Employees.LastName,
Employees.FirstName,
Employees.Region
FROM
Employees
WHERE
(((Employees.Region)=[Enter Region]));
QryFinal
SELECT
QryNested.EmployeeID,
QryNested.LastName,
QryNested.FirstName,
QryNested.Region
FROM
QryNested
WHERE
(((QryNested.LastName)= 'Davolio'));
If you try to upsize the combination of Access queries just discussed, qryFinal will fail to migrate. However, qryNested will be upsized as an "intermediate" stored procedure:
CREATE PROCEDURE QryNested @Enter_Region varchar (255)
AS
SELECT
Employees.EmployeeID,
Employees.LastName,
Employees.FirstName,
Employees. Region
FROM
Employees
WHERE (((Employees. Region)=@Enter_Region))
You can modify the WHERE clause in this stored procedure to not only reflect the @Enter_Region input parameter, but the 'Davolio' condition from QryFinal:
ALTER PROCEDURE QryNested @Enter_Region varchar (255)
AS
SELECT
Employees.EmployeeID,
Employees.LastName,
Employees.FirstName,
Employees. Region
FROM
Employees
WHERE (((Employees. Region)=@Enter_Region))AND Employees.LastName = 'Davolio'
After you save the changes to this stored procedure, it will return the same results that QryFinal did in your original database.
After you have upsized your database, you may want to take full advantage of the rich feature set of SQL Server or MSDE in your application. The best place to start is to SQL Server 7.0 Books Online. SQL Server 7.0 Books Online is free and can be downloaded from
http://support.microsoft.com/download/support/mslfiles/sqlbol.exe.
--------------------------------------------
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This article is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.
Microsoft is a registered trademark of Microsoft Corporation.
Other product or company names mentioned herein may be the trademarks of their respective owners.
Microsoft Corporation · One Microsoft Way · Redmond, WA 98052-6399 · USA