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 origi-
nally 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 IsLoadedfunction, 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 IsLoadedproperty 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_Closeevent procedure:
Dim prj As ObjectSet prj = Application.CurrentProjectIf prj.AllForms(“fmnuMain”).IsLoaded = True Then
Forms![fmnuMain].Visible = True
Else
DoCmd.OpenForm “fmnuMain”
End IfSome 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 rec-
ommend removing the functions from query calculated expressions before upsizing; after the data-
base is upsized, you can modify the corresponding stored procedure or user-defined function as
needed in a way that will work in SQL Server.
Configuring SQL Server 2005 for Data Access ..................................................................
In Access 2003, you didn’t need to do any special SQL Server setup before upsizing an Access
database to SQL Server using the Microsoft Database Engine (MSDE), the predecessor to SQL
Server 2005 Express. But the situation in Office 2007 is very different. In addition to preparing
your Access database, you also have a considerable number of setup chores to do to make SQL
Working with SQL Server Data 18