Preparing an Access Database for Upsizing to SQL Server

To convert an Access database (or just its tables) to SQL Server (this is known as upsizing), you need to run the Upsizing Wizard, as described in the "Using the Upsizing Wizard" section later in this chapter. However, before you run the Upsizing Wizard to upsize an Access database to SQL Server 2005 or SSE, you need to make some preparations in your database:

■ Make sure that each table has a unique index, because a SQL Server can't update a table that lacks a unique index.

■ Make any hidden tables visible (see sidebar that follows), because the Upsizing Wizard can't upsize hidden tables.

■ Compile all the code, and correct any errors.

Making Hidden Tables Visible

To make hidden tables visible, click File O Access Options, select the Current Database page, and click the Navigation Options button:

Current Database

Datasheet Object Designers Proofing Advanced Customization

Trust Center Resources

Options for the current database. Application Options

Application Jit le; Application Icon:

Morthwind

[ Browse,,. J

□ Use as Form

and Report Icon

Display Form:

fmrvuMain

" 1

[7] Display Status

Sar

Document Window Options

o Overlapping Windows

© Tabbed Documents

171 Display Document Tabs

171 Use Access Special Keys •

E Compact on Close

B Remove person

al Information from fi

e properties o

rv save

IP] Use Windows-themed Controls on Forms

171 Enable Layout View for this database

B Check for trunc

ated number fields

Picture Property Storage Format

© Preserve so

rce image format (sm

Her file size]

a Convert all

icture data to bitmap

(compatible

uvlth Access 2003 and

Navigation

[7] Display Navigation Pane

Navigation Option s.n

The Navigation Options button on the Current Database page of the Access Options dialog. On the Navigation Options dialog, check the "Show Hidden Options" checkbox:

Navigation Options

Grouping Options

Click on a Category to change the Category display order or to add groups

Categories

Groups for Tables and Related Views"

Tables and Related Views

El

tb Customers

i

Object Type

m

tblOrders

Custom

M

tblEmployees

El

tblShippers

=

El

tblOrderDetails

El

tblProducts

El

tbllnfb

El

tJkpReports

Add Item |

Add Group |[ Delete Group |[ Rename Group

Display Options

51 jShow Hidden Objects; 0 Show System Objects CrShow Search Bar

Open Objects with

Single-dick a Double-dick

Making hidden objects visible in the Navigation Options dialog.

You may have code in an Access database that was originally created many versions ago, but you haven't upgraded it because it still runs. Before upsizing to SQL Server, you should make sure that all your code is up to date, because SQL Server is much less forgiving than Access.

I made a sample database for this chapter, Basic Northwind.accdb, based on the old Northwind sample database that came with several previous versions of Access. Because Northwind was originally created many versions ago, and has only been minimally upgraded over Access versions, there is a good deal of old code in this database — some of it very old code indeed: Access 95 or earlier. Before upsizing, I took the opportunity to update all the code to the current syntax.

For example, the old Northwind code uses the IsLoaded function, provided in a module. Many versions of Access ago, this function was needed, but since Access 2000, you don't need a special function to check whether a form is loaded—just use the IsLoaded property of the form, as an item in the AllForms collection. Here is some typical code for returning to the main menu, as used in a standard Form_Close event procedure:

Dim prj As Object

Set prj = Application.CurrentProject

If prj.AllForms("fmnuMain").IsLoaded = True Then

Forms![fmnuMain].Visible = True Else

DoCmd.OpenForm "fmnuMain" End If

Some features that are supported in Access applications won't survive upsizing, unfortunately. Functions called from calculated expressions in queries are not supported in SQL Server, so when you upsize a query that uses functions in calculated field expressions, you will get an error. I recommend removing the functions from query calculated expressions before upsizing; after the database is upsized, you can modify the corresponding stored procedure or user-defined function as needed in a way that will work in SQL Server.

0 0

Post a comment