Execute sql stored procedure or sql query from Access.

If we need to execute sql stored procedure or a sql query from Access 2007, we can do it by following steps:-

1. On the create tab, click Query design in the other group.
2. Click close in the Show Table dialog box without adding any tables or queries.
3. On the Design tab, click Pass-Through in the Query Type workgroup.
4. Click Property Sheet in the Show/Hide workgroup to display the property sheet for the query.
5. In the query property sheet, place the mouse pointer in the ODBC Connect Strproperty, and then, click the Build (…) button.

   With the ODBC Connect Str property, you specify information about the database to which you want to connect. You can type the connection information, or click Build, and then enter information about the server to which you are connecting.

   The connectionString should be in format as

           “ODBC;DSN=YOUR_DSN_NAME;SRVR=YOUR_SERVER_NAME;DATABASE=YOUR_DB_NAME;UID=youruserid;PWD=youruseridpassword;”

     6.  When you are prompted to save the password in the connection string, clickYes if you want the password and logon name to be stored in the   connection string information.

     7. If the query is not the type that returns records, set the ReturnsRecordsproperty to No.

8. In the SQL Pass-Through Query window, type your pass-through query. For example, the following pass-through query uses the Microsoft SQL Server TOP operator in the SELECT statement to return only the first 25 orders in the Orders table from the sample Northwind database:

9.Exec i.e. ( < stored Procedure>

     or else if you want to execute a query then you can directly mention the query as “SELECT * FROM ”

10.To run the query, click Run in the Results group on the Design tab. For a SQL pass-through query that returns records, click Datasheet view on the status bar.
11. If necessary, Microsoft Access prompts you for information about your server database.

For more details please refer to:-
http://support.microsoft.com/kb/303968