Ms Access Date Triggers Emulation

MS Access Date triggers emulation

If you are meaning that you need to execute say a query on a table, and that query will manage the execution of sending an email in respect of each offending row - then I dont think you'll have much luck;

You can open an email window from access, but this won't send an email by itself.

What are you using for your backend? If it's flexible, and you used SQL server for the backend then you could use an actual trigger which calls a stored procedure to send the email required.

Aside from this, all I can think of to do, would be

create a query which finds the rows for which an email is to be sent;
create a .net executable which receives a set of information as parameters & sends the email you require on the basis of that data,
on a form in your application, use a timer event to periodically execute the logic to open the query, using like an adodb.recordset and loop through the recordset and call the .net executable for each row returned by the query.

Hope this helps

Triggering an mdb query on server

If you're looking for something to run when a row is added, you're looking for "triggers" - which is not available in Access*. You find that sort of thing in proper RDBMSs like SQL Server, MySQL, etc.

If you're looking for something to run once a day, you could create a small VBS/PowerShell/etc. script and set up a scheduled task on the server to run it at an appropriate time.

You could also run the query in response to a page. If this is something that you want done after insert, then you can append the appropriate code to the end of the page or have it run as a part of the "success" page (if you have one).

* Unless you are using a .accdb (Access 2010 and later) file and the appropriate drivers. Then it appears you can use Event-Driven Data Macros to get the same effect. Thank you Gord Thompson.

Comparing Date Values in Access - Data Type Mismatch in Criteria Expression

You reported you get Data Type Mismatch error with this WHERE clause.

WHERE DateValue(Trim(Left([bc_TestingTickets].[notes],
    InStr([bc_TestingTickets].[notes],' ')))) > #4/1/2012#

That makes me wonder whether [bc_TestingTickets].[notes] can ever be Null, either because the table design allows Null for that field, or Nulls are prohibited by the design but are present in the query's set of candidate rows as the result of a LEFT or RIGHT JOIN.

If Nulls are present, your situation may be similar to this simple query which also triggers the data type mismatch error:

SELECT DateValue(Trim(Left(Null,InStr(Null,' '))));

If that proves to be the cause of your problem, you will have to design around it somehow. I can't offer a suggestion about how you should do that. Trying to analyze your query scared me away. :-(

Oracle - emulating a trigger on SELECT

Since I was only really looking to modify the select on 1 particular table, the answer for me was to rename the existing table and create a view with the original name.

How can I do a BEFORE UPDATED trigger with sql server?

MSSQL does not support BEFORE triggers. The closest you have is INSTEAD OF triggers but their behavior is different to that of BEFORE triggers in MySQL.

You can learn more about them here, and note that INSTEAD OF triggers "Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements." Thus, actions on the update may not take place if the trigger is not properly written/handled. Cascading actions are also affected.

You may instead want to use a different approach to what you are trying to achieve.

Ms Access Date Triggers Emulation