Can Access Run A Query Automatically by Date? 2

[quest4]

I have a delete query which I manually run on 01/01 of each year. Is there any way to make it run the first time I open the form for the first time in a new year? Thanks alot in advance for anyone who helps.

 

[THoey]

You could have a static table that had the last run date of the delete query. Have the first form of your application check to see if the year had changed (compare if YEAR(DATE()) <> YEAR(MyTable.LastRunDate). If the years do not match, run the query.

Hope that helps... Terry M. Hoey

 

[Databaseguy]

Create a table called tblDate. Put one record in it, from at least 2 years ago.(just to prevent an empty recordset the first time the code runs)

Public Function RunOncez()
Dim dbs As Database
Dim rst As Recordset
Dim strSQL
Set dbs = CurrentDb
strSQL = "SELECT Max(Format([date]," & Chr(34) & "yyyy" & Chr(34) & ")) AS Year FROM tblDate"
Set rst = dbs.OpenRecordset(strSQL)
If Format(Now(), &quot;yyyy&quot;) <> rst.Fields(0).Value Then
	With rst 	
           .AddNew
           .Fields(0).Value = Now()
           .Update
    	End With
    	DoCmd.OpenQuery(“Your Query”) 
Else
End If
End Function

Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[quest4]

DataBaseGuy, that looks good and I think I understand it, god that is scary, but where do I put it? Would I put it in form property sheet in OnOpen or what? Thanks a lot for the help.

 

[Databaseguy]

Call the function from your Autoexec macro. Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[quest4]

Forgive me, but I'm still fairly new that Access. I am learning alot lately, biut there is still a lot more I need to now. So could you please tell me where is the autoexec macro hiding? Thanks for the response.

 

[quest4]

Ok, I found AutoExec in help. Would I first put the above procedure in a module and the in then macro use Run Module? Thanks for your response.

 

[quest4]

Two quick dumb questions, in addition to the aboove info, I would replace your tblDate with my real table wouldn't I? Can I run a second query right after this query is done? To make sure that I run this right, I would have my data from 2001 and my data from 2000 in the table, correct? Then when it turns 2002, I can wipe out the 2000 data? Thanks for all of your help.

 

[Databaseguy]

OK, Sorry to take so long. Had to write some code for the 9 to Fivers here real quick.

Create a new macro. In the macro, under action, choose run code. Down near the bottom, you should see a function name argument. Type RunOncez()in there. Close the macro, and when prompted to save it, name it autoexec.

Yes, you will create a new module, and put the procedure in there. You can name the module whatever you want, but don't duplicate names or use reserved names (table, query, autoexec, etc). Actually, Module1 (the default) will suffice.

No, you need to create tblDate. That is what keeps track of when the query last ran. You can manipualte (i.e. delete) the data if you prefer. Just remember to keep the max date (right now, 2001) greater than now. Otherwise, the code will run. In other words, if you have 1/1/1999 and 1/1/2000 in tblDate, the code will run.If you have 1/1/2001, it won't until 1/1/2001.

Have Fun !!!! Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[quest4]

Thanks, I am a little foggy on two things,tblDates, would that be a table with two columns, one for ID and one for dates? The other is tblAttendende, my table with the data, how many years of data do I keep in it? I was think 2000 & 2001 and when it turned to 2002 deleting the 2000 data, is that OK? Also can I run a second query after this one? Something like a delete query to get rid of the 2000 data? Thanks for the help again, I have a feel that with this one I had better be 100% correct or else.

 

[Databaseguy]

Nope. tblDate will have one column. Date/Time Format.Remember, this is the table that tells Access whether or not to run the function.

tblAttendee has no bearing on this function.

Yeah, you can run a second query. Just add another DoCmd next to the first one. I.E:
DoCmd.OpenQuery(“Your Query 1”)
DoCmd.OpenQuery(“Your Query 2”)

Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[quest4]

Great, thanks alot I learned a little more about this stuff. Now if I can only get an answer to other question with the WHERE clause, I'll have it made in the shade. Thanks again.

 

[BoxHead]

Quest4,

Would suggest including an automatic export of the data to an archive file before deleting if you don't already have it as part of the delete procedure.

When you were doing this manually, you could check the date. Your computer only knows what its told. Hate to see your system time get reset to its manufacture date by a crash, virus, worm, etc and have it delete your data prematurely.

 

[quest4]

Thanks, but I already have plans to start thinking about the archiving, I have never done it, but I will soon be learning. We back up our servers everynight, and that is where this little Access database will reside, after it has been tested. I really appreachiate your thoughts and I will be taking your advice to heart.

 

[quest4]

I just tried to open my Access database and it the debugger came on. It high lited the public fuction and the Dim dbs as Database lines. Thanks you to anyone who can help me on this one.

 

[Databaseguy]

That was written for 97. Sounds like you've got 2000. From the VBE, go to tools/references, check DAO 3.51 and DAO 3.6 and move them to near the top of the list using the arrows.

Or, use this code, which may need a little tweaking:

Dim cnn as connection
Dim rst as New ADODB.Recordset
Dim strSQL
Set cnn = CurrentProject.Connection
Cnn Open."Provider = Microsoft.Jet.OLEDB 4.0; Source = "YourDatabase.mdb" 
strSQL = "SELECT Max(Format([date]," & Chr(34) & "yyyy" & Chr(34) & ")) AS Year FROM tblDate"
rst.Open strSQL
If Format(Now(), &quot;yyyy&quot;) <> rst.Fields(0).Value Then
With rst 
.AddNew
.Fields(0).Value = Now()
.Update
End With
DoCmd.OpenQuery(“Your Query”) 
Else
End If
End Function

Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[quest4]

Actually, someone else helped me out with this, he had me turn on the reference for DAO 3.6 and increase its' priority and now it is working. Thanks you for all of your help, you are really goos at this, hopefully I will be half as good in the future. Now all I have to do is wait and hope that someone will show how in insert that line in my last thread, Problem Query Need Help, and I can finally start testing this little monster.