Migrating Microsoft Access 2000 Queries to SQL Server 2000
The Microsoft Access Upsizing Wizard utility ships with Microsoft Office 2000 Professional
Edition and allows create an equivalent database on SQL Server with the same table structure,
data, and many other attributes of the original Access database. The Upsizing Wizard can
recreate table structures, indexes, validation rules, defaults, autonumbers, and relationships,
but cannot upsize certain types of queries. This article describes how you can migrate Microsoft
Access 2000 queries to SQL Server 2000 by using the Upsizing Wizard, and how you should prepare
your queries so that they can be successfully migrated.
When you run Microsoft Access 2000 Upsizing Wizard utility with the "Create a new Access
client/server application" option, the wizard tries to convert all Access queries to SQL Server
views or stored procedures. Some Access queries will not be upsized, some queries will be upsized
successfully and will run without previous corrections, and some queries will be upsized
successfully but will run only after setting some database options.
By default, Upsizing Wizard tries to upsize all Access data to SQL Server, but you can select
the "Only create table structure, don't upsize any data" check box to specify that only the
data structure will be upsized. Try to migrate the Access database structure first, and migrate
your data only after all your queries migrated the way that you want them.
Queries that are not upsized
Some types of Microsoft Access 2000 queries will not be upsized by Upsizing Tools. You should
manually recreate these queries if you need them. The following queries will not be upsized:
- Crosstab queries
- Action queries 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
The crosstab queries returns data by two sets of facts: one down the left side and the other
across the top of the datasheet. You should manually replace such queries with SQL Server base
queries or nested queries.
The action queries are the delete, append, make-table and update queries. You should manually
replace action queries that take parameters and action queries that contain nested queries with
SQL Server stored procedures.
Try to avoid using SQL pass-through queries or replace them with SQL Server base queries.
Separate Access SQL Data Definition Language (DDL) queries to SQL Server DDL statements and
SQL Server queries.
Replace Access union queries with SQL Server views, base queries, or nested queries.
Replace Access queries that reference values on a form with SQL Server base queries
Upsizing base select queries
The Microsoft Access 2000 base select queries are upsized as SQL Server 2000 views or
stored procedures. Usually Access base query is upsized as view, but sometimes it
can be upsized as a couple of objects: view and stored procedure. The Access base query
is upsized as two objects (view and stored procedure) only when SQL Server view's
syntax does not support some Access query features.
For example, SQL Server view does not support the ORDER BY clause, so the Microsoft
Access 2000 base select query, which contains the ORDER BY clause, cannot be upsized
to SQL Server 2000 view. In this case, such Access query will be upsized to:
- a view that selects the data
- a stored procedure that applies the ORDER BY clause to a newly created view.
Upsizing nested select queries
A nested select query is a SELECT statement that contains one or more subqueries.
The Upsizing Wizard migrates nested select query from the last subquery up to the
base SELECT statement. So, if any of the subqueries fail to upsize, the base SELECT
statement cannot be migrated also.
The Upsizing Wizard always tries to migrate the nested select queries to views, but
sometimes the wizard must create two objects (view and stored procedure) to upsize the
subquery, see previous title "Upsizing base select queries". Because views cannot use
stored procedures as a source of data, so if the Upsizing Wizard upsized any nested
query as a stored procedure, the upper query will not be able to access the data it
returns and the base query will be skipped by the Upsizing Wizard.
The nested select queries will be upsized to views (without stored procedures), when
the following conditions are met:
- all subqueries do not contain parameters
- all subqueries do not contain the ORDER BY statement.
- all subqueries do not contain any keywords that may cause the query to be upsized
as a stored procedure.
Upsizing action queries
The Microsoft Access 2000 action queries are the delete, append, make-table and update
queries. The Upsizing Wizard always migrate the action queries to stored procedures.
Remember, that Upsizing Wizard cannot migrate the action queries that take parameters
and the action queries that contain nested queries. You should manually replace action
queries that take parameters and action queries that contain nested queries with SQL Server
Upsized queries that won't run
Though Upsizing Tools successfully migrate append and make-table queries to SQL Server
stored procedures, these stored procedures may not run. In this case, you should manually
update the SQL Server database's or table's settings.
For example, a make-table query uses the SELECT INTO statement to create a new table,
but the "select into/bulkcopy" option for the SQL Server 2000 database is turned off.
In this case, the table will not be created. To allow SELECT INTO statement, the
"select into/bulkcopy" database option must be turned on. By default, this option is
turned off. You can use the sp_dboption system stored procedure to turn on the
"select into/bulkcopy" database option.
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'ON'
Another example of the upsized queries that won't run: the Upsizing Tools convert
the Microsoft Access AutoNumber field to SQL Server integer field with the IDENTITY
property and the upsized append query tries to place data in an IDENTITY column.
By default, the IDENTITY column cannot be updated manually, so you will get the error
message. To resolve this problem, you should set the SQL Server IDENTITY_INSERT option
SET IDENTITY_INSERT jobs ON
Alexander Chigrik is from Rostov-on-Don in Russia. He works as a Microsoft SQL Server DBA and developer.
Alexander has more than 14 years solid experience with Microsoft SQL, as he started with version 6.5 in 1996 and now works with SQL Server 2008.