Porting an Access database to SQL server[pick]

Source: Internet
Author: User
Tags date implement insert sql server query ole one table window crosstab
access|server| Data | Databases as users grow in demand for enterprise-class high-performance databases, users often have to switch from a file-server environment in a Microsoft Access jet engine to a client-server environment in Microsoft SQL Server. Access Upsizing Wizard in Microsoft Office 2000 enables you to 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 migrate your application to SQL Server.


This article can be a guide to porting Access 2000 to SQL Server if you are not too willing to implement porting with Access 2000 and Upsizing Wizard. Transferring an application on access first requires transferring the data to SQL Server, then transferring the query into the database, or transferring it to a SQL file for later execution. The last step to take is porting the application.

SQL Server tools used in database porting

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 scheduling engine, a high degree of fault tolerance, and an embedded replication management interface. Using SQL Server Manager, you can implement the following features:

Managing connections and User licenses
Create a script program
Managing backups of SQL Server objects
Backing up data and transaction logs
Manage tables, views, stored procedures, triggers, indexes, rules, default values, and user-defined data types
Establish full text indexing, database diagrams, and database maintenance plans
Input and output data
converting data
Perform various network management tasks

On a computer with Microsoft Windows NT operating system, SQL Server Manager is installed by SQL Server Setup and defaults to the server component, while running Windows NT and Microsoft Windows 95 on the machine, it will be defaulted to the client side component. You will start the data transfer Service (Dts,data Transformation Services) from the graphical user interface of SQL Server Manager.

Data transfer Service (Database Transformation Services, DTS)
Data transfer services allow you to enter and output data between multiple heterogeneous data sources using a database based OLE architecture, or to transfer database and database objects between multiple computers that use SQL Server 7.0, and you can also use data transfer services to More easily build data warehouses and data centers in an online transaction processing system (OLTP).

DTS Wizard allows you to interactively create DTS packages that import, export, validate, and transfer data through OLE DB and ODBC. DTS Wizard also allows you to copy illustrations (schemas) and data between relational databases.

SQL Server Query Analyzer (Query Analyzer)
SQL Server Query Analyzer is a graphical query tool that allows you to analyze a query, execute multiple queries, view data, and get index recommendations. 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 consecutive 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 criteria, or export an action step to a screen, file, or datasheet. Using SQL Server Profiler, you can rerun the last action that was captured. This tool helps application developers identify transactions that can degrade application performance. This feature is useful when porting an application based on a file architecture to a client/server architecture, as its final step includes optimizing applications that target a new client/server environment.

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

In the Tools menu of SQL Server Manager (Enterprise Manager), point to "Data Transformation Services" and click "Import data."
In the dialog window for select data Source (Choose a), select Microsoft Access as the data source, and then enter your. MDB file name (MDB is the file name extension) or select Browse file.
In the dialog window for select data destination (Choose a destination), select Microsoft OLE DB Provider for SQL server, select the database server, and then click the authentication mode that you want.
In the dialog window for specify table backup or query (specify table copy or query), click Copy Tables.
In the "Select Data Source" dialog window, click Select All.

Porting Microsoft Access queries
You can transfer an Access query to SQL Server in one of the following formats:

Transactional SQL script (Transact-cripts)
Transactional SQL statements are typically called by database programs, but you can also run them directly using SQL Server Query Analyzer contained in SQL Server 7.0. SQL Server Query Analyzer helps developers test transactions with SQL statements, or run transactional SQL statements that perform query processing, data manipulation (insert, modify, delete), and data definition (CREATE TABLE).

Stored procedures (Stored procedures)
Developers can transfer most of the transactional SQL statements generated from Access queries (find, insert, modify, delete) to stored procedures. Stored procedures written with 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 called either with parameters or without parameters, either by database programs or by SQL Server Query Analyzer manually.

View (views)
A view is a virtual table that displays specific rows and columns from one or more tables. They allow users to create queries without directly performing complex connections that form the basis of a query. The view does not support the use of parameters. Views that connect multiple data tables cannot be modified with an INSERT, update, or DELETE statement. The view is called by a transactional SQL statement, or it can be used in a program segment that runs in SQL Server Query Analyzer. The ORDER by sort clause in the view is not supported by the SQL Server view and the SQL-92 standard. For additional information about transactional SQL, stored procedures, and views, see the SQL Server Online reference book.

SQL Server Migration selection and recommendations for access query types
A SELECT statement can be stored in a transaction-processing SQL file, stored procedure, or view. Establishing a stored procedure is the best way to separate database application development from the physical implementation of database design. Stored procedures can be created in one place and invoked by the application.
If the database on which the stored procedure is based changes and the stored procedures are carefully modified to reflect these changes, the call to the stored procedure will not be compromised.

Cross-table (crosstab)
Cross tables are often used to summarize reports.

A crosstab table of access can be executed by a transactional SQL statement in a SQL program segment, stored procedure, or view. Whenever a query is issued, the data connection is repeated to ensure that the most recent data is being used.

Depending on the actual application, the more appropriate approach is to store the data in the crosstab as a temporary table (refer to Make table below), and the temporary table needs less resources, but the temporary table is established with only one snapshot of the data (snapshot).

Create a table (Make table)
Make table (creating tables) in Access can be performed through the Build Table statement "CREATE TABLE" in a transaction-processing SQL script or a transaction in a stored procedure. The syntax looks like this:
[{Top Integer | Top integer PERCENT} [with TIES]]
[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, but the way to execute the UPDATE statement better is to create a stored procedure.

The Allend statement can be stored in the transaction _sql script, but the way to execute the APPEND statement better is to create a stored procedure.

porting queries to stored procedures and views for Microsoft Access

Each access query must be replaced with the following series of statements:
< SELECT, UPDATE, DELETE, INSERT, CREATE TABLE statement from Microsoft Access >

<place (SELECT only, with no parameters) Microsoft Access query>

For each access query, you should perform:

Open access, and then in SQL Server, open SQL Server Query Analyzer.
Click the "Queries" tab in the Database window of access and click the "Design" button.
Click the SQL button on the View menu.
Paste the entire query into SQL Server Query Analyzer.
Test the syntax, save the transaction processing SQL statements for later use, or execute the statements in the database. You can choose to save the transactional SQL statement to a script program.

Migrate Microsoft Access queries to transactional SQL statements

Most access queries should be converted to stored procedures and views. However, some statements that are not commonly used by application developers can be stored as transactional SQL scripts, a text file with SQL as the file name extension. These files can be run in SQL Server Query Analyzer.

If you're going to convert some access queries to SQL files, consider putting these transactional SQL statements into several scripts differently, depending on how they are used. For example, you can categorize transactional SQL statements that must run at the same frequency into the same script. In another script, you should include all transactional SQL statements that run only under certain conditions. In addition, the transactional SQL statements that must be executed in a certain order should be grouped into a discontinuous script.

To transfer an access statement to a transaction-processing SQL file

Copy statements to SQL Server Query Analyzer
Parse a statement using the Blue Multi-option icon
Execute the statement at the appropriate time

Developers who want to perform query tasks in Access to create tables (make table) have several options in SQL Server. Developers can create one of the following objects:

A view
The view has the effect of a dynamic virtual table that provides the most recent information. This is an input/output enhancer because whenever a query is issued it requires a connection to reproduce the data table.

A temporary table
A temporary table creates a snapshot of a connected user session. You can create a local or global temporary table. A local temporary table is visible only in the current session, and the global temporary table is visible in all sessions. Precede the name of the local temporary table with a single number prefix ((#table_name), and precede the name of the global temporary table with a two-digit prefix (# #table_name). Queries against temporary tables perform very quickly because they usually take a result set with only one table, rather than dynamically connecting multiple tables together.
For additional information about temporary tables, see the SQL Server online reference book.

Data Transformation Services (DTS) in SQL Server 7.0 allows you to create a package to implement the standardization, automation, and scheduling of temporary tables. For example, when you migrate the Northwind sample database in Access 2.0, the crosstab for quarterly data reports can be transformed into a view or a data transformation that can establish temporary tables on a spec basis. For additional information about DTS, see the SQL Server online reference book.

Other Design Considerations
Here are some additional issues you must consider when you migrate your Access application to SQL Server:

Using Parameters
SQL Server stored procedures with parameters require a syntax format different from the 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] & "," & [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] & "," & [name], Employees.country, Orders. [Shipped Date];

SQL Server 7.0 Format:

CREATE PROCEDURE Emp_sales_by_country
@BeginningDate datetime,
@EndingDate datetime
SELECT Orders. [Order ID], [last name] + "," + [name] as salesperson, Employees.country,
Orders. [Shipped Date], [order subtotals]. Subtotal as [Sale Amount]
From Employees INNER JOIN (Orders INNER J

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.