The general approach adopted is to use the Windows Task scheduler to run a batch job every hour to transfer all the new data collected in the last
hour to the web host database. The general methodology for mirroring the databases is as follows:-
Getting the data
Extract the data for mirroring using a VBScript and save the data to a CSV file. The script used is shown below.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set dataRec = CreateObject("ADODB.Recordset")
Set Cn = CreateObject("ADODB.Connection")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data"&_
"Source = D:\dbase\dbase.mdb;Persist Security Info=False"
'select all data from the last 75 minutes and write to file Update.csv
sql = "SELECT * FROM Table WHERE Date_Time>=#"&DateAdd("n",-75,Now())&"#;"
Do UNTIL datarec.eof
For i = 1 to dataRec.Fields.Count-1
strCSV = strCSV & dataRec(i).value &","
The important features to note regarding this script is the use of the DataAdd function to get the latest data.
provides a date/time which is 75 minutes prior to the current time. I retrieve 75 minutes of data to provide a 15 minute overlap to avoid
any issues of timing diffence between the GHowSA data collection system and the task scheduler.
The first major problem now arises when trying to run the script. An error code 800A0E7A 'Provider cannot be found. It may not be properly
installed' is generated at line 13. This is because I was running the script on a 64-bit machine and there are no 64-bit version of the OLEDB
for Access! The solution is to run the script in 32-bit mode using SySWoW64 and cscript. To do this the script must be run from the command
We now have a script that can be scheduled to provide a CSV file of the data to be mirrored. In Task Scheduler set the Action
to c:\windows\SySWoW64\cscript with parameters d:\script\update.vbs (the full path and name of the VBScript file)
The update data must now be copied to the data to the web server using FTP. There a numerous scripts available on the internet
to do this but not all are equal. The scipt I use is derived from one from
'Today Guess What'which
is a VBScript FTP Download Sample and is also available here
An FTP upload script uses the windows FTP client to upload
the files using parameters defined in a batch file. The progress
of the tranfer plus any error messages are presented in a message box. These messages can be inhibited by setting the constant 'silent' to true.
However, even if messaging is inhibited if there are any problems encountered with the FTP upload then the output of the ftp process will be
saved in a file Update_?????.txt in the system default temp folder.
A very simple ftp upload script can be seen here
The file tranfer scripts is then appended to the data capture script.
Now that the update data is on the web host server we can process the data to add it to the database. This is done in the same VBScript by calling
a page on the server to process the data. This paqe parses the csv file to create sql insert statements to add the data to the database. It is important
to note that the table being updated must have constraints to prevent duplicate data being added. In my case the constraint requires the combination of
date/time and the device identity to be unique.
Setting constraints in MS access across multiple fields is fairly easy, once you know how its done,
since the method is not exactly intuitive. First of all make sure that all the fields that you intend to use in the constraint have the required
property set to 'Yes'. In the design view of the table go to 'View' and select 'Indexes' and add a name for the new index and select the first field
to be used in the constraint. Then move down one row and select the next field for the constraint (note the Index Name on this row must be left blank).
Continue adding fields as required up to a maximum of ten fields. When all the fields have been added highlight the row containing the Index Name
you entered at the beginning and In the Index Properties section set 'Unique' to yes. Finally save the table design.
Note you will not be able to save the table design if the table already contains data which does not meet the constraint requirements. If this
is the case with a table containing a large amount of data then create a design only copy of the table, add the contraints to the copy and then
append data from the original table to the copy table. This will add all data except that which does not meet the new constraint. Delete the
original table and then rename the copy to the original name.
The final task of the update page is to check if the update data spans midnight and if so it (a) updates the summary table with the summary data
for the the previous day and (b) deletes data from the detail table that is older than seven days.
The task is added to the Task Scheduler and set to run every hour. Unfortunately, every time the task runs a Command Prompt windows pops-up which,
whilst not a major issue, it can be irritating. To avoid this window pop up you can either change the user account to the system account which will not
open a window or you can from Run command line or Task Scheduler action use:
cmd.exe /c start /min c:\windows\SySWoW64\cscript d:\update\update.vbs ^& exit
to run the task minimised and close on completion. This latter option is preferred since the first method involves some security issue regarding
the running of scripts that access the internet under the system account.