Port the Access database to SQL Server

With the increasing demand for enterprise-level high-performance databases, users often need to switch from the file-Server environment of the Microsoft Access Jet Engine to the customer-Server environment of Microsoft SQL Server. In Microsoft Office 2000, Access 2000 Upsizing Wizard transfers 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 Upsizing Wizard to port your application to SQL Server.

If you are not willing to use Access 2000 and Upsizing Wizard for transplantation, this article can be used as a guide to Port Access 2000 to SQL Server. To transfer an Access application, you first need to transfer the data to SQL Server, and then transfer the query to the database, or transfer it to an SQL file for later execution. The last step is to port the application.

SQL Server tool used in database migration

SQL Server Manager)
The SQL Server Manager allows enterprise-level configuration and management of SQL Server and SQL Server objects. The SQL Server Manager provides a powerful scheduling engine with high fault tolerance and an embedded copy management interface. You can use the SQL Server Manager to implement the following functions:

Manage connections and user licenses
Create a script
Manage the backup of SQL Server objects
Backup Data and transaction processing logs
Manage tables, views, stored procedures, triggers, indexes, rules, default values, and user-defined data types
Create full text indexes, database charts, and database maintenance plans
Input and Output Data
Convert data
Execute various network management tasks

In a computer with Microsoft Windows NT as the operating system, SQL Server Manager is installed by SQL Server Setup and is default as a Server component, however, on machines running Windows NT and Microsoft Windows 95, it will be the customer component by default. You will start the Data transfer service (DTS, Data Transformation Services) from the graphical user interface of SQL Server Manager ).

Data transfer Services (DTS)
Data transfer service allows you to input and output data between multiple heterogeneous data sources, which adopt a database-based OLE architecture; alternatively, you can transfer databases and database objects between multiple computers using SQL Server 7.0. You can also use the data transfer service to conveniently transfer data between an online transaction processing system (OLTP) data warehouse and data center.

DTS Wizard allows you to create data transmission packages interactively and input, output, verify, and transfer data through ole db and ODBC. DTS Wizard also allows you to copy diagrams and data between relational databases.

SQL Server Query Analyzer)
The SQL Server Query analyzer is a graphical query tool that analyzes a query and executes multiple queries at the same time to view data and obtain index recommendations. The SQL Server Query analyzer provides the showplan option 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 activities 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. With SQL Server Profiler, you can re-execute the last captured operation. This tool helps application developers identify transactions that may reduce application performance. This feature is useful when porting a file system-based application to a client/server structure, this is because the last step involves optimizing applications in the new customer/server environment.

Transfer tables and data
To use DTS Wizard to transfer your Access data to SQL Server, take the following steps:

In the tool menu of SQL Server Manager (Enterprise Manager), point to "Data Transformation Services" and click "Import Data ."
In the dialog box of "Select Data Source" (Choose a Data Source), select Microsoft Access as the Data Source, enter your. mdb File Name (mdb is the file extension), or select a Browse File.
In the dialog box of "Select data target" (Choose a Destination), select "Microsoft ole db Provider for SQL Server", select the database Server, and click the Authentication mode.
In the Specify Table Copy or Query dialog box, click Copy tables ).
In the "Select data source" dialog box, click "Select All ).

Port Microsoft Access Query
You can transfer one of the following formats of Access query to SQL Server:

Transaction Processing SQL script program (Transact-SQL s cripts)
Transaction Processing 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. The SQL Server Query analyzer can help developers test transaction processing SQL statements, or run query processing, data operations (insert, modify, delete), and data definitions (create tables).

Stored Procedure)
Developers can transfer most of the transaction processing SQL statements that generate self-Access queries (search, insert, modify, and delete) to the stored procedure. A stored procedure written using a transaction to process SQL statements can be used to pack and standardize your data access, and the stored procedure is actually stored in the database. Stored Procedures can contain or do not contain parameters. They can be called by database programs 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 constitute the basic query. The view does not support the use of parameters. Views connected to multiple data tables cannot be modified using the INSERT, UPDATE, or DELETE statements. The view is called by SQL statements for transaction processing. It can also be used in SQL Server to query program segments running in analyzer. The SQL Server View and SQL-92 standards do not support ORDER BY sort clauses in the view. For more information about transaction processing SQL, stored procedures, and views, see SQL Server online reference books.

