access|server| Data | database
As users grow in demand for enterprise-class High-performance databases, users are often converted from the file-server environment of the Microsoft Access jet engine to
Client-server environment for Microsoft SQL Server. Access Upsizing Wizard in Microsoft Office 2000 enables you to implement data tables and
The query is transferred to SQL Server 7.0. If you are using an earlier version of Access, you can upgrade it to Access 2000 before you use the
Upsizing Wizard to migrate your application to SQL Server.
If you're not too willing to use Access 2000 and Upsizing Wizard to implement a migration, this article can be used as a way to migrate Access 2000 to SQL Server
A guide. 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
Be executed later. 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 strong
Force of the scheduling engine, a high degree of fault tolerance and an embedded copy 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
In a computer with Microsoft Windows NT operating system, SQL Server Manager is installed by SQL Server Setup and is defaulted as a service
Device component, and on a machine running Windows NT and Microsoft Windows 95, it defaults to the client side component. You will be from SQL Server
The manager's graphical user interface starts the data transfer service (Dts,data Transformation Services).
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 when using SQL
Transfer database and database objects between multiple computers in Server 7.0; You can also use data transfer services to more easily handle an online transaction
System (OLTP) to establish data warehouses and data centers.
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 type the number of relationships
Copy diagrams (schemas) and data between libraries.
SQL Server Query Analyzer (Query Analyzer)
SQL Server Query Analyzer is a graphical query tool through which you can analyze a query, execute multiple queries, view data, and get indexes
Suggestions. 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 SQL Server-generated
Events, filtering events based on user-specified criteria, or outputting an action step to a screen, file, or datasheet. Using SQL Server Profiler, you can restart
Performs the last action that was captured. This tool helps application developers identify transactions that can degrade application performance. In a base
This feature is useful when porting an application to a file architecture to a client/server structure, as its final step includes the targeting of new customers/services
The application of the environment of the device is optimized.
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 again and click the authentication mode 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 use SQL Server Query Analyzer, contained in SQL Server 7.0, to directly ship
Line them. SQL Server Query Analyzer helps developers test transactions with SQL statements, or run those that perform query processing, data manipulation (INSERT, repair
and data definition (CREATE TABLE) transaction SQL statements.
Stored procedures (Stored procedures)
Developers can transfer most of the transactional SQL statements generated from Access queries (find, insert, modify, delete) to stored procedures. Dealing with SQL with transactions
Statement writing stored procedures 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, either by database programs or manually by SQL Server Query Analyzer.
View (views)
A view is a virtual table that displays specific rows and columns from one or more tables. They allow users to create a search without directly performing a complex connection that forms the basis of a query
Polling 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. View is handled by transaction SQL
Sentence, or it can be used in SQL Server Query Analyzer to run the program segment. The SQL Server view and the SQL-92 standard do not support order by ordering in views
Sentence 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. The establishment of a stored procedure is the application of database development and database design objects
The best way to implement the separation. 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 broken
Bad.
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
be reproduced to ensure that the most recent data is being used.
Depending on the actual application, the appropriate method is to store the data in the crosstab table as a temporary table (refer to Make table below), and the temporary table to the resource's
There is less demand, but temporary tables are created with only one snapshot of the data (snapshot).
Create a table (Make table)
Make table (creating tables) in Access enables you to process SQL language-based statements by transaction processing SQL script programs or transactions in stored procedures
"CREATE TABLE" to perform. 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.
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 certain foot
In this program.
Migrate Microsoft Access queries to transactional SQL statements
Most access queries should be converted to stored procedures and views. However, some of the statements that are not commonly used by application developers can be stored as transaction-processing SQL feet
This is 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 using them differently to separate the transaction-handling SQL statements
In several scripting programs. 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 transaction-processing SQL statements that run only under certain conditions. In addition, transaction processing SQL statements that must be executed in a certain order should be categorized into a discontinuous foot
In this book.
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 the data
Table reappears to establish a connection.
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 tables are visible in all sessions. Prefix the name of a local temporary table with a single number ((#table_name), while the name of the global temporary table
Prefix (# #table_name) preceded by a two-digit number. Queries against temporary tables perform very quickly because they usually take a result set with only one
Tables instead of 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
When you migrate the Northwind sample database in Access 2.0, the crosstab for quarterly data reports can be converted to a view or one that can be built on a canonical basis
The data conversion of the temporary table. 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. [Employee ID] = Orders. [Employee ID]
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
As
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
