AutoRun query on table update 1

[eadiem]

Is there a way to do this?
I see there are macros to automatically run every night, but how about to run it when a table is updated.

 

[GingerR]

If you update your table from a FORM, then you can launch EVENTS like AfterUpdate, BeforeUpdate, etc.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[eadiem]

Well it is actually another application that is connecting to the db and reading the tables. It can't see forms but it can see tables.

 

[GingerR]

What application?

There are no events on tables in Access. In SQL Server, there are Triggers and Stored Procedures so like if a record is added or some data is changed in a certain way that you specify, you write a TRIGGER that makes something else happen. But this doesn't exist in Access.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[eadiem]

Thanks for your help. It sounds like a trigger would be the ideal solution.
The application is ArcGIS (mapping/GIS software). Unfortunately it can only read Access tables. If it could get queries that would solve my problem too.
I have several tables in my DB. Some of them are for display on my maps in ArcGIS. However, I have made these specifically for my map layout by combining data from several data storage tables, adjusting column headings, field size, creating an ID field required by ArcGIS, etc - they cannot be used for data storage. These map-specific tables are created by running make-table queries on several data storage tables. So I want to run these make-table queries every time the data storage tables are updated.
Make any sense?
Any suggestions?
Much appreciated.

 

[GingerR]

How are the data storage tables updated? Do you just enter data directly into them?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[eadiem]

They are created by exporting csv files from MicroMine software.

 

[GingerR]

So when you import in the csv files, can you just run code to re-vamp the ArcGIS tables? Is somebody pushing buttons to do this? How is this happening?

I think I'm missing something here....

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[eadiem]

That is what i am doing right now. When somebody imports a csv file, they then have to run the make-table queries (there are several) to create the tables for the map layouts. I am looking for a way to make this process automatic.
Also - sometimes the data in tables is modified after it is imported. So it is not always just "on import" that I need to re-create the layout tables.
Sorry for the confusion - hope this helps clear it up.

 

[GingerR]

How are they running the Make-Table queries now? You mean like one at a time? Not from a macro or vba or anything?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[eadiem]

you got it

 

[GingerR]

So...have you thought of putting the queries into a macro? That's the "baby step"--just listing them one after another, then you can just run the macro.

The "Toddler Step" would be to then make a form with a button on it, so that the user clicks a button to a macro that imports the new data, clicks a button to view/edit the data, and clicks another button to run your new macro which creates the GIS tables.

Getting out of your Pull-ups, you could fiddle around with VBA and just write the code:

Docmd.OpenQuery "DeleteQueryName1"
Docmd.OpenQuery "AppendQueryName1"

Docmd.OpenQuery "DeleteQueryName2"
Docmd.OpenQuery "AppendQueryName2"

In order to get rid of the pop-up messages "are you sure you want to add these records", etc., use

Docmd.SetWarnings FALSE

but make sure to set it back to TRUE when you're done.

Same thing in a macro--SetWarnings NO or YES.


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[eadiem]

Thanks for the idea. I will definitely give that a shot. I am also playing around with making my GIS read the queries, which would make life a lot simpler. Turns out it doesn't read make-table queries but with some effort may be possible to read select queries.
Really appreciate the help, thank you!!