Run a query monthly once.

[cynapattery]

Hi,
I want to run a query(to create a table) once in every month. But the application is run on a local computer. that means, on the first time when some one open the access, it should create the table. the next time it will be first time the application is run next month.
that means, it must not be on the first day of a month.
Please help,
Thanks,

 

[CJ_London]

Is your database opened and closed on a daily basis and does it have a splash form or similar opening form where you can use the on open event to check the date and then run the update query if required?

 

[cynapattery]

it is openned and closed randomly. to be precise only when need comes but mostly once in 2-3 days but cannot assure. therefore on the first time it is opened in a month, it should be updated.
splash form I do not have.

 

[CJ_London]

OK, I'm still trying to understand what you require. So a few more questions:

1. Is the table a new creation each month? i.e. the previous version of the table is deleted.
2. What fields are to go into the table and which ones are indexed?
3. Is the table populated as part of this creating - i.e. a make table - or is it created empty and populated through the month?

 

[cynapattery]

Hi CJ_London,
thanks for the help. I got it workign what I was trying to do.
-------------------
Private Sub proc_monthlyqry()
'the procedure which checks and run the query monthly once.
Dim str_monthlyqry As String
Dim cur_date As String
Dim cur_mon As String
cur_mon = Format(Date, "MM")
cur_date = cur_Year & cur_mon
str_monthlyqry = DLookup("check_month", "tbl_check_month")
If DLookup("check_month", "tbl_check_month") < cur_date Then
CurrentDb.Execute ("DROP TABLE Tbl_Qry_UMS_Data")
CurrentDb.Execute "Qry_UMS_Data"
'MsgBox cur_date
str_monthlyqry = "Update tbl_check_month set tbl_check_month.check_month = '" & cur_date & "' "
DoCmd.SetWarnings False
DoCmd.RunSQL str_monthlyqry
DoCmd.SetWarnings True
End If
End Sub
--------------------------



I have to runa qry once in every month when the first user access the program. and I created a table with the date and if the current date greater than previously saved date it run the qry and then save the current date in the table. therefore only the next month it will run again.

thanks again for the help..

 

[CJ_London]

Pretty much what I was going to suggest!

Glad you got it sorted

 

[anpesx]

Where should i paste this code? please!

Hi CJ_London,
thanks for the help. I got it workign what I was trying to do.
-------------------
Private Sub proc_monthlyqry()
'the procedure which checks and run the query monthly once.
Dim str_monthlyqry As String
Dim cur_date As String
Dim cur_mon As String
cur_mon = Format(Date, "MM")
cur_date = cur_Year & cur_mon
str_monthlyqry = DLookup("check_month", "tbl_check_month")
If DLookup("check_month", "tbl_check_month") < cur_date Then
CurrentDb.Execute ("DROP TABLE Tbl_Qry_UMS_Data")
CurrentDb.Execute "Qry_UMS_Data"
'MsgBox cur_date
str_monthlyqry = "Update tbl_check_month set tbl_check_month.check_month = '" & cur_date & "' "
DoCmd.SetWarnings False
DoCmd.RunSQL str_monthlyqry
DoCmd.SetWarnings True
End If
End Sub
--------------------------



I have to runa qry once in every month when the first user access the program. and I created a table with the date and if the current date greater than previously saved date it run the qry and then save the current date in the table. therefore only the next month it will run again.

thanks again for the help..

 

[CJ_London]

in a vba module - could be a form, could be a general module. All depends on how you want to run it.

Please don't hijack threads - this one is over 4 years old. Please start a new one and refer back to this thread if need be