German Mikulski
asked on
Aссess 2010 database macro
I need to create a macro, that would start after a new record is added to a table. How do I do it?
Also, after the macro is executed, all records from the table should be deleted. Is that possible?
Also, after the macro is executed, all records from the table should be deleted. Is that possible?
ASKER
I have found an After Insert macro in Table Tools, but the list of offered actions there is very limited and does not contain "OpenQuery" - the one which is needed.
In terms of deleting all records, form option will not work, as data is added from a web form directly into the table.
In terms of deleting all records, form option will not work, as data is added from a web form directly into the table.
Is this a Web Database?
If so, then you're quite limited in what you can do. Access 2013 moved to Web Apps, which have more functionality, but I don't know if you'll ever be able to do what you want in a Web Database or Web App. Web Databases/Apps are fine for simple CRUD (Create-Read-Update-Delete ) actions, but when you get into more complex needs, you often find limitations which prevent you from doing what you want.
You can create a "hybrid" app, where the data resides on Sharepoint/Azure, but your application is a standard, desktop Access database.
If not, then you might be able to create another Macro, and use RunDataMacro action in the AfterInsert table macro.
If so, then you're quite limited in what you can do. Access 2013 moved to Web Apps, which have more functionality, but I don't know if you'll ever be able to do what you want in a Web Database or Web App. Web Databases/Apps are fine for simple CRUD (Create-Read-Update-Delete
You can create a "hybrid" app, where the data resides on Sharepoint/Azure, but your application is a standard, desktop Access database.
If not, then you might be able to create another Macro, and use RunDataMacro action in the AfterInsert table macro.
ASKER
Not, it is just an ordinary .accbd database. I created a separate macro, that would run the needed query. But RunDataMacro shows only data macros, so my macro wasn't displayed. What else can I do?
I'm not sure. I don't really use macros, and haven't for quite some time. From my undestanding, however, Table-based macros (i.e. DataMacros) can only execute other DataMacros. Here's what MSDN has to say about DataMacros:
In other words - I don't think you'll be able to do what you're after, unless you can add code to the web page to handle those actions.
Data macros are a new feature of Access 2010 which enable you to add logic to events that occur in tables, such as adding, updating, or deleting data. They are similar to "triggers" in Microsoft SQL Server. This article shows you how to create and debug data macros.From Here: https://support.office.microsoft.com/en-in/article/Create-a-data-macro-b1b94bca-4f17-47ad-a66d-f296ef834200
In other words - I don't think you'll be able to do what you're after, unless you can add code to the web page to handle those actions.
ASKER
But isn't it possible to write sone kind of vba code that woyld trigger these actions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could do this on a Form, of course. Using a combination of Form events, you could determine when a New Record is added, and then run your code, and then delete all records in the table.