Automatically run a query when a table is updated...??

[matterhorn]

Hello,
This question/problem is two fold....

First, I would like to run a query whenever a table is updated or modified. I have a linked table to another database on a server. This is updated daily by other users. My goal is to run a query on my Access database whenever this update occurs on the server's machine. Is this possible, maybe a stored procedure???

Secondly, I would like to automatically generate a table with the result set of this query on the same database that contains the query.

In a nutshell....I would like to break up a table into 7 differant tables, whenever the original table is updated....make any sense? Any ideas? Please let me know if you have any thoughts.

Thanks so much!!

 

[oharab]

The quick answer is no. Access does not support triggers, which is what you are talking about.
The full answer is Depends on how your users are updating the data. You can't if users are entering directly into a table, but you can use the After Insert or After Update events of a form to code the updates if you are using a data entry form.
For absolute security, you probably want to wrap your code up in a transaction, so that if one update fails, you can roll back the other updates so you're not left with orphaned data in one of your other tables.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------
Want to get great answers to your Tek-Tips questions? Have a look at F

 

[ProgramError]

I used a similar idea to indicate to other users that a new record had been entered. It uses the on timer event on each users frontend control form to check if a value in an admin table is set or not.

Let me know if you want further details


Program Error
Programmers do it one finger at a time!

 

[matterhorn]

Thanks for the information...I just have a few more questions...

The users will be entering information via a form. So the possibility of using After Insert or After Update event will work. So that takes care of getting to the point of requesting the system to generate the queries, but... that leads me to the second question. If the queries are successfully created, can they be automatically (coded) to be entered into a table that mirrors the query? Please let me know your thoughts.

Thanks for the help so far!

Matt

 

[oharab]

I'm not sure what you're getting at.
The queries can do whatever you like! You can use an update or append query to copy the data into a table, you can shift data round using recordsets, you could even output them to a text file, whatever you can do with code, you can do it with in the After Update event.

B.

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------
Want to get great answers to your Tek-Tips questions? Have a look at F