White Paper Template



[pic]

Microsoft( Access 2000 Upsizing Tools

Using the Access 2000 Upsizing Tools

Microsoft Product Support Services White Paper

Published October 1999

Abstract

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 SQL Server 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 white paper is designed to show you how to use the Access 2000 Upsizing Tools.

© 1999 Microsoft Corporation. All rights reserved.

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 White Paper 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

.

Contents

Introduction 1

Version Support 1

SQL Server 7.0 1

MSDE 1

SQL Server 6.5 2

Unsupported Versions of SQL Server 2

New Features and Enhancements 2

Creating New Client/Server Applications 2

Unattended Upsizing 2

Silent Errors 2

Tables Automatically Renamed 2

Automatic Log Truncation 2

Linked Tables Can Be Upsized 3

Error Message Text for Triggers 3

Wizard Defaults to "Create New Database" 3

Hidden Objects Are Not Upsized 3

Upsizing Report Improvements 3

Better Error Details 3

Automatic Snapshot Report 3

Naming Rules 4

The Upsizing Process 4

Preparing to Upsize 4

Security 4

Jet Security Settings Not Migrated 4

Permissions Necessary to Upsize 4

Tables 5

DefaultValue and ValidationRule Properties 5

ValidationText Property 6

The Format and InputMask Properties 6

The Caption Property 7

The AllowZeroLength Property 7

Required 7

Indexed 7

Data Types 8

Lookup Fields 9

Queries 9

Queries That Are Not Upsized 10

Properties That Are Not Upsized 10

Upsizing Queries with Functions 11

How Queries Are Upsized 12

Queries That Are Upsized But That Won’t Run 15

Forms 16

Properties 16

How Forms Are Upsized 16

Form RecordSource Property 17

The OrderBy Property 17

The Filter Property 18

Data-Bearing Controls 18

Charts 18

Reports 19

OrderByOn and FilterOn Properties 19

Client/Side Filtering and Grouping 20

Modules 20

Data Access Objects 20

Miscellaneous Issues 20

Naming Issues 20

Upsizing Logic 20

Running the Upsizing Tools 20

Gathering Necessary Information 21

Running the Wizard 21

Existing or New Database? 21

Select Tables to Upsize 21

Choosing Which Table Attributes to Upsize 21

Modifying the Application 22

Finishing 23

Examining the Upsizing Report 23

Recreating Objects on SQL Server or MSDE 24

Tables 24

Queries 24

Learning More about SQL Server 7 and MSDE 28

Introduction

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 or the Microsoft Data Engine (MSDE), a programmer can seamlessly develop both the “back-end” and user interface portions of an application.

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 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.

Version Support

The Microsoft Access 2000 Upsizing Tools support the following database servers:

SQL Server 7.0

You can use the Upsizing Tools with all versions of SQL Server 7.0, including the following:

• SQL Server 7.0 Desktop Edition

• SQL Server 7.0 Standard Edition

• SQL Server 7.0 Enterprise Edition

MSDE

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 the Microsoft Data Engine via Microsoft Access Client/Server or the Upsizing Tools. If this occurs, you should contact the software manufacturer of the third-party application to determine how MSDE was configured during installation:

• Has the sa (System Administrator) password for MSDE been changed?

• Have permissions for the System Administrator been modified?

• Was MSDE configured to use the default code page (1252) or another code page altogether?

• What sort order has MSDE been configured with?

• What is the Unicode Collation setting?

• What Network Libraries are installed?

SQL Server 6.5

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.

Unsupported Versions of SQL Server

You cannot use SQL Server versions 4.x or 6.0 with the Microsoft Access 2000 Upsizing Tools.

New Features and Enhancements

The following sections describe changes and additions made to the Upsizing Tools in Microsoft Access 2000.

Creating New Client/Server Applications

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.

Unattended Upsizing

Two changes have been made to the Upsizing Wizard to allow large databases to be upsized while unattended.

Silent Errors

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.

Tables Automatically Renamed

If the name of a table in your Access database already exists in the SQL Server or MSDE database that you are upsizing to, the upsized table is made unique by appending a number to the end of the table name.

Automatic Log Truncation

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.

Linked Tables Can Be Upsized

You can use the Microsoft Access 2000 Upsizing Tools to copy tables linked to SQL Server and MSDE. You should not use this functionality as your primary method of moving ISAM and ODBC-based data to your SQL Server, however. Instead, use the Data Transformation Services available with MSDE and SQL Server 7.0.

Error Message Text for Triggers

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.

Wizard Defaults to “Create New Database”

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.

Hidden Objects Are Not Upsized

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.

Upsizing Report Improvements

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.

Better Error Details

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.

Automatic Snapshot Report

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 saved as a snapshot report in the same folder as the database being upsized.

Naming Rules

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 Process

The Upsizing Tools can be used to quickly migrate data 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:

• Prepare your database for upsizing.

• Run the wizard.

• Examine the Upsizing Report.

• Re-create objects, rules, defaults, and so on, on the server that were not successfully upsized.

Preparing to Upsize

Before you run the Upsizing Wizard to create a client/server application, take the time to examine your database to find and correct possible trouble spots.

Security

Jet Security Settings Not Migrated

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, available for download at .

Permissions Necessary to Upsize

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, while 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 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.

Tables

Default Value and Validation Rule Properties

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 (VBA), not all VBA 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:

• If the Upsizing Wizard fails to map a function in a field’s ValidationRule property, only the validation rule is skipped, and the rest of the table is upsized.

• If the Upsizing Wizard fails to map a function in a field’s DefaultValue property, the entire table is skipped.

The following table lists Visual Basic for Applications functions that may be used in the DefaultValue or ValidationRule property of your fields.

|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$() | |

Table 1 - Upsizeable funtions

ValidationText Property

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.

The Format and InputMask Properties

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.

• All formatting displayed as a result of using the Format property will be lost when the data is migrated to SQL Server or MSDE.

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).

• Formatting created by using the Input Mask property may or may not be lost when upsized to SQL Server or MSDE. If you create the input mask on your field using the Input Mask Wizard and choose the “Stored Data without the symbols in the mask” option, your formatting will be lost when upsized.

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

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 AllowZeroLength Property

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.”

Required

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.

Indexed

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 describes the behavior that you should expect, based on the selections that you make for the Indexed property of a field.

|Indexed property |Index created by Upsizing Tools |

|No |None |

|Yes (Duplicates OK) |Non Clustered Index |

|Yes (No Duplicates) |Unique, Non Clustered Index |

Table 2 - Indexing and the Indexed Property

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:

• A column in your table contains more than one null value.

• The same column’s Required property is set to No, and its Indexed property is set to Yes (No Duplicates).

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.”

Data Types

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.

The following table details how the Upsizing Tools map data types during migration.

|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 |

Table 3 - Jet to SQL data type mapping

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.

The 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:

• The first record contains a hyperlink that jumps to . The hyperlink itself is displayed as Microsoft in the table, and has a Screen Tip that reads “Click me to jump to MS web site.”

• The second record contains a hyperlink that jumps to Form1 in your database. The hyperlink is displayed as Form1 and has a Screen Tip that reads “Click me to open Form1.”

You run the Microsoft Access 2000 Upsizing Tools and choose to create a new Access client/server application. After the wizard is finished, you view the data contained in what used to be your Hyperlink field. You note that the information no longer functions as a hyperlink jump, and that additional information is visible that wasn’t before:

Microsoft# me to jump to MS web site

Form1##Form Form1#Click me to open Form1

Lookup Fields

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.

Queries

This section of the white paper 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.

Queries That Are Not Upsized

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:

• Crosstab queries

• Action queries (append, delete, make-table, update) that take parameters

• Action queries that contain nested queries

• SQL pass-through queries

• SQL Data Definition Language (DDL) queries

• Union queries

• Queries that reference values on a form

You must manually re-create queries that the Upsizing Tools do not migrate.

Properties That Are Not Upsized

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. The following table details which properties can be upsized and which cannot.

|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 |

Table 4 - Upsizeable Query Properties

Upsizing Queries with Functions

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.

The following table lists functions that should be successfully mapped by the Upsizing Tools when migrating a query.

|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$() | |

Table 5 - Functions that will Upsize

How Queries Are Upsized

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:

• A view that feeds intermediate results to a final stored procedure.

• A stored procedure.

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 to select employees from:

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:

• A view that SELECTS the columns of your choice.

• A stored procedure that applies the ORDER BY clause to a newly created view.

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:

• Do not contain parameters.

• Do not contain the ORDER BY statement.

• Do not contain any keywords that may cause the query to be upsized as a stored procedure.

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:

• Append queries

• Delete queries

• Make-table queries

• Update queries

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.

Queries That Are Upsized But Won’t Run

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.”

Forms

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.

Properties

Forms in Access databases and projects share most properties. Access projects support all the form properties found in an Access database, and add the following additional form properties.

|Max Rec Button |Unique Table |

|Max Records |Resync Command |

|Server Filter |Input Parameters |

|Server Filter by Form | |

Table 6 - Access Project-specific form properties

The function of the properties in Table 6 is detailed in Microsoft Access Help, but they are also discussed in this white paper where appropriate.

How Forms Are Upsized

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.

The following table describes how the Upsizing Wizard operates when presented with different values in the RecordSource or RowSource property.

|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 |

Table 7 - How the Upsizing Tools migrate the RecordSource and RowSource properties

Form RecordSource Property

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];

The OrderBy Property

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 Filter Property

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.

Data-Bearing Controls

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:

• A query that uses parameters

• A query or SELECT statement that references a control on a form in its WHERE clause

• A SELECT statement that fails to reference columns in the SELECT list or ORDER BY clause using the form [tablename].[fieldname]

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.”

Charts

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:

The recordsource 'ut_qry##' specified on this form or report does not exist.

-and-

An error occurred while sending data to the OLE Server (the application used to create the object.)

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.

Reports

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.

|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 |

Table 8 - How The Upsizing Tools migrate the RecordSource of a report

As a developer, the report-related upsizing issues that you should be aware of are similar to the issues that you face when upsizing forms:

• Upsized charts will appear blank until you manually reset their RowSource property.

• References to column names in SELECT statements contained in the RowSource property of a report must use the format [tablename].[columnname]. Failure to use this format will cause errors when you try to open the upsized report.

• Values specified in the RowSource property of a list box or combo box must adhere to the same standards mentioned in the “Data-Bearing Controls” section of the Forms topic.

• If you use a SELECT statement in the RowSource property of your report, any values stored in the OrderBy and Filter properties will be discarded during the upsizing process.

After checking your reports for the elements just described, examine the following properties.

OrderByOn and FilterOn 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.

Client-Side Filtering and Grouping

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.

Modules

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.

Data Access Objects

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



or refer to the Microsoft ActiveX Data Objects Help file (Ado210.CHM).

Miscellaneous Issues

Naming Issues

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, 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.”

Upsizing Logic

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 DTS Wizard installed by SQL Server or MSDE to transfer remaining data in your original database to the empty tables on SQL Server.

Running the Upsizing Tools

The following sections discuss the process of using the Upsizing Wizard to migrate your data to SQL Server or MSDE

Gathering Necessary Information

Before you run the Upsizing Wizard, examine the following “to-do” list:

• A printer driver must be installed on the computer that you run the wizard on in order to render the Upsizing Report after the upsizing process is complete.

• If you plan to create a new SQL Server database to upsize your Access data to, get account information (login name and password) for a SQL Server or MSDE login account that has CREATE DATABASE permissions.

• If you intend to upsize data to a pre-existing SQL Server or MSDE database, make sure that you have created a Data Source Name (DSN) that points to the database in question and that uses a login account with permissions to create objects in the target SQL Server database.

Running the Wizard

To run the Upsizing Tools, open the database that you want to upsize, and then on the Tools menu, point to Database Utilities, and then click Upsizing Wizard. The wizard will prompt you to make several decisions as follows.

Existing or New Database?

On the first screen of the Upsizing Wizard, select whether you want to use an existing database or to 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:

• The name of the SQL Server to create the database on.

• A login ID and password for an account that has CREATE DATABASE permissions on the server.

• The name that will be used for the newly created database.

Select Tables to Upsize

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.

Choosing Which Table Attributes to Upsize

After you have selected which tables to upsize, you should 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 then must be bound to individual columns or user-defined data types.

Table Relationships

You can upsize table relationships in one of two ways:

• DRI – Use Declarative Referential Integrity (DRI) in most cases: DRI creates relationships between your tables during the process of table creation itself.

• Triggers – DRI does not support cascading updates and deletes or cross-database referential integrity. If you need to implement either, use triggers instead of DRI.

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 that 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.

Modifying the Application

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 Sever or MSDE back to your database using ODBC.

Your forms, reports, queries, data access pages, and queries 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 on making 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:

• In a database with linked tables, users can access data on the server without specifying a password.

• In an Access project, the user name and password are automatically stored in the connect string used by the project to connect to the server.

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.

Finishing

After you specify which options the Upsizing Wizard should use, click Finish. The wizard will begin migrating your tables to SQL Server.

Examining the Upsizing Report

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.

Re-creating Objects on SQL Server or MSDE

It is not uncommon for the Upsizing Wizard to fail to upsize some of the objects in your original Access database. When this happens, you can re-create the objects on your server. You use different methods to re-create each type of object on your server, as follows.

Tables

When the Upsizing Wizard fails to migrate a particular table in your database, you can re-create it by using one of two methods:

• Address the reasons why the table did not import (functions in the DefaultValue property of a field, for example), and then rerun the Upsizing Tools to migrate the specific table.

• Use Data Transformation Services (DTS)

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 having 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 that DTS does not try to migrate all of the 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.

Queries

Generally queries that do not successfully upsize fall into one of three categories:

• Queries that the Upsizing Wizard is not designed to upsize.

• Queries that use Visual Basic for Applications functions with no simple Transact-SQL equivalent.

• Queries that use other nested queries as a record source.

Queries That the Upsizing Wizard Is Not Designed to Upsize

The Queries That Are Not Upsized topic earlier in this paper discusses several types of queries that the Upsizing Wizard does not try to upsize. Use the following table to determine how you can re-create an equivalent object on SQL Server or MSDE.

|Access object |How to create a functional equivalent on SQL Server or MSDE |

|Cross-tab query |View the SQL Server 7 Books Online Help topic “Cross-tab reports.” |

|Action query that takes parameters |View the SQL Server 7 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 |View the SQL Server 7 Books Online Help topic “CREATE PROCEDURE.” |

|queries |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 Books Online Help topic “CREATE PROCEDURE.” Use |

| |DDL statements inside a stored procedure. |

|Union query |View the SQL Server 7 Books Online Help topic “Combining Results with |

| |UNION.” |

|Query that takes a form references |View the SQL Server 7 Books Online Help topic “CREATE PROCEDURE.” Use |

|as parameters |a stored procedure with input parameters in conjunction with the |

| |InputParameter property of an Access form. |

Table 9 – Recreating Queries on SQL Server

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 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 only functions that the Upsizing Wizard will not successfully map automatically are included in the lists.

|Visual Basic for |TSQL equivalent |Parameters |Result |

|Applications function | | | |

|ASC() |ASCII |(char_expr) |ASCII code value of leftmost|

| | | |character. |

|INSTR() |CHARINDEX |('pattern', |Returns the starting |

| | |expression) |position of the specified |

| | | |pattern. |

|LOWER() |LOWER |(char_expr) |Converts to lowercase. |

|LTRIM() |LTRIM |(char_expr) |Data without leading blanks.|

|STRING() |REPLICATE |(char_expr, |Repeats a character |

| | |integer_expr) |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 |Returns character data |

| | |[, decimal]]) |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 10 - Equivalent String Functions

|Visual Basic for |TSQL equivalent |Parameters |Results |

|Applications function | | | |

|DATEADD() |DATEADD |(datepart, number, |Adds the number of |

| | |date) |dateparts to the date. |

|DATEDIFF() |DATEDIFF |(datepart, date1, |Number of dateparts between|

| | |date2) |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. |

Table 11 - Equivalent Date/Time Functions

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.

Learning More about SQL Server 7.0 and MSDE

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 available for download at:



................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download