Microsoft Access
  Home arrow Microsoft Access arrow Working with Stored Procedures in an MS Ac...
ASP Free Forums 
.NET  
ASP  
ASP Code  
ASP.NET  
ASP.NET Code  
BrainDump  
C#  
Code Examples  
Database  
Database Code  
IIS  
Microsoft Access  
MS SQL Server  
Silverlight  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
Mobile Linux 
App Generation ROI 
IBM® developerWorks 
ASP Web Hosting  
ASP.NET Web Hosting 
Windows Web Hosting
 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
MICROSOFT ACCESS

Working with Stored Procedures in an MS Access Project
By: Jayaram Krishnaswamy
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 13
    2007-01-17

    Table of Contents:
  • Working with Stored Procedures in an MS Access Project
  • Creating a Stored Procedure
  • A Stored Procedure Without Parameters
  • Does MS Access Project support the directionality of the parameters?

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    Working with Stored Procedures in an MS Access Project


    (Page 1 of 4 )

    This tutorial shows the steps involved in creating stored procedures using MS Access 2000 with the back end SQL 2000 Server. It describes three kinds of stored procedures including procedures that return values in parameters.

    Microsoft Access Project gives you access to data stored on SQL Servers via familiar OLEDB technology. With an MS Access Project you can work in the client/ server mode as easily as in the file server mode, a mode you would have handled with earlier (prior to Access 2000) versions. An MS Access Project can only be created to work with MS SQL Server.

    With the Access Project construct, Microsoft has extended the playground of MS Access to the RDBMS arena. The data is all stored on the SQL Server (or MSDE, its junior version), but the Access application will have access to this data via the OLEDB connectivity. The reader will benefit from reading the earlier article on creating an MS Access Project by connecting to a SQL Server by linked tables and the article on stored procedures.

    Test data used in the tutorial

    A Microsoft Access Project will be created to obtain data used in this tutorial. First, create an MS Access Project and choose to create a new project. In this tutorial MS Access 2002 was used. An MS Access project will be associated with a SQL Server. For this tutorial an MSDE desktop server will be used, called XPHTEK/Test.

    When the project is created the first thing that comes up is the Microsoft SQL Server Database Wizard as shown in the next picture. Make sure at this point that the server is running. If it is not, you can start the server from the Services in Administrative Tools which you should find in the Control Panel. You may want to change the SQL Server by clicking on the drop-down. The name of the database that will be created on the SQL Server will be the Project name with the suffix "SQL" appended, in this case, parametersSQL.

    When you click on the Next button it will take you to the screen informing you that the program has enough information to create the database. If you click on the Finish button on this screen it creates the project as shown in the next picture. The Table object comes up in default, but for the picture shown it has been changed to Queries to show that you can create a function, a view or a stored procedure on the database you just now created.

    Now you can see this database in the SQL Server Management Studio as shown in the next picture.

    The project in MS Access at present does not have any tables except for some wizards which may let you create the various objects by guiding you through the process, including tables. But the parametersSQL database has all the system related tables whose owner is dbo. In order to work with this database, three tables were imported from the Northwind.mdb (from the project file's menu drop-down, click on Get External Data-->Import...). Import data which must be on most of the machines which have MS Access.

    Now this project has the three tables. This automatically adds the three tables to the MSDE Server named Test. You may verify this in the Management Studio by looking up parametersSQL. You may need to refresh the database's node to see the added tables.

    More Microsoft Access Articles
    More By Jayaram Krishnaswamy


       · Using stored procedures you are buying in security so that your code is safe from...
       · Hi,I liked your article on stored procedures. I was wondering if we can use...
       · Hi,I also have the same issue. I right now have application which uses Tables...
     

    MICROSOFT ACCESS ARTICLES

    - Improving Construction of Statistical Proces...
    - How to Monitor Website Traffic using Statist...
    - Chi Square Test of Independence with MS Excel
    - Two-Way ANOVA (Analysis of Variance) in Micr...
    - Converting a MySQL Database to an Excel Work...
    - Linking SQL Express 2005 Tables to MS Access...
    - Working with Access Projects in Access 2007
    - Exploring Access 2007
    - Working with Stored Procedures in an MS Acce...
    - Creating and Using Action Queries
    - Creating Data Access Pages with Charts using...
    - Advanced Ideas using VBA
    - VBA Details
    - Updating Records in MS Access
    - Using ADO`s Record Object with URLs





    © 2003-2010 by Developer Shed. All rights reserved. DS Cluster 4 Hosted by Hostway
    For more Enterprise Application Development news, visit eWeek