Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 30th, 2005, 10:41 PM
Authorized User
 
Join Date: Aug 2005
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to CarlosV
Default Trigger in Access 2003

Hello, I have a question about Access 2003, Can I create a trigger or something like that in Access 2003, if it's true, someone know how to create it ??

 
Old August 30th, 2005, 10:44 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Carlos,

What kind of trigger? What do you want to do if it's true?

Kevin

dartcoach
 
Old September 1st, 2005, 11:59 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You can develop triggers in several ways in Access.
In table design you can make the field Required, which will ask for input if the field is not updated.
You can also create a validation rule that makes sure the user enters a certain kind of data.
You can require a data type to make sure certain kinds of data are entered.
You can use an input mask in text fields to make sure data is entered in a certain way.

In you forms you can also add code to check the values that are entered in each field, and prompt the user for valid input if the data entered in the field isn't what you like.
You can also trigger events based on inputs or selections on form fields.

As he said, what do you want to do?

mmcdonal
 
Old November 6th, 2006, 08:36 AM
Registered User
 
Join Date: Nov 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

For me, I need a trigger to update a field in another DB, once a certain value is updated in the access DB.

El-Sawaf
 
Old November 6th, 2006, 11:58 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

This is easy to do afsawaf. What is the triggering event, and what data do you want to pass? Is it a new record each time, or an update to an existing record? Etc. What kind of connection will you use?



mmcdonal
 
Old November 9th, 2006, 08:35 AM
Registered User
 
Join Date: Nov 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

-Triggering event: On updating a row in MS Access to a certain value.
-What data do you want to pass?: Some data from that same updated row.
-Is it a new record each time, or an update to an existing record?: Update, no new insertions are required.
-Kind of connection to be used: No preference, the other DB is MS SQL 7.0.



El-Sawaf
 
Old January 12th, 2007, 08:32 AM
Registered User
 
Join Date: Jan 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If I understand the question correctly (i.e. "Can I create triggers in 'MS Access' like I can in Oracle/MS SQL Server?") then the answer is simply NO.

It is worth understanding the MS access architecture before I suggest how you can work around this however.

Your data lives in an MDB file, is read/managed by the JET RDBMS and your application layer is MS Access (this is not always understood).
eg.
Your Data (MDB file)
     |
     |
     v
JET (RDBMS) ----> perhaps also {ASP website (Application)}
     |
     |
     v
MS Access (Application)

It is the JET RDBMS engine that is not capable of using triggers in the conventional sense.

The only two solutions I know of are
Solution 1. Move your data out of the MDB file and into a 'better' RDBMS (Oracle/MS SQL Server etc). e.g. the MS SQL server you mention (You can do this easily in Access by using the 'upsizing wizard'). You can then add conventional triggers to your tables.
  If your application layer is MS Access forms and you use the upsizing wizard then everything will still work as before, and your access application simply becomes a .MDP (Access project) file and uses pass-through queries to get data from SQL Server. (i.e. where the data comes from becomes transparent to your old forms)

Solution 2.Add Application layer programming to achieve the trigger-like effect.
   a) If your application is MS Access, then you are presumably using MS Access forms, VBA etc. I think it is fairly simple to add code to each form that acts on_update etc.
   b) If you are using some other route via JET, such as a web application using ASP/ASP.NET then you need to add code into that. e.g. With a well architected ASP.NET app, you would place custom code in your Data Access Layer classes.

Cheers & Good luck.

 
Old April 23rd, 2007, 09:50 AM
Registered User
 
Join Date: Nov 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks "SurfJunkie", that was really informative.

El-Sawaf





Similar Threads
Thread Thread Starter Forum Replies Last Post
Create Trigger,Procedure, Function in MS-Access.? navneet_chauhan10 SQL Language 1 December 27th, 2007 09:33 AM
Trigger in Access 2003 sagardori Access 1 November 7th, 2007 06:43 AM
Access 2003 Treview simmyboy Access 5 July 18th, 2006 02:51 PM
Upgrading from Access 2002 to Access 2003 lryckman Access 1 July 16th, 2006 01:46 AM
Access 2003 n2suns Access 4 May 17th, 2005 03:37 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.