access|server| Data | Database Summary: This article describes the procedures and tools for converting an Access database to a SQL Server database
Brief introduction
As the user's demand for enterprise-class high-performance databases grows, users often convert to a client-server environment in Microsoft SQL Server from the file-server environment of the Microsoft Access jet engine. 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-scripts)
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:
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, but the way to execute the UPDATE statement better is to create a stored procedure.
APPEND (ADD)
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:
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, 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.
