How do I make a stored procedure in MS Access?

by

Access 2010 has both stored procedures, and also has table triggers. And, both features are available even when you not using a server (so, in 100% file based mode).

If you using SQL Server with Access, then of course the stored procedures are built using SQL Server and not Access.

For Access 2010, you open up the table (non-design view), and then choose the table tab. You see options there to create store procedures and table triggers.

For example:

screenshot

Note that the stored procedure language is its own flavor just like Oracle or SQL Server (T-SQL). Here is example code to update an inventory of fruits as a result of an update in the fruit order table
alt text

Keep in mind these are true engine-level table triggers. In fact if you open up that table with VB6, VB.NET, FoxPro or even modify the table on a computer WITHOUT Access having been installed, the procedural code and the trigger at the table level will execute. So, this is a new feature of the data engine jet (now called ACE) for Access 2010. As noted, this is procedural code that runs, not just a single statement.

More Related Contents:

  1. Combine values from related rows into a single concatenated string value
  2. Bang Notation and Dot Notation in VBA and MS-Access
  3. “General error Unable to open registry key Temporary (volatile) …” from Access ODBC
  4. Is there a simple way of populating dropdown in this Access Database schema?
  5. How can I get table names from an MS Access Database?
  6. Is there any way to create multiple insert statements in a ms-access query?
  7. Difference between Microsoft.Jet.OleDb and Microsoft.Ace.OleDb
  8. How to diagnose MS access crashes
  9. “Operation must use an updateable query” error in MS Access
  10. VBScript & Access MDB – 800A0E7A – “Provider cannot be found. It may not be properly installed”
  11. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  12. MS Access LIMIT X, Y
  13. Prevent enabling of shift bypass – Access 2010
  14. Access query producing results like ROW_NUMBER() in T-SQL
  15. Is MS Access (JET) suitable for multiuser access?
  16. Calling stored procedure using VBA
  17. Save password for ODBC connection to MS SQL server from MS Access 2007
  18. How to write Case Sensitive Query for MS Access?
  19. Access VBA: Find max number in column and add 1
  20. ConcatRelated function in a query
  21. Access 2007 – INSERT and instant SELECT doesn’t retrieve the inserted data
  22. Create sequential ID value based on the year that a record is added
  23. How can I modify a saved Microsoft Access 2007 or 2010 Import Specification?
  24. MS Access query returning Chinese characters – possible table corruption?
  25. Using a centralized VBA module in multiple Access databases
  26. Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess
  27. How to execute a query in ms-access in VBA code?
  28. How to add a menu item to the default right click context menu
  29. Force all users to disconnect from 2010 Access backend database
  30. ODBC Call Failed with stored procedure – Pass through query

Leave a Comment