Automate Running Marco 1

[Londa1130]

I have created five tables in my Access database that has information being fed to it through linking of tables from an ODBC (Slim Application).Now that the tables are created, I have a Marco that runs five (5) append queries that updates any data that has been added or changes in the ODBC system. Is there anyway to automate when the Marco runs instead of me having to manually run it? Also, I have to log into the Slim application with username and password, can that be stored some kind of way? Any help would be greatly appreciated. Thanks!

On a side note can anyway recommend a good Access Programming book for beginner?

 

[scriverb]

I have designed in the past just such a system. On an ODBC "RDBS" large data files were updated nightly with the previous days data. That process usually was completed by 6:00 am each day.

I created a database that I logged into and left running overnight. I set the Timer Interval on the main form to check every half hour to see if it was after 6:00 am. I also kept an internal file of the last time/date that the update was completed. I also checked that to see if we had completed the update for this day.

When these checks indicated we needed to do the update I executed the queries necessary to complete the process. After completion of the process I updated the table with the Date/Time of the last update. Now the timerevent would not trigger until tomorrows date after 6:00 am.

Now this worked fine execept when the ODBC tables weren't updated properly due to operational problems. So, we added a file at the ODBC location that gets updated with todays date that could get checked first to see that the data updates at that level were completed. This solved all of the issues.

You can set this application to be run only through a windows scheduler event. This can be setup to run the database at 6:00 am. After the process is completed in your application you can then perform an Application.Quit and have the database shutdown.

Hope this helps you in your systems design.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Londa1130]

How do I get the Marco to run autmatically? Can you provide me with some sample code?

 

[scriverb]

I will try to keep this simple. I am assuming this database application is designed in a Client/Server Front-End(FE) and Back-End(BE) style.

1. Create a table in your BE database(Server).

Table Name:  [b]tblDBParameters[/b]
Fields:
RecCounter          AutoNumber Primary Index
LastDownloadDate    Date/Time
DownloadInProgress  Boolean    Default:  False
Download_StartTime  Date/Time  Format: Medium Time
                               InputMask: 09:00\ >LL;0;_

2. Enter a single record in this table with the following Data:

06/11/2004, False, 6:00 AM

3. Open the FE database and establish a link to this table in the BE database.

4. Create a form that will be opened upon startup of this database. This is just a Splash form that will sit on the screen for display purposes. Put a command button on it that can close the application. Code: Application.Quit

5. Put the following into the code module for this form:

[red]Option Compare Database
Option Explicit[/red]
[green][b]'DO NOT duplication the above lines in your code module[/b][/green]
Dim vTimeToRun As Date
Dim vDownloadInProgress As Boolean
Dim vLastDownloadDate As Date

[b]Private Sub Form_Load()[/b]
vTimeToRun = DLookup("Download_StartTime", "tblDBParameters")
vDownloadInProgress = DLookup("DownloadInProgress", "tblDBParameters")
vLastDownloadDate = DLookup("LastDownload_Date", "tblDBParameters")
Me!TimerInterval = 900000   [green]'Set to 15 minute[/green] intervals
If vDownloadInProgress Then
    MsgBox "Download in Progess.  Try again in 5 minutes."
    Application.Quit
End If
End Sub



[b]Private Sub Form_Timer()[/b]
If Time() > (vTimeToRun - 15) And Me.TimerInterval = 900000 Then
    Me.TimerInterval = 60000
End If
If vLastDownloadDate < Date And Time() > vTimeToRun And vDownloadInProgress = False Then
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblDBParameters", dbOpenDynaset)
    rs.MoveFirst
    rs.Edit
    rs("DownloadInProgress") = True
    vDownloadInProgress = True
    rs.Update
    rs.Close

    [green][b]'put your code to run reports or queries here[/b][/green]
    
    Set rs = db.OpenRecordset("tblDBParameters", dbOpenDynaset)
    rs.MoveFirst
    rs.Edit
    rs("DownloadInProgress") = False
    rs("LastDownload_Date") = Date
    vLastDownloadDate = Date
    vDownloadInProgress = False
    rs.Update
    rs.Close
    db.Close
    Me.TimerInterval = 900000
Else
    'download completed for today.
End If
End Sub

This application is now ready to run. The tblDBParameters fields control the actions being taken. The time is variable and should be set to the time that you want your action queries to be run. The TimerInterval is set to check the time every 15 minutes. At your target time minus 15 minutes the code changes the timer interval to 1 minute. When the System Time is greater than the time set to run, the application then locks up the backend so that no one can logon. This protects your datafiles while you are performing the action queries. The variable vDownloadInProgress and the corresponding field in the tblDBParameters is monitored by anyone else logging on and if set to True then it logs them off immediately. After the action queries are run the fields in tblDBParameters is updated with the todays date and the flag field is set to false. The TimerInterval is reset to 15 minutes and the application goes back into semi-sleep mode waiting for its next time to execute, which will be tomorrow morning.

Let me know if there are any questions that you have about this process. Good luck.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Zion7]

good VBA book for beginners?
Beginning Access 2003 VBA, by WROX Press.
This appears to be the most popular.
Unfortunately, the edition I bought "2002" does not have the appendix, the "Introduction claimed it did.
"2000" does & so does 2003? ...just my luck(I didn't appreciate the significance, at the time of purchase).
A little daunting at first, requires a few reads on some chapters.
"Access VBA Programming for the absolute beginner" by Premier Press, author Michael Vine. was definatley the catalyst in my case, to getting a grips with it, then, I remained with Beginning Access 2003 VBA( a must in all VBA programmers, libraries.)
Hope this helps, good Luck!