Selection and suggestions for migrating Access query-type SQL Server
A select statement can be stored in SQL files, stored procedures, or views for transaction processing. Establishing a stored procedure is the best way to separate the physical implementation of Database Application Development from database design. Stored procedures can be created in one place and called by applications.
If the Stored Procedure changes based on the database, and the stored procedure is carefully modified to reflect the changes, the call to the stored procedure will not be damaged.

Cross tabulation (CROSSTAB)
Cross tabulation is often used to summarize reports.

An Access cross-tabulation can be executed through SQL program segments, stored procedures, or transactions in the view to process SQL statements. When a query is sent, the data connection is re-executed to ensure that the latest data is used.

Based on the actual application, it is more appropriate to store the data in the cross tabulation as a temporary TABLE (refer to make table below). The temporary tables have less resource requirements, however, when creating a temporary table, only one snapshot of the data is provided ).

MAKE TABLE)
In Access, "make table" (create table) can be executed through the SQL script program for transaction processing or the create table statement for the SQL language for transaction processing in the stored procedure. 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)
The UPDATE statement can be stored in the transaction _ SQL script program. However, a better way to execute the UPDATE statement is to create a stored procedure.

APPEND (ADD)
The ALLEND statement can be stored in the transaction _ SQL script program. However, a better method to execute the APPEND statement is to create a stored procedure.

Export Microsoft Access to the stored procedure and view

Each Access query must be replaced with the following statements:
Create procedure <NAME_HERE>
<SELECT, UPDATE, DELETE, INSERT, create table statement from Microsoft Access>
GO

Create view <NAME_HERE>
<Place (SELECT only, with no parameters) Microsoft Access Query>
GO

Execute the following command for each Access query:

Open Access, and then open the SQL Server Query analyzer in SQL Server.
In the Access database window, click the "Queries" tab and then click the "Design" button.
Click SQL in the View menu.
Paste the entire Query into the SQL Server Query analyzer.
Test syntax, save SQL statements for transaction processing for future use, or execute these statements in the database. You can save the SQL statement for transaction processing to a script program.

Port Microsoft Access to query SQL statements for Transaction Processing

Most Access queries should be converted to stored procedures and views. However, some application developers can store SQL scripts for transaction processing, a text file with SQL as the file extension. These files can be run in the SQL Server Query analyzer.

If you want to convert some Access queries to SQL files, you can consider placing these transaction processing SQL statements in several script programs according to their usage methods. For example, you can classify transaction processing SQL statements that must run at the same frequency into the same script. Another script should contain all SQL statements for transaction processing that are only run under certain conditions. In addition, SQL statements that must be executed in a certain order should be classified into a discontinuous script.

Transfers the Access statement to the SQL file for Transaction Processing

Copy the statement to the SQL Server Query analyzer.
Use the blue multi-option icon to analyze the statement
Execute this statement as appropriate

The developer who wants to execute the make table query task in Access has several options in SQL Server. Developers can create one of the following objects:

One view
A view has a dynamic virtual table effect and provides the latest information. This is an input/output enhancement, because every time a query is made, it requires a connection to reproduce the data table.

A temporary table
A temporary table creates a snapshot for a 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. Add the prefix (# table_name) of a single number before the name of a local temporary table, and add the prefix of two digits before the name of the global temporary table (# table_name ). The query of temporary tables is very fast, because they usually only use one table when getting a result set, rather than dynamically joining multiple tables together.
For more information about temporary tables, see SQL Server online reference books.

Data transmission in SQL Server 7.0 allows you to create packages to standardize, automate, and schedule temporary tables. For example, when you port the Northwind Sample Database in Access 2.0, the cross table used for quarterly data reports can be converted into a view or a data conversion that can be created on a standard basis. For more information about DTS, see SQL Server online reference books.

Other design considerations
The following are some other considerations when you port your Access application to SQL Server:

Parameters Used
The stored procedure of SQL Server with parameters requires a syntax format different from Access query, for example:
Access 2.0 format:
Query name: Employee Sales By Country, in NWIND. mdb:

PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
SELECT Orders. [Order ID], [Last Name] & "," & [First Name] AS Salesperson, Employees. country, 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], Employees. Country, 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, Employees. Country,
Orders. [Shipped Date], [Order Subtotals]. Subtotal AS [Sale Amount]
FROM Employees inner join (Orders INNER J