[interworld.sharma]

The best course of action for you will depend on how you are approaching your data access. There are three approaches you can take: Use stored procedures Keep the queries in the code (but put all your queries into functions and fix everyt..

[Loftyvistas]

Moving Tables and Data

To use the DTS Wizard to transfer your Access data into SQL Server, you can use these steps:

1. In SQL Server Enterprise Manager, on the Tools menu, point to Data Transformation Services, and then click Import Data.
2. In the Choose a Data Source dialog box, select Microsoft Access as the Source, and then type the file name of your .mdb database (.mdb file extension) or browse for the file.
3. In the Choose a Destination dialog box, select Microsoft OLE DB Provider for SQL Server, select the database server, and then click the required authentication mode.
4. In the Specify Table Copy or Query dialog box, click Copy tables.
5. In the Select Source Tables dialog box, click Select All.

Migrating Microsoft Access Queries

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

• Transact-SQLscripts
  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).
• Storedprocedures
  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.
Migrating Microsoft Access Queries into Stored Procedures and Views

Each Access query must be placed into this set 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:

1. Open Access, and then in SQL Server, open SQL Server Query Analyzer.
2. In Access, in the Database window, click the Queries tab, and then click Design.
   
3. On the View menu, click SQL.
4. Paste the entire query into SQL Server Query Analyzer.
5. Either test the syntax and save the Transact-SQL statement for later use, or run the statement in the database. You can optionally save the Transact-SQL to a script.

Real Estate Malta Real Estate Dubai Eeal Estate Egypt

[justfunction]

hi dear
Microsoft Access allows you to export data to a variety of formats for sharing and analysis. In this tutorial, we walk through the process of exporting an Access table to an Excel workbook

[Jhon Butcher]

And once the data has been imported, the user can simply right click anywhere in the data results and select "refresh data" to re-query the database and pull in new data (assuming of course they don't have to change any criteria). If that will work for you, then you could just simply build the spreadsheet and send it to everyone and tell them to "refresh" the data whenever the want to.