Migrating Microsoft Access Queries

You must move your existing Access queries into SQL Server in one of these formats:

Transact-SQL scripts
Transact-SQL statements are usually called from database programs, but you can use SQL Server Query Analyzer, included in SQL Server 7.0, to run them against the database directly. SQL Server Query Analyzer helps developers to test Transact-SQL statements against development databases, or to run Transact-SQL statements that perform queries, data manipulation (INSERT, UPDATE, DELETE), or data definition (CREATE TABLE).
Stored procedures
Developers can move most Transact-SQL statements that originate from Access queries (SELECT, INSERT, UPDATE, and DELETE) into stored procedures. Stored procedures written in Transact-SQL can be used to encapsulate and standardize your data access, and are actually stored within the database. Stored procedures can run with or without parameters and are called from database programs or manually from SQL Server Query Analyzer.
Views
Views are used as virtual tables that expose specific rows and columns from one or more tables. They allow users to create queries without directly implementing the complex joins that underlie the query. Views do not support the use of parameters. Views that join more than one table cannot be modified using INSERT, UPDATE, or DELETE statements. Views are called from Transact-SQL statements, and can also be used in *.scripts that are run in SQL Server Query Analyzer. SQL Server views and the SQL-92 standard do not support ORDER BY clauses in views.

For more information about Transact-SQL, stored procedures, or views, see SQL Server Books Online.

Access query type	SQL Server migration options and comments
SELECT	A SELECT statement can be stored in a Transact-SQL file, a stored procedure, or a view. Creating stored procedures is the best way to separate the database application development from the physical implementation of the database design. Stored procedures are created in one place, and are called from the application. Calls to stored procedures will not “break” if the underlying database changes and the stored procedure is carefully modified to reflect these changes.
CROSSTAB	Crosstabs are often used for summary reports. An Access CROSSTAB can be implemented as a Transact-SQL SELECT statement in a SQL script, a stored procedure, or a view. The data join is reexecuted each time a query is issued, ensuring that the latest data is always used. Depending on the application, it might be appropriate to store data from the crosstab as a temporary table (see MAKE TABLE in the next row). The temporary table requires fewer resources, but offers only a snapshot of the data at the time the temporary table is created.
MAKE TABLE	An Access MAKE TABLE can be implemented as a Transact-SQL CREATE TABLE statement in a Transact-SQL script or stored procedure. The syntax 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	An UPDATE statement can be stored in a Transact-SQL script; however, the recommended way to implement an UPDATE statement is to create a stored procedure.
APPEND	An APPEND statement can be stored in a Transact-SQL script; however, the recommended way to implement an APPEND statement is to create a stored procedure.
DELETE	A DELETE statement can be stored in a Transact-SQL script; however, the recommended way to implement a DELETE statement is to create a stored procedure.