March 7, 2018 at 9:39 am
Hope I explain it clear enough for people to understand what I'm trying to accomplish.
I have tbl1 that gets populated via an application. When tbl1 gets a new record, I want an Append Query to run, which will insert a new record into tbl2.
I have created the Append Query and runs fine when I run it manually. However, I havent figured out how to have that Append Query run automatically when tbl1 get a new record.
Hopefully someone can assist.
Thanks all!
March 7, 2018 at 10:06 am
This sounds like a job for a trigger.
You would create an AFTER INSERT trigger on tbl1 and in that you put your append query.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql
Syntax:CREATE TRIGGER [schema].[trigger name]
ON tbl1
FOR AFTER INSERT
AS
BEGIN
<append query>
END
In your append query, you will want to have it look at the table called INSERTED instead of tbl1. INSERTED contains all of the rows that were inserted into the table.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 7, 2018 at 10:56 am
DarthBurrito - Wednesday, March 7, 2018 9:39 AMHope I explain it clear enough for people to understand what I'm trying to accomplish.I have tbl1 that gets populated via an application. When tbl1 gets a new record, I want an Append Query to run, which will insert a new record into tbl2.
I have created the Append Query and runs fine when I run it manually. However, I havent figured out how to have that Append Query run automatically when tbl1 get a new record.
Hopefully someone can assist.
Thanks all!
If your back-end tables are in SQL Server, the previous suggestion is spot on. On the other hand, if your data is stored locally in Access tables, you don't have the trigger capabilities of SQL Server to do the append, and you will either need to alter the application that records to tbl1, or get really creative with Access automation.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
March 7, 2018 at 11:21 am
WendellB - Wednesday, March 7, 2018 10:56 AMIf your back-end tables are in SQL Server, the previous suggestion is spot on. On the other hand, if your data is stored locally in Access tables, you don't have the trigger capabilities of SQL Server to do the append, and you will either need to alter the application that records to tbl1, or get really creative with Access automation.
You know Access better than I do so..... I know Access doesn't have triggers but I did hear about data macros when those came out and was thinking that might be an option in this case if it's Access tables. Is that something that the poster could use? I've never used them but they were reported to be similar to triggers.
Sue
March 7, 2018 at 11:24 am
WendellB - Wednesday, March 7, 2018 10:56 AMDarthBurrito - Wednesday, March 7, 2018 9:39 AMHope I explain it clear enough for people to understand what I'm trying to accomplish.I have tbl1 that gets populated via an application. When tbl1 gets a new record, I want an Append Query to run, which will insert a new record into tbl2.
I have created the Append Query and runs fine when I run it manually. However, I havent figured out how to have that Append Query run automatically when tbl1 get a new record.
Hopefully someone can assist.
Thanks all!
If your back-end tables are in SQL Server, the previous suggestion is spot on. On the other hand, if your data is stored locally in Access tables, you don't have the trigger capabilities of SQL Server to do the append, and you will either need to alter the application that records to tbl1, or get really creative with Access automation.
Oh whoops. I did not realize this was in the Microsoft Access section. My mistake. I need to pay more attention :(.
What about an after insert macro event?
https://msdn.microsoft.com/en-us/library/office/ff196099.aspx?f=255&MSPPError=-2147217396
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 7, 2018 at 12:19 pm
Thanks all! Yeah, so all this is happening is in Access with Access tables.
I seen the After Insert event, but couldnt make sense of the steps after I select that event. The drop down options didnt make sense or stood out where I can run the Append Query.
I guess I need a bit more guidance on how to set up the After Insert event and which drop-down selections I should use. That's where I was getting lost.
March 7, 2018 at 1:18 pm
DarthBurrito - Wednesday, March 7, 2018 12:19 PMThanks all! Yeah, so all this is happening is in Access with Access tables.I seen the After Insert event, but couldnt make sense of the steps after I select that event. The drop down options didnt make sense or stood out where I can run the Append Query.
I guess I need a bit more guidance on how to set up the After Insert event and which drop-down selections I should use. That's where I was getting lost.
I think that WendellB is correct here. I am a bit out of my element, but have been looking online and am trying to take a stab at this still.
From looking around online, it sounds like you can have an after insert macro call some VBA.
I think you'd need to either use information from this site:
https://msdn.microsoft.com/en-us/vba/access-vba/articles/form-afterinsert-event-access?f=255&MSPPError=-2147217396
which explains how to set up the VBA to catch an after insert, or this site:
https://stackoverflow.com/questions/37283681/ms-access-run-code-after-a-row-is-updated
which gives a neat way to call a VBA function using an after insert macro.
Either way, I think that you are going to need to do some coding to make that work.
if you can tie the append query to a button, I believe you should be able to make the button not-visible and then trigger the button click from the VBA which would be called from the after insert macro.
I do not have a copy of Access, so I don't have a good way to test this unfortunately.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 7, 2018 at 2:26 pm
DarthBurrito - Wednesday, March 7, 2018 12:19 PMThanks all! Yeah, so all this is happening is in Access with Access tables.I seen the After Insert event, but couldnt make sense of the steps after I select that event. The drop down options didnt make sense or stood out where I can run the Append Query.
I guess I need a bit more guidance on how to set up the After Insert event and which drop-down selections I should use. That's where I was getting lost.
You can select the After insert event for the data macro. When you select that, the macro builder window should open for you to build your macro. Different actions are available depending on what event is selected.
In this post, he is building a data macro - has some screen shots and such that may give you a better idea. And he is using the after update event to insert a record into another table, very similar to what you are trying:
Leveraging Data Macro in Microsoft Access 2010
Sue
March 7, 2018 at 2:35 pm
DarthBurrito - Wednesday, March 7, 2018 12:19 PMThanks all! Yeah, so all this is happening is in Access with Access tables.I seen the After Insert event, but couldnt make sense of the steps after I select that event. The drop down options didnt make sense or stood out where I can run the Append Query.
I guess I need a bit more guidance on how to set up the After Insert event and which drop-down selections I should use. That's where I was getting lost.
It depends on the version of Access you are using. If you are using Access 2010, 2013 or 2016 then you do have the data macros that can be used to perform various functions. Take a look at Attaching Logic to Data Using Data Macros ... - that's the approach that Sue_H suggested earlier in the thread and bmg002 suggested just above. If you are using an older version, then the only option is to use code attached to the After Insert event on tbl1. And that means you need to be comfortable with Visual Basic in Access. You can use Access macros to run an append query, but getting the specific data to append to tbl2 will be tricky to near impossible.
Is there some compelling reason this needs to be done in Access. As bmg002 suggested in his first response, this is almost trivial to do if the data is done in SQL Server, though it does require that you understand triggers, and of course actually have a SQL Server installation available to use.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
March 8, 2018 at 7:30 am
Here's a bit of a backstory of what's going on.
We have AppX that uses Databasics (SQL) database and an Access database in the background.
Databasics decided to upgrade DB1 that was an old version to the latest and greatest version of Databasics, I'll call this new database, DB2.
AppX works just fine with DB1. I ran a few processes to make sure everything I'm suppose to get as the end result is what I'm getting. So we're good there.
When AppX is pointed to the new DB2, many parts of the process works. However, in Access, there are two tables that are not being populated correctly by AppX.
The person that created AppX is no longer with the company and is long gone, so I cant ask him/her.
I know the tables that are not populating and for table1, I thought maybe I can create an Append Query that would create a single record when another related table gets a new record.
I tested the Append Query and it worked. Now I just need to figure out how to fire off that Append Query when the related table gets a new record.
I did download a free decompiler, but I dont know how to read C#, but I did find the sections where AppX is instructed to insert a new record into the two problem tables.
I hope all above wasn't confusing.
March 8, 2018 at 8:46 am
DarthBurrito - Thursday, March 8, 2018 7:30 AMHere's a bit of a backstory of what's going on.
We have AppX that uses Databasics (SQL) database and an Access database in the background.
Databasics decided to upgrade DB1 that was an old version to the latest and greatest version of Databasics, I'll call this new database, DB2.
AppX works just fine with DB1. I ran a few processes to make sure everything I'm suppose to get as the end result is what I'm getting. So we're good there.When AppX is pointed to the new DB2, many parts of the process works. However, in Access, there are two tables that are not being populated correctly by AppX.
The person that created AppX is no longer with the company and is long gone, so I cant ask him/her.
I know the tables that are not populating and for table1, I thought maybe I can create an Append Query that would create a single record when another related table gets a new record.I tested the Append Query and it worked. Now I just need to figure out how to fire off that Append Query when the related table gets a new record.
I did download a free decompiler, but I dont know how to read C#, but I did find the sections where AppX is instructed to insert a new record into the two problem tables.I hope all above wasn't confusing.
Well, I'm usually confused, and easily too, but doing a bit of web research indicates that Databasics provides SAAS (Software As A Service) using a cloud-based database to track time and expenses across a number of industries. If that's the case, do you really know what their data storage mechanism is? (It could be SQL Azure, but it could be AWS or some other cloud database.) Do you know if any of the tables you are dealing with are actually linked tables rather than local Access tables? (The linked table manager will tell you very quickly.) Also, you haven't answered the question as to what version of Access is being used...
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply