Update data into Access table according to a schedule

[caiyan]

I need to create a database use access that the access can update the database based on a schedule, like daily.


For example, in the table there are several columns, and each column stands for a meter point, and in real world, each meter will have a reading every 5 mins. Now the database consists of all the readings till yesterday 11:55pm, by right, tomorrow for example at 8am, the database will update the readings till tonight 11:55pm, today's readings will be placed right after below yesterday 11:55pm row in the same table. The data source will be generated in CSV file every day 8am and put into a specific folder. What I need to do is to update them into this database.
As I'm new to MS access, I wonder anyone has any idea about how to implement this and thus I can have something to refer to study. Really thanks for your help.

[June7]

Review https://www.accessforums.net/reports...pdf-33990.html

[caiyan]

Hi 'June 7', thanks for your information.
As I'm very new to VBA as well, I'm not sure whether I understand your code in the right way:
Can I understand your code as that:
The system will automatic open the defined access file if the date fulfill the condition that "month=6,7,8,9" or "month=1,2,3,4,5,10,11,12 with day<8".
Thus may I know normally when will this process happen as you only define the month required. Like in June, how frequently will this be processed?
Or can I defined the hour also?
For example, if I changed your code to:
If (Hour(Date())=8) Then
And others will remain the same.
Then does it mean that the code will be run every day at 8 am to open the access file defined?

Another question is that can I perform other functions to the access automatically according to the defined time if I just add the functions into your code?
For example, I need to append the data from a csv file to the access, can I just implement this by writing the related function scripts right in your code?

[June7]

The VBscript runs according to conditions set in Windows ScheduledTasks (sorry, I said TaskManager in other thread and have fixed). I have setting for Weekly at 7:00 am. Set up task for the frequency you want. The VBScript opens db when conditions met. Then when the db does open, procedure behind form runs if conditions met. Modify code to suit your circumstances.

[caiyan]

Hi June7
So the period to run the script is defined in the Windows Scheduled Tasks.

By the way, as what I need is not open the bd, I only need to update the db with schedule without open it.

Originally I develop the code without scheduler to append the data from excel like this:
Private Sub cmdImportFile_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb
DoCmd.TransferSpreadsheet acImport, , "RawData", "C:\Documents and Settings\YuC\My Documents\Downloads\SIP.xls", True
End Sub

So if I want to implement the function above with scheduler, where should I implement the code?
If I derive the code into your Script, will it function as what I want:


' ProcessKillLocal.vbs
' Sample VBScript to kill a program
' Author Guy Thomas http://computerperformance.co.uk/
' Version 2.7 - December 2010' ------------------------ -------------------------------'
Option Explicit
Dim objWMIService, objProcess, colProcess
Dim strComputer, strProcessKill
strComputer = "."
strProcessKill = "'msaccess.exe'"
If (Month(Date()) >= 6 And Month(Date()) <= 9) Or ((Month(Date()) <= 5 Or Month(Date()) >= 10) And Day(Date()) < 8) Then

Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcess = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = " & strProcessKill )
For Each objProcess in colProcess
objProcess.Terminate()
Next
'WSCript.Echo "Just killed process " & strProcessKill & " on " & strComputer
'WScript.Quit
'End of WMI Example of a Kill Process

Dim objFSO, oShell
Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim dbs As DAO.Database
Set dbs = CurrentDb
DoCmd.TransferSpreadsheet acImport, , "RawData", "C:\Documents and Settings\YuC\My Documents\Downloads\SIP.xls", True

End If

[June7]

Did you understand that the VBScript is not code within Access? It is a file created with text editor (Notepad is good enough). Can't have the last three lines you show. I tried to figure out how to get VBA procedure to run by calling from VBScript and without opening db, couldn't.

You didn't change the If Then condition to fit your situation, whatever that might be.

[caiyan]

Oops, I thought the VBScript is work together with access. Thanks for your reminder.
Thus what I hope to implement is to update the db automatically without open access.
If it's not possible, or can I automatically run the VBA in the access when the db is opened, and then close the db after updating?

For the if then condition, as I just need to update the db everyday, can I just simply remove that condition?

[June7]

This is the sequence of actions:

1. ScheduledTasks runs the VBScript

2. VBScript opens Access db

3. Access db runs procedure behind default form Open event

Have conditions or don't have conditions, modify code to suit your situation.

I tried to make VBScript run procedure in VBA general module without opening Access and could not make it work, hence the VBScript just opens the db.

[caiyan]

Thanks June7, I think I understand your points now, thanks for your detials. I can build the function into the AutoExec so that it will run every time when the db is opened, and at the last part of that function add a quit function to close the db.
Thanks for your code and help, and I think I need to explore deeper into this area.