Part VI: Access as an Enterprise Platform
1234
l (^) Tables: Individual tables are converted to SQL Server tables. Data types are converted to
their corresponding SQL Server data types.
Cross-Reference
Refer to the next section, “Comparing Access to SQL Server data types,” for a listing of SQL Server data types
and how they compare to native Access data types.
l (^) Queries: Queries are converted into views, stored procedures, and functions according to
the following rules:
l (^) Select queries that don’t have an ORDER BY clause or parameters are converted to
views.
l (^) Action queries are converted to stored-procedure action queries. Access adds SET
NOCOUNT ON after the parameter declaration code to make sure the stored procedure
runs.
l Select queries that use either parameters or an ORDER BY clause are converted to user-
defined functions. If necessary, the TOP 100 PERCENT clause is added to a query that
contains an ORDER BY clause.
l (^) Parameter queries that use named parameters maintain the original text name used in
the Access database and are converted either to stored procedures or inline user-
defined functions.
l Forms and reports: Converted with no changes. The RecordSource property of a form
or report still points to the same database object as before, except that in an .adp file, the
table is hosted by SQL Server.
l (^) Ribbons: Converted with no changes.
l Macros and modules: Converted with no changes.
To take full advantage of SQL Server and an Access project, you need to make some fairly signifi-
cant changes to your newly converted application. Although the Upsizing Wizard tries to make its
best guess as to the most efficient conversion approach, you should review the table and query
designs and revise them as necessary. Record sources and control sources for forms and reports are
converted without any changes. In an implementation with a large number of users, you don’t
want to bind forms and reports directly to a table or even a query.
Note
If you’re converting an application created in an earlier version of Access, you may also need to manually con-
vert code from Data Access Objects (DAO) to ActiveX Data Objects (ADO) in your modules.