As users' demand for enterprise-level high-performance databases grows, users often need to convert from the file-server environment of the Microsoft Access Jet engine to the client-server environment of the Microsoft SQL Server. Access 2000 Upsizing Wizard in Microsoft Office 2000 Upsizing Wizard can transfer data tables and queries to SQL Server 7.0. If you are using an earlier version of Access, you can first upgrade it to Access 2000 and then use the Upsizing Wizard in it to port your application to SQL Server.
If you are not very willing to use Access 2000 and Upsizing Wizard to implement porting, this article can be used as a guide to porting Access 2000 to SQL Server. Transferring an application on Access first requires transferring the data to SQL Server, then transferring the query into the database, or transferring it to SQL files for later execution. The last step to take is to port the application.
SQL Server tools used in database migration
SQL Server Manager (SQL Server Enterprise Manager)
SQL Server Manager allows enterprise-level configuration and management of SQL Server and objects in SQL Server. SQL Server Manager provides a powerful scheduled engine, high fault tolerance and an embedded replication management interface. Use SQL Server Manager to implement the following functions:
Manage connections and user licenses
Create script program
Manage backup of SQL Server objects
Backup data and transaction logs
Manage tables, views, stored procedures, triggers, indexes, rules, default values, and user-defined data types
Establish full text index, database charts and database maintenance plans
Input and output data
Convert data
Perform various network management tasks
In computers with Microsoft Windows NT as the operating system, SQL Server Manager is installed by SQL Server Setup and is defaulted to a server component. On machines running Windows NT and Microsoft Windows 95, it will be defaulted to a client component. You will start the data transfer service (DTS, Data Transformation Services) from the graphical user interface of SQL Server Manager.
Data Transformation Services (DTS)
The data transfer service allows you to input and output data between multiple heterogeneous data sources that adopt a database-based OLE architecture; or transfer databases and database objects between multiple computers using SQL Server 7.0; you can also use data transfer services to create data warehouses and data centers in an online transaction processing system (OLTP).
DTS Wizard allows you to create DTS packages interactively, input, output, verify and transfer data through OLE DB and ODBC. DTS Wizard also allows you to copy schemas and data between relational databases.
SQL Server Query Analyzer
SQL Server Query Analyzer is a graphical query tool through which you can analyze a query, execute multiple queries at the same time, view data and obtain index suggestions. The SQL Server Query Analyzer provides the showplan option, which can be used to display the data extraction method selected by the SQL Server Query Optimizer.
SQL Server Profiler
SQL Server Profiler can capture continuous records of database server activity in real time. SQL Server Profiler allows you to monitor events generated by SQL Server, filter events based on user-specified standards, or output operation steps to screens, files or data tables. Using SQL Server Profiler, you can re-execute the captured last operation. This tool can help application developers identify transactions that may degrade application performance. This feature is useful when porting a file architecture-based application into a client/server structure, as its final step includes optimizing applications for new client/server environments.
Transfer tables and data
Use DTS Wizard to transfer your Access data to SQL Server, and you can take the following steps:
In the tool menu of SQL Server Manager (Enterprise Manager), point to "Data Transformation Services" and then click "Import Data."
In the dialog window of "Choose a Data Source", select Microsoft Access as the data source, and then enter yours. mdb file name (mdb is the file extension) or select browse the file.
In the dialog window of "Choose a Destination", select "Microsoft OLE DB Provider for SQL Server", then select the database server, and then click the required authentication mode.
In the dialog window of "Specify Table Copy or Query", click "Copy tables".
In the "Select Data Source" dialog window, click "Select All" (Select All).
Migrate Microsoft Access Query
You can transfer Access's query to SQL Server in one of the following formats:
Transaction processing SQL script program (Transact-SQL s scripts)
Transaction SQL statements are usually called by database programs, but you can also run them directly using the SQL Server query analyzer included in SQL Server 7.0. SQL Server Query Analyzer can help developers test transaction processing SQL statements, or run transaction processing SQL statements that perform query processing, data operations (insert, modify, delete) and data definitions (create tables).
Stored procedures (Stored procedures)
Developers can transfer most transactional SQL statements generated from Access queries (find, insert, modify, delete) to stored procedures. Stored procedures written in transactional SQL statements can be used to package and standardize your data access, and stored procedures are actually stored in the database. Stored procedures can be either with parameters or without parameters, and can be called by the database program or manually executed by the SQL Server query analyzer.
Views
A view is a virtual table that displays specific rows and columns from one or more tables. They allow users to establish queries without directly executing complex connections that form the basis of the query. The view does not support the use of parameters. Views connected to multiple data tables cannot be modified using INSERT, UPDATE or DELETE statements. The view is called by transactional SQL statements and can also be used for program blocks running in SQL Server query analyzer. SQL Server views and SQL-92 standards do not support the ORDER BY sort clause in the view. For additional information about transactional SQL, stored procedures and views, please refer to the SQL Server Online Reference Book.
SQL Server port selection and suggestions for Access query type
A SELECT statement can be stored in a transactional SQL file, stored procedure, or view. Establishing stored procedures is the best way to separate database application development from physical implementation of database design. Stored procedures can be created in one place and called by the application.
If the database on which the stored procedure is based changes and the stored procedure is carefully modified to reflect these changes, the call to the stored procedure will not be destroyed.
Crosstab (CROSSTAB)
Crosstabs are often used to summarize reports.
An Access crosstab can be executed by transaction processing SQL statements in SQL program blocks, stored procedures, or views. Whenever a query is issued, the data connection is reproduced to ensure that the most recent data is used.
According to actual application, a more appropriate method is to store the data in the crosstab as a temporary table (see MAKE TABLE below). The temporary table has relatively few resources, but the temporary table only provides a snapshot of the data while establishing it.
Create table (MAKE TABLE)
The "MAKE TABLE" in Access can be executed through the table creation statement "CREATE TABLE" of the transaction processing SQL language in the transaction processing SQL script program or stored procedures. The syntax is as follows:
SELECT [ ALL | DISTINCT ]
[ {TOP integer | TOP integer PERCENT} [ WITH TIES] ]
<select_list>
[ INTO new_table ]
[ FROM {<table_source>} [,…n] ]
[ WHERE <search_condition> ]
[ GROUP BY [ALL] group_by_expression [,…n]
[ WITH { CUBE | ROLLUP } ]
CREATE TABLE mytable (low int, high int)
UPDATE (modify)
UPDATE statements can be stored in a transaction_SQL script program, however, the better way to execute UPDATE statements is to create a stored procedure.
APPEND (Add)
The ALLEND statement can be stored in a transaction_SQL script program, but the better way to execute the APPEND statement is to create a stored procedure.
Migrate Microsoft Access's query to stored procedures and views
Each Access query must be replaced by a series of statements:
CREATE PROCEDURE <NAME_HERE> AS
< SELECT, UPDATE, DELETE, INSERT, CREATE TABLE statement from Microsoft Access >
GO
CREATE VIEW <NAME_HERE> AS
<Place (SELECT only, with no parameters) Microsoft Access Query>
GO
For each Access query, it should be executed:
Open Access, and then in SQL Server, open the SQL Server query analyzer.
Click the "Queries" tab key in the database window in Access, and then click the "Design" button.
Click the SQL button on the View menu.
Paste the entire query into the SQL Server query analyzer.
Test the syntax, save transactional SQL statements for later use, or execute these statements in the database. You can choose to save the transactional SQL statement into a scripting program.
Migrate Microsoft Access query to transaction processing SQL statements
Most Access queries should be converted into stored procedures and views. However, there are some statements that are less commonly used by application developers that can be stored as transactional SQL scripts, a text file with SQL as the file extension. These files can be run in the SQL Server query analyzer.
If you plan to convert some Access queries into SQL files, consider differently placing these transactional SQL statements in several scripting programs depending on the way they are used. For example, you can classify transactional SQL statements that must run at the same frequency into the same script. Another script should contain all transactional SQL statements that run only under certain conditions. In addition, transaction processing SQL statements that must be executed in a certain order should be classified into a discontinuous script.
Transfer Access statements to transaction SQL file
Copy the statement to SQL Server query analyzer
Use blue multi-option icon to analyze statements
Execute this statement at the appropriate time
Developers who want to perform query tasks for creating tables in Access (MAKE TABLE) have several options in SQL Server. Developers can create one of the following objects:
A view
Views have the effect of dynamic virtual tables that provide recent information. This is an input/output booster because whenever a query is issued it requires a connection to the data table reproduction.
A temporary table
The temporary table creates a snapshot of the connected user session. You can create local or global temporary tables. Local temporary tables are only visible in the current session, while global temporary tables are visible in all sessions. The name of the local temporary table is prefixed by a single number ((#table_name)), and the name of the global temporary table is prefixed by a two-digit number (##table_name). Queries on temporary tables are very fast to execute because they usually use only one table when they get a result set, rather than dynamically joining multiple tables together.
For additional information about temporary tables, please refer to the SQL Server online reference book.
Data Transformation Service (DTS) in SQL Server 7.0 allows you to standardize, automate and schedule temporary table establishment by creating packages. For example, when you port the Northwind sample database in Access 2.0, the crosstab used for quarterly data reports can be transformed into a view or a data transformation that can establish temporary tables on a specification basis. For additional information about DTS, please refer to the SQL Server Online Reference Book.
Other design considerations
Here are some other issues you must consider when porting your Access application to SQL Server:
Use parameters
SQL Server stored procedures with parameters require a syntax format different from Access queries, such as:
Access 2.0 format:
Query name: Employee Sales By Country, in:
PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
SELECT Orders.[Order ID], [Last Name] & ", " & [First Name] AS Salesperson, , Orders.[Shipped Date], [Order Subtotals].Subtotal AS [Sale Amount]
FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.[Order ID] = [Order Subtotals].[Order ID]) ON Employees. = Orders.
WHERE (((Orders.[Shipped Date]) Between [Beginning Date] And [Ending Date]))
ORDER BY [Last Name] & ", " & [First Name], , Orders.[Shipped Date];
SQL Server 7.0 format:
CREATE PROCEDURE EMP_SALES_BY_COUNTRY
@BeginningDate datetime,
@EndingDate datetime
AS
SELECT Orders.[Order ID], [Last Name] + ", " + [First Name] AS Salesperson, ,
Orders.[Shipped Date], [Order Subtotals].Subtotal AS [Sale Amount]
FROM Employees INNER JOIN (Orders INNER J