Chapter 5 - Migrating Access 2000 Databases to SQL Server 2000
As your business grows and demands more high-level performance from your database system, you may have to move from the file-server environment of the Microsoft® Access 2000 Jet engine to the client/server environment of Microsoft SQL Server™ 2000. You may be considering migrating (upsizing) your database to SQL Server because:
The number of database users is increasing.
As the number of users increases, the file-sharing mechanism of a Microsoft Access database may result in a slower performance and increased network traffic. The client/server environment of Microsoft SQL Server is designed for a large number of users and minimizing network traffic.
The size of your database is growing.
Microsoft SQL Server 2000 can support databases of up to 2 terabytes in size.
You must access your database over the Internet.
SQL Server 2000 is an XML-enabled database server that allows you to access the server through a URL, and to write and retrieve data as XML documents.
You require increased security.
Integration of Microsoft SQL Server's security with the security of a Microsoft Windows NT® or Microsoft Windows® 2000 network allows easier administration of complex network security settings.
You want higher availability.
SQL Server can be backed up and restored dynamically while the database is in use. Users do not have to close the database while the daily backup is performed. In addition, the process of transaction logging makes it possible to restore the database up to the point of failure.
Migration Options
.gif "Cc917622.spacer(en-us,TechNet.10).gif")
The process of migrating an Access database to SQL Server 2000 moves some or all data and data definitions to the server. After the data is safely transferred to SQL Server, you still must decide how to interact with the server-based data. The following options are possible solutions:
Create a two-tier application.
You can link server-based tables to local tables in Access and use the other existing database objects (queries, forms, reports, modules, and macros) as before. This creates a two-tier application, in which the server running SQL Server stores data, and copies of the interface are stored in Access on the users' computers. Queries are run on the client tier. Locally stored forms, reports, modules, and macros provide the remaining elements of the user interface. The design of server-based tables cannot be modified from Access. The advantage of this method is a low transition cost because no modifications are needed to the front-end database objects. Users continue to interact with a familiar interface, and the SQL Server provides centralized data storage, backup and recovery options, a multiuser environment, an integrated security model, and other advantages. The main disadvantages of this method are that queries are still processed locally, and a large amount of network traffic is generated.
Create a client/server application.
Microsoft Access 2000 introduces Microsoft Access Project, a new type of Access file (.adp) designed as a client/server application. Communication between the client and the server takes place through OLE DB component architecture, which was specifically designed for efficient client/server communication. All tables and data definition objects such as views, stored procedures, and database diagrams are stored on the server. The client stores only code-based or HTML-based objects such as forms, reports, modules, and macros. Most data processing takes place on the server, and only filtered data is sent to the client, thus minimizing network traffic.
Upsize data without making any modifications to the Access application.
If you are going to create a new application in Microsoft Visual Basic®, Active Server Pages (ASPs), or another environment, you may not want to migrate the application-specific logic and user interface.
Before You Migrate
Several issues should be considered before you begin migrating your database:
Confirm the integrity of the original database. The best way to protect your database is to create a complete backup and safely store it away from the computers involved in migrating. Consider this step as the single most critical step of the entire operation.
Ensure that you have adequate permissions on both the Access database and the instance of SQL Server to which you are migrating. You will need at least Read Design permission on all objects that will be migrated and on all objects that act as data source for migrated objects. For example, to properly migrate a query you must have Read Design permissions on the query itself as well as the table or tables that the query uses as the data source.
If you are upsizing an MDE file, make sure that you have access to a copy of the original Access database. In an MDE file all source code for Microsoft Visual Basic for Applications is removed and replaced with a compiled, binary format. This creates a more efficient database but also prevents access to the design of forms, reports, and modules, making migration of these objects impossible. Tables, queries, macros, and data access pages are not affected and can be migrated.
You will need a password to any Visual Basic for Applications projects within your database.
Migration Tools
You have two main methods to upsize your database:
The Upsizing Wizard available with Microsoft Access
SQL Server Tools such as Data Transformation Services (DTS), SQL Server Enterprise Manager, SQL Query Analyzer, and SQL Profiler
The actual process of moving a database to SQL Server will most likely be a combination of these methods. The Upsizing Wizard can be used to quickly move your database, and the SQL Server Tools can be used in the process of fine-tuning your queries and indexes.
Upsizing Wizard
The Upsizing Wizard moves a Microsoft Access database to a new or existing Microsoft SQL Server database or to a new Microsoft Access Project. The wizard moves data and data definitions to the server and migrates database objects. The wizard re-creates primary keys, table relationships, and any existing rules and defaults.
To use the Upsizing Wizard, you must run the Access 2000 and SQL Server 2000 Readiness Update. This update is available on the SQL Server 2000 Resource Kit CD-ROM in \ToolsAndSamples\AccessUpsizing.
Before you use the Upsizing Wizard:
Create the database and log devices on SQL Server.
Gather data on table relationships with the Access database.
Ensure that the names in the .mdb file comply with SQL Server rules.
Make note of all default values in the database. You will want to confirm the defaults were set correctly after you migrate.
Make a backup of your database.
When you use the Upsizing Wizard:
Use Declarative Referential Integrity (DRI), not triggers, to enforce table relationships.
Unless you want timestamps, do not allow the wizard to decide where to put them. You can always add these exactly where you want them later.
After running the wizard, check the log to ensure that the migration went smoothly.
In SQL Server, update foreign keys to support cascading deletes and updates where desired.
SQL Server Tools Used in Migrations
SQL Server 2000 provides several tools that you can use to migrate your Access data and applications. Using these tools provides more control over the migrating process, but may be time-consuming because they require more manual input.
SQL Server Enterprise Manager
Using SQL Server Enterprise Manager, you can configure and manage SQL Server and SQL Server objects throughout your enterprise. SQL Server Enterprise Manager provides access to a powerful scheduling engine, administrative alert capabilities, and a built-in replication management interface. You can also use SQL Server Enterprise Manager to:
Manage logins and user permissions.
Create scripts.
Manage backup of SQL Server objects.
Back up databases and transaction logs.
Manage tables, views, stored procedures, triggers, indexes, rules, defaults, and user-defined data types.
Create full-text indexes, database diagrams, and database maintenance plans.
Import and export data.
Transform data.
Perform various Web administration tasks.
Data Transformation Services (DTS)
DTS enables importing and exporting data between SQL Server and any OLE DB or ODBC data source, including Microsoft Access, and to transfer databases and database objects (for example, indexes and stored procedures) between multiple computers running SQL Server 2000. Both SQL Server and Microsoft Data Engine (MSDE) include the DTS Import/Export Wizard, which enable creating and running DTS packages interactively.
You can use the DTS Import/Export Wizard to automatically create tables on SQL Server, and then to copy data from Access to the new SQL Server or MSDE tables. DTS can move data at a faster rate than the Microsoft Access Upsizing Wizard, but DTS does not provide all of the features of the Access Upsizing Wizard. The following is a list of actions that the Access Upsizing Wizard can perform, but which the DTS Import/Export Wizard cannot perform:
The Access Upsizing Wizard automatically migrates all rules and defaults that exist in a table to SQL Server; DTS does not.
DTS does not upsize any Access queries.
SQL Query Analyzer
SQL Query Analyzer is a graphical query tool that allows visually analyzing the plan of a query, executing multiple queries simultaneously, viewing data, and obtaining index recommendations. SQL Query Analyzer shows graphical query plan information, which is used to report data retrieval and sorting methods chosen by the SQL Server query optimizer. SQL Query Analyzer can be used to test Transact-SQL statements against development databases or to run Transact-SQL statements that perform queries, data manipulation (INSERT, UPDATE, or DELETE), or data definition (CREATE TABLE).
SQL Profiler
SQL Profiler captures a continuous record of server activity in real time. SQL Profiler allows monitoring events produced through SQL Server, filtering events based on user-specified criteria, and directing the trace output to the screen, a file, or a table. Using SQL Profiler, you can replay previously captured traces. Application developers can use this tool to identify transactions that might be deteriorating the performance of an application.
Optimizing performance in the client/server environment is the last step in migrating an Access database to SQL Server. At this stage, you are likely to use the output generated by SQL Profiler and the Index Tuning Wizard to optimize queries and indexes.
Moving Data
Before you use DTS Import/Export Wizard, you must decide into which database to import the Access database. You can use either an existing database or create a new database on the server. For more information about creating a new database, see SQL Server Books Online.
To transfer Access tables to SQL Server
Open SQL Server Enterprise Manager, expand the server, and click the Databases folder.
On the Tools menu, point to Data Transformation Services, and then click Import Data.
In the Choose a Data Source dialog box, select Microsoft Access as the Data Source, and then type the path and file name of your Access database (.mdb), or use the browser to browse for the file.
In the Choose a Destination dialog box, select Microsoft OLE DB Provider for SQL Server, and then select the database server and the required authentication mode. In the Database dialog box, select the target database on the server.
In the Specify Table Copy or Query dialog box, click Copy tables and views from the source database.
In the Select Source Tables and Views dialog box, select the tables and queries to import.
In the Save, Schedule, and Replicate Package dialog box, select Run Immediately.
Click Finish.
Migrating Access Queries
Microsoft Access queries must be converted into one of the following SQL Server formats:
Stored procedures
Stored procedures are optimized and precompiled Transact-SQL statements that are stored as one unit. Stored procedures can be called from other programs, call other stored procedures themselves, and accept input variables. Stored procedures are best suited for queries that undertake some sort of action, such as inserting or updating data.
Views
Views are used as virtual tables that expose specific rows and columns from one or more tables. Views enable you to create queries without directly implementing the complex joins that underlie the query. Views do not support the use of parameters. Views that join more than one table cannot be modified using INSERT, UPDATE, or DELETE statements. Views are called from Transact-SQL statements and can also be used in scripts that are run in SQL Query Analyzer. SQL Server views and the SQL-92 standard do not support ORDER BY clauses in views.
User-defined functions
User-defined functions are subroutines of encapsulated Transact-SQL logic that can be called from other bits of Transact-SQL code. Thus, they provide an excellent way to store logic that will be accessed repeatedly. Earlier versions of SQL Server include built-in functions such as GETDATE(),REPLACE() and POWER(). User-defined functions allow for this same easy functionality, but with your own logic.
User-defined functions can return either a scalar value or a table. The scalar data types allowed in user-defined functions include any scalar data type in SQL Server 2000, except table, text, ntext, image, cursor, and timestamp. User-defined functions that return tables can consist of one or multiple Transact-SQL statements and can be accessed directly in the FROM clause of a Transact-SQL statement. However, the dataset in the returned table cannot be modified.
Indexed views
With an indexed view, the result set of a query is stored and indexed on disk. Furthermore, the indexed view is dynamically updated when any of the source data changes. Therefore, the overhead saved by storing this data on disk can be lost if the underlying data is changed frequently.
Transact-SQL scripts
Transact-SQL scripts are text files that contain Transact-SQL statements. Transact-SQL scripts can be executed directly from SQL Query Analyzer or be used as input in the isql and osql utilities. You can also use them as permanent copies of Transact-SQL syntax kept separate from any database.
There are several benefits to using stored procedures, views, or user-defined functions instead of Transact-SQL scripts:
All three run faster than SQL scripts because they are optimized at the time when they are created. After a stored procedure is executed, a copy of it remains in the memory and executes even faster when it is called again.
Stored procedures and views can be used to implement security mechanisms. Users can be granted permission to execute a procedure that returns data in a restricted and standardized manner.
They add modular structure to your programs. The same stored procedure, view, or user-defined function can be called from various parts of the program and modified independently from the program code.
Most Transact-SQL statements that originate from Access queries (select, insert, update, and delete) can be moved into a stored procedures view or user-defined function.
For more information about Transact-SQL scripts, stored procedures, and views, see SQL Server Books Online.
Limitations in Upsizing Queries
The following queries cannot be upsized by the upsizing tools and must be re-created manually:
Action queries containing nested queries
Action queries with parameters
Crosstab queries
SQL Data Definition Language (DDL) queries
SQL pass-through queries
Queries that reference values on a form
Union queries
For more information about running upsized make-table and append queries, search for the Microsoft Knowledge Base article "229681-ACC2000: Cannot Use Upsized Append and Make-Table Queries in an Access Project" at https://support.microsoft.com/.
For more information about parameters and setting table properties, see SQL Server Books Online.
The following table provides a summary of migration options for several of the Access query types.
Access query type |
SQL Server migration options and comments |
|---|---|
Select |
Select statements are best suited to SQL Server views. Views are used to expose certain subsets or supersets of data to users without necessarily giving them access to the underlying tables. |
Crosstab |
Crosstabs are often used for summary reports. |
Make-table |
An Access make-table query can be implemented as a Transact-SQL CREATE TABLE statement in a Transact-SQL script or stored procedure. The syntax is: |
Update |
An update query can be stored in a Transact-SQL script; however, the recommended way to implement an update statement is to create a stored procedure. |
Append |
An append query can be stored in a Transact-SQL script; however, the recommended way to implement an append query is to create a stored procedure. |
Delete |
A delete query can be stored in a Transact-SQL script; however, the recommended way to implement a delete query is to create a stored procedure. |
Pass-through |
If you have the appropriate OLE DB provider, you can use a linked server configuration in SQL Server to allow for the executing of queries directly against a remote data source. |
Migrating Access Queries into User-Defined Functions
User-defined functions can accept parameters and return either a scalar value or a table. Unlike stored procedures, their result sets can be accessed directly from another query. For example, the following user-defined function returns all the employees from the employees table for a given state in the Northwind sample database in SQL Server:
CREATE FUNCTION dbo.udfEmpByCity(@City varchar(15)) RETURNS TABLE AS RETURN ( SELECT EmployeeID, LastName, FirstName FROM dbo.Employees WHERE (City = @City) )
To access the result set of this function, simply use:
SELECT * FROM udfEmpByCity('Seattle')
For more information about user-defined functions, see SQL Server Books Online.
Migrating Access Queries into Stored Procedures and Views
Each Access query must be placed into one of the following statements:
To create a stored procedure, use:
CREATE PROCEDURE <NAME_HERE> AS
< SELECT, UPDATE, DELETE, INSERT, CREATE TABLE statement from Microsoft Access > GO
To create a view, use:
CREATE VIEW <NAME_HERE> AS
<Place (SELECT only, with no parameters) Microsoft Access Query> GO
To migrate each Access query
Start Microsoft Access and open the database to export.
In the Database window, click the Queries tab, select the query to export, and then click Design.
On the View menu, click SQL. Select and copy the entire SQL query.
In SQL Server Enterprise Manager, on the Tools menu, click SQL Query Analyzer.
Paste the entire query into SQL Query Analyzer.
On the Query menu, click Parse. This verifies that the SQL syntax is compliant with Transact-SQL.
On the Query menu, click Run. This creates a new view or procedure. To confirm that a new object was created, right-click Views or Stored Procedures, and select Refresh.
Converting Make-Table and Crosstab Queries
The Access make-table and crosstab queries can be converted into views, temporary tables, or indexed views.
View
This creates virtual, temporary tables with current data. Views are dynamic structures that allow the user to change the underlying tables. A view does not save any I/O time because it requires rejoining the data tables each time the query is executed.
Temporary table
A temporary table creates a static snapshot of data for a connected user's session. You can create local and global temporary tables. Local temporary tables are visible only in the current session. Global temporary tables are visible to all sessions. Prefix local temporary table names with one number sign (#table_name), and prefix global temporary table names with two number signs (##table_name). Queries run quickly against temporary tables because they generally use only one table rather than dynamically joining together several tables to obtain a result set. Using Data Transformation Services (DTS) in SQL Server 2000, you can standardize, automate, and schedule the creation of temporary tables by creating packages. Temporary tables are not dynamically updated. Therefore, if the underlying tables are updated without rebuilding the temporary table, the data in the temporary table can be inaccurate.
Indexed view
Like a temporary table, an indexed view stores its result set on disk, and like a view, an indexed view dynamically reflects changes that occur against the underlying tables. Thus, with indexed views, you can avoid the overhead of a regular view and the limited availability of a temporary table. The benefits of using an indexed view can be negated, however, if the data in the base tables is updated frequently.
For more information about views, temporary tables, indexed views, and DTS, see SQL Server Books Online.
Migrating Access Queries into Transact-SQL Scripts
Most Access queries should be translated into stored procedures, views, and user-defined functions. Nevertheless, some statements that are run infrequently by an application developer can be stored as Transact-SQL scripts.
If you plan to transfer some of your Access queries to .sql files, consider grouping the Transact-SQL statements into logical scripts. Distinct scripts can be created for statements that are run with the same frequency, that are run only under certain conditions, or that must be run in a specific order.
To move a Jet-SQL statement from Access to a Transact-SQL file
Start Microsoft Access and open the database to export.
In the Database window, click the Queries tab, select the query to export, and then click Design.
On the View menu, click SQL. Select and copy the entire SQL query
In SQL Server Enterprise Manager, on the Tools menu, click SQL Query Analyzer.
Paste the entire query into SQL Query Analyzer.
On the Query menu, click Parse (or press CTRL+F5).
Execute the statement if appropriate.
Save the Transact-SQL syntax as an .sql file
Additional Design Considerations for Queries
Often there is more than one way to migrate the logic within an Access query to SQL Server. The following section provides some additional considerations for migrating that logic.
Parameter Queries
Some Access queries require the user to enter parameter values. In SQL Server, parameters cannot be part of a view definition, but can be used in stored procedures and user-defined functions. In the following example, the Access query queries the Northwind sample database for the total number of orders by each company from a country specified by the user. This nested query uses the ORDER BY clause to sort the results by the CompanyName column.
SELECT [CompanyName], [City], Region, (SELECT Count(*) FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) AS Number_of_Orders FROM Customers WHERE [Customers].[Country]=[Enter Country] ORDER BY Customers.CompanyName;
In SQL Server, the query can become either a view and a stored procedure or a single user-defined function. The result set of these solutions cannot be updated. The view has a derived column in it and the result sets of user-defined functions are not updatable.
View and stored procedure |
User-defined function |
|---|---|
DROP VIEW "No_of_Orders" |
CREATE FUNCTION udfCompOrders(@CountryName varchar(30)) |
In the first column of the preceding table, the stored procedure queries the view, filters the results according to the CountryName parameter, and then sorts the output according to the ORDER BY clause. All of this logic can be encapsulated into one user-defined function, including the parameter and the order by clause, as shown in the second column.
When making your decision, you should determine whether there are individual units of logic in the query that might be needed by other queries. If this is the case, you might consider breaking your query up into multiple views, stored procedures, and user-defined functions so that your logic can be reused.
Nested Queries
Nested queries are one of more queries contained within another query. The nested query often acts as a data source for the nesting query. Nested queries can be migrated to SQL Server as user-defined functions or nested views. In some queries the nested query contains parameters, and the nesting one does not. In this case, you must use a user-defined function because views cannot take parameters.
Make-Table Queries
In earlier versions of SQL Server, select into and bulk copy operations were not logged, leading to an inconsistent transaction log. SQL Server 2000 can be configured to log these operations to maintain point-in-time recovery by setting the recovery model of the database to FULL. For more information about setting SQL Server recovery models, see the SQL Server Books Online.
Append Queries
The upsizing tools convert the AutoNumber field to an integer column that uses the IDENTITY property in the upsized table. The combination of an integer column with the IDENTITY property can act the same way as the AutoNumber data type in Access. For an insert statement to explicitly name the IDENTITY column, the IDENTITY_INSERT property must be set to ON. By default, a new table will have the property set to OFF, and a newly upsized append query will fail.
To set the IDENTITY_INSERT property, use this Transact SQL statement:
SET IDENTITY_INSERT {TableName} {ON|OFF}
Note Only one table per session can have the IDENTITY_INSERT property set to ON at any given time.
Verifying SQL Server–Compliant Syntax
In SQL Query Analyzer, you can use the Parse command on the Query menu to verify whether a view or stored procedure functions in SQL Server. As shown in the following illustration, the ORDER BY clause is used in a view. Views do not support the ORDER BY clause without the TOP clause to limit the number of rows returned in the result set. The lower pane of the SQL Query Analyzer lists all detected errors, their locations, and a short message to assist in correcting the syntax.
.gif "Cc917622.acesscrn(en-us,TechNet.10).gif")
Access and SQL Server Syntax
The following table compares Access and SQL Server syntax.
Access syntax |
SQL Server syntax |
|---|---|
String concatenation with "&" |
String concatenation with "+" |
Supported clauses/operators |
DISTINCTROW |
If the query is a multi-table join, then DISTINCT. If the query uses DISTINCTROW against a single table, it will have to be recoded. |
FROM |
FROM |
GROUP BY |
GROUP BY |
HAVING |
HAVING |
INTO |
INTO |
Not supported |
COMPUTE |
ORDER BY |
Not supported in views |
SELECT |
SELECT |
SELECT TOP N [PERCENT] |
SELECT TOP N [PERCENT] |
UNION (ALL) |
UNION (ALL) |
WHERE |
WHERE |
WITH OWNER ACCESS |
Not supported |
Aggregate functions |
Not supported |
GROUPING (column_name) |
AVG |
AVG ([ALL | DISTINCT] expression) |
COUNT(*) |
COUNT(*) |
COUNT(column) |
COUNT ([ALL | DISTINCT] expression) |
FIRST, LAST |
Not supported |
MAKE TABLE, ALTER TABLE |
CREATE TABLE, ALTER TABLE |
MAX |
MAX(expression) |
MIN |
MIN(expression) |
PIVOT |
Not supported |
STDEV, STDEVP |
STDEV, STDEVP |
SUM |
SUM ([ALL | DISTINCT] expression) |
TRANSFORM |
WITH ROLLUP, WITH CUBE on SELECT statements |
VAR, VARP |
VAR, VARP |
Other supported clauses |
ADD COLUMN |
ADD COLUMN |
CONSTRAINT |
CONSTRAINT |
DROP COLUMN |
DROP COLUMN |
Not supported |
GRANT, LOCK |
DROP INDEX |
DROP INDEX |
String concatenation with "&" |
String concatenation with "+" |
Visual Basic Functions
The following tables show the Visual Basic conversion and date functions used in Access and the corresponding Transact-SQL functions used in SQL Server.
Visual Basic conversion function |
Transact-SQL function |
|---|---|
Cint(x) |
CONVERT(smallint,x) |
CLng(x) |
CONVERT (int,x) |
CSng(x) |
CONVERT (real,x) |
CDbl(x) |
CONVERT (float,x) |
CStr(x) |
CONVERT (varchar,x) |
Ccur(x) |
CONVERT (money,x) |
CVdate(x) |
CONVERT (datetime,x) |
Visual Basic date function |
Transact-SQL function |
|---|---|
DATEADD() |
DATEADD |
DATEDIFF() |
DATEDIFF |
FORMAT() |
DATENAME |
DATEPART() |
DATEPART |
NOW(), DATE() |
GETDATE |
Not all Visual Basic for Applications functions are supported by Transact-SQL equivalents. The following table shows functions that must be converted to a different syntax in Transact-SQL.
Visual Basic function |
Transact-SQL function |
|---|---|
ASC() |
ASCII() |
INSTR() |
CHARINDEX |
STRING() |
REPLICATE |
STRREVERSE() |
REVERSE |
STR() |
STR |
Access and SQL Server Data Types
Data types in Microsoft Access and Microsoft SQL Server are not identical. When you convert from Access to SQL Server data types, use the following table as a guideline.
Access data type |
SQL Server data type |
|---|---|
Autonumber (Long Integer) |
int (Identity) |
Binary |
varbinary |
Byte |
Smallint |
Currency |
Money |
Date/time |
Datetime |
Double |
Float |
Hyperlink |
ntext (link functionality lost)* |
Integer |
Smallint |
Long Integer |
Int |
Memo |
Ntext |
Memo |
Text |
Number |
Decimal |
Number |
Float |
Number |
Int |
Number |
Decimal |
Number |
Real |
Number |
Smallint |
Number |
Tinyint |
Number |
uniqueidentifier |
OLE Object |
Image |
Replication ID |
varbinary |
Single |
Real |
Text |
Nvarchar |
Text |
Varchar |
Yes/No |
Bit |
* Access hyperlinks can store additional information such as Screen Tips, Sub Address, and Display Text. This information is not visible in Access, but will become visible once converted to SQL Server.
For more information about converting data types between Access and SQL Server, search for the Microsoft Knowledge Base article "224529 ACC2000: Data Types Change Importing from ADP to MDB and Back" at https://support.microsoft.com/.
Migrating Your Applications
The process of migrating your Access applications to SQL Server 2000 is a three-step procedure:
Create a working version of the application for SQL Server.
Optimize the application for the client/server environment.
Optimize SQL Server-based data structure.
Creating a Client/Server Application
Access provides the user-interface functionality that SQL Server does not. Often, when an Access application is migrated, the user interface is migrated as well to Visual Basic, Active Server Pages, or another environment. Here are some things to think about when moving the front-end of an Access application.
Converting Code
Regardless of which method of data migration you use, your application code remains unaffected. Currently, there are two data access object models you are likely to encounter:
For versions of Access prior to Access 2000, Data Access Objects (DAO) is the most common object model. DAO version 3.6 is currently available in Access 2000 and can still be used with Jet-SQL 4.0. DAO cannot expose a number of features of SQL Server; therefore, DAO-based code must be modified for the application to work in the client/server environment.
DAO code that works with strictly Access objects, such as forms or reports, continues to work in a front end application. However, any code that works with server-based objects (for example, tables and queries) must be manually converted to ADO.
With the release of Access 2000, Microsoft ActiveX® Data Objects (ADO) is the new object model. ADO is a high-speed, low-memory/disk footprint data access object model that is optimized for Internet and intranet access, and very well suited for the client/server environment. ADO manipulates SQL Server data using the Microsoft OLE DB Provider for SQL Server.
For more information about ADO and DAO, see SQL Server Books Online, or see https://msdn2.microsoft.com/data/default.aspx.
Forms
Forms and Reports in Access derive their data from queries or SELECT statements bound to the RecordSource property. In the client/server environment, the RecordSource property is based on a server-based view or on a stored procedure that is expected to return the same data as the original Access query. When you convert the record source, the same rules apply as for queries (see "Migrating Access Queries" earlier in this chapter).
If your form or report contains bound controls, you may receive errors when you view them after upsizing. Most likely the error is caused by one of the following conditions:
The query or a SELECT statement does not refer to field names using their table names, for example, [TableName].[FieldName]. Instead, the query or statement uses only [FieldName], which works perfectly well in Access, but causes errors in SQL Server.
The control refers to a parameter query.
The WHERE statement in a query or a SELECT statement refers to a form or a report-based control.
Optimizing the Application for the Client/Server Environment
The next step in migrating your database to SQL Server is to optimize the application for the client/server environment. Here are some considerations and guidelines:
If you linked local tables to SQL Server-based data, use pass-through queries wherever possible. Pass-through queries are not processed locally but, instead, are passed unmodified to the server. For example, instead of running multiple code loops to local tables, you can run a single pass-through query with the appropriate Transact-SQL syntax.
Use as few queries as possible. If your forms contain subforms, they will send at least two queries to the server. If you base your form on a single query, you can modify that number to one.
If a form is used only to display data (not to enter or update information), change the RecordsetType property to Snapshot. This will retrieve a faster, static object.
Consider using local tables as record sources for combo boxes and list boxes where information never changes. For example, the list of countries, U.S. states, planets, or poisonous snakes in Arizona can be kept locally. In Access project you cannot create local tables, but you can store the information in a text file. For more information, see the Microsoft Access documentation.
Request less data. If most users use only a small set of fields on a form, you can make only those most frequently accessed fields available when the form opens. Any additional fields can be triggered with another query fired with a command button.
Monitor the growth of your database. Solutions that work with smaller databases may become awkward and inefficient as the amount of data grows. A user interface that retrieves 50 records can be still useful, but one that retrieves 5000 records requires another approach.
Optimizing Data Structure
The last step in the migrating process is to optimize the data structure. SQL Server provides the graphical tools that can help you in optimize your database:
SQL Profiler
Use this to record a number of statistics about the operation of the entire database, such as warnings, errors, objects created and dropped, stored procedure operation, Transact SQL and RPC statistics, I/O and CPU stats, and more. This tool records server activity in a trace (.trc) file that can be used by other programs to optimize your database.
SQL Query Analyzer
Use this to create and test Transact-SQL statements, manage indexes, and display details of query execution plans.
Index Tuning Wizard
Using the information in the trace file and the structure of the database, the Index Tuning Wizard makes suggestions and implements the most efficient index structure for your database and its average workload.
For more information about optimizing indexes, the SQL Profiler, SQL Query Analyzer, and the Index Tuning Wizard, see SQL Server Books Online.