How do you trigger a query to run automatically 1

[intrepidtechie]

I am looking for a method to trigger a query each time the table gets updated.
For your information the table is getting updated through a VB data import routine.
If you can provide any information on how to make a auto firing query that would be great.

Thanks in advance,
IntrepidTechie

 

[Blorf]

Er, can you not have the vb data import routine also run your query after the import?

ChaZ

 

[intrepidtechie]

I would like to run it within Access not in the Vb routine.

 

[MoLaker]

Perhaps the easiest way might be to create another table and store a "flag" in it when the VB runs. Then when you open the DB in Access, have it check to see if the flag is set. If so, run your query and reset the flag.

 

[intrepidtechie]

Hello MoLaker,
I have never done this "flag" thing. Could you provide an example maybe.

 

[MoLaker]

It's only a term I used. I simply meant for you to store a value in a table such as a TRUE or a 1 or whatever you decide when the VB runs. Then look to see if that value is there when you open the db in Access. If I were doing it, I would most likely use a Boolean field and set it's condition TRUE or FALSE. TRUE when the VB runs and reset it back to FALSE when you open Access and run your query.

 

[intrepidtechie]

Hello MoLaker,
I think what you are trying to explain is to keep a boolean value to check if the VB process ran or not.
If the Vb runs and updated the DB the set the flag to True and run the query and then reset it back to False after the query has done its work.
I got the logic but an example for this would really help.
Let me know.

 

[MoLaker]

OK. First create a new table containing a single boolean field with the default value set to FALSE. Insert a single record. I'll call the table tblUpdate and the field UpdateFlag.

Next, in your VB procedure include an UPDATE query to set the boolean field to TRUE.

"Update tblUpdate SET UpDateFlag = TRUE"

Then in Access, create a macro named Autoexec to run a routine which checks the value of the flag. A DLookUp() would work well here.

If DLooKUp("[UpDateFlag]","tblUpDate")=TRUE then
   'run your update query here
   'then reset the flag
   Docmd.RunSQL "Update tblUpdate SET UpDateFlag = FALSE"
EndIf

When the VB procedure runs, the flag is set to TRUE.
When the Access application is opened, the Autoexec macro will execute automatically and run the routine to check for the TRUE flag. If TRUE, do your thing and reset the flag to FALSE. If not true, don't do anything.

 

[intrepidtechie]

Hello MoLaker,
I did exactly as you mentioned. Add the code to the VB routine and created a table and wrote the macro.
When I ran the Vb routine it went well (I think).
Then I opened the db to check if it ran the query. But when I opened the db it gave me a alert
"Microsoft Access can't find the name 'IF' you entered in the expression". You may have specified a control that wasn't on the current object without specifying the correct form or report context.

I used the type of macro as RunMacro and the SQl code I wrote is
' Check the Boolean value
IF DLooKUp("[UpDateFlag]", "tblUpdate") = TRUE then
' the query name
' here am I to add the actual SQL code in the query or just the query name
Update_Zeros
' reset the flag here
Docmd.RunSQL "Update tblUpdate SET UpDateFlag = FALSE"
EndIf

What am I doing wrong.

 

[PHV]

Seems you're confused with macro, sql and VBA ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MoLaker]

Sorry, I made some assumptions I probably should not have made.

You can't just run the code sample I gave directly in a macro. Macros basically run built-in commands, functions or, in your case, a custom function. So, the code sample above must be in a function you write.

Create a module (object). In that module insert a new public function. Name it whatever you like, but I'll call it UpdateData in this example.

Public Function UpdateData()
   ' Check the Boolean value in tblUpdate
   If DLookup("[UpDateFlag]", "tblUpdate") = True Then
      ' assuming your query is named 
      'Update_Zeros, open the query
      [b]DoCmd.OpenQuery "Update_Zeros"[/b]
      ' reset the flag here
      [b]DoCmd.RunSQL "Update tblUpdate SET UpDateFlag = FALSE"[/b]
   End If
End Function

Then for the first and only action in your autoexec macro you would enter RunCode and the function to be ran (entered in the Function Name field) would be the function you just created - in my example, UpdateData.

As a note, you can run SQL statements in a macro, but you cannot make decisions such as IF/THEN statements. If it did not matter if your Update_Zeros query ran every time the database was opened, you could have 2 actions in your macro and not use a functions. The first action would be to OpenQuery - "Update_Zeros" and the 2nd action would be to RunSQL - "Update tblUpdate SET UpDateFlag = FALSE".

Lastly, to check to see if your VB ran correctly and set the flag, open your database while holding down the SHIFT key. This will prevent the autoexec macro from running and you'll be able to look at the data to see if the VB ran correctly.