Programming & Security

Posted: 12 Jan 2010 07:47 AM PST

In some Projects we have to upload data regularly from external data sources like dBase, Excel, flat files like CSV, Text and so on. The external file can remain linked to the Project and the data from them can be added into the local MS-Access Table for preparing Reports

For example, let us say we have an MS-Access Application that prepares monthly Reports on Profitability of Businesses and to do this we must upload raw data on Revenue and Expenses received every month in one of the file Formats mentioned above. If our Application is designed in a such way that when the existing linked file is replaced with new data file with the same name then the Application must be able to identify the presence of new data in the attached file and allow the User to upload the new records into the master file. But, before this action is allowed it become our responsibility as a developer to see that the data once uploaded is not allowed into the System again.

To do this we must install a procedure that will check for the presence of new data in the attached File. If found then enable a Command Button that will allow the User to click and upload the new data into the System. Otherwise keep the Command Button disabled till such time fresh data is detected in the attached file.

So, how do we detect the presence of fresh data in the attached file? Depending on the type of file we can use different approaches to determine the presence of fresh data in the attached file by checking continuity of some Control Number like Invoice Number or the Last Receipt Date, or any other set of unique values that you can depend on from the last uploaded data, and compare them with those Values of the attached file. If the compared values on both files are same then we can assume that the attached file contents are already uploaded earlier, otherwise prepare to upload fresh data. For this kind of checking we may prepare few Queries to filter the set of values from both files and read those values into a VBA routine to compare and control the flow of action.

But, I use a simple method to check the presence of new data in the attached file. Before going into that we have to consider few other issues. If the attached external file is on the local drive of each User and all of them are allowed to run the upload action then it is difficult to keep control of this action and the data can go wrong as well. If the attached file is kept on Server and only one authorized User is allowed to execute the Upload action then there can be some control over the procedure. Depending on all these considerations we have to device a method to upload the data correctly into the database.

I have several Applications that upload data from AS400, dbase, Excel and AS400 Report Spool Files too. I have tried out several methods to detect the presence of fresh information in those files using Queries with control data from tables within the Database and from linked tables. Perhaps, you may wonder what I did for AS400 Report Spool Files. This File cannot be kept attached with the database because it doesn't have the correct data table format except the Detail Lines.

I have developed programs that will read the spool file line by line and discard unwanted lines like Header, Footer, underlines, blank lines etc. and to take only data lines cut into text fields in a Table initially before converting each field values into their respective data types and write it out into a new table.

But, the question remains as how do I keep track of the presence of new Report Spool File that cannot be kept attached to the Database at all. It is a simple trick. At the end of the upload action of the current file I will make a copy of first 50 lines of the Spool File and create a second control file. Whenever the Application is open by the User a program is run to open both files and do a line by line comparison. If there is no difference in the first 50 lines of both files then the data was already uploaded into the System otherwise the System is prepared to upload fresh data from new file.

After trying several methods with different file types I thought we need a simple method that should work for all kind of files (attached to the system or not) and created one, which is presented below for your use, if needed.

We need a small table with the following Fields:

Field Name	Data Type	Field Size
FileLength	Long Integer
FileDateTime	Date/Time
UserName	Text	25
UploadDate	Date/Time
FilePath	Text	255

The sample table in Datasheet View:

The idea works something like this. When we upload the file contents we will save some basic information of the attached file, like File-Size in number of bytes and the last modified date and time of the file. Besides that, the name of the User (if the database is implemented with Microsoft Access Security) who is authorized to run the upload action and the date of last upload event took place.

We can read the attached File-Size in bytes with the Function: FileLen(PathName) and the File's last modified Date and Time can be obtained with the Function FileDateTime(PathName). After the upload action these values can be updated into the above table to cross-check with the values of the external file to determine the presence of new data. If needed we can set the attached file's Read-Only attribute ON with the Function SetAttr(PathName,vbReadOnly) so that the file can be protected from inadvertent changes. It can be reset to Normal with SetAttr(PathName,vbNormal).

A program must be run immediately after the Main Switchboard Form is open and cross check the File-size and the File Date/Time recorded in the table with the attached file's attributes and if found different then we can be sure that new data have arrived and enable the Command Button so that the User can click and upload new data.

But, if the User kept the Application open and replaced the attached file with new one then the Button will remain disabled because the status checking program runs only when the Main Switchboard opens. Instead of asking the user to close and open the Application again, as a standard procedure in these circumstances, we can create another Command Button with Refresh Caption so that when the User clicks on this Button we can run the above procedure and enable the Upload Button if the file attributes indicates the presence of new file.

A sample VBA Routine is given below that reads the information from the table and cross checks with the attributes of the attached file and Enable/Disable the Upload Command Button.

 
Public Function UploadControl(ByVal frmName As String)
'------------------------------------------------------
'Author   : a.p.r. pillai
'Date     : January-2010
'Remarks  : Data Upload control Routine
'         : All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------
Dim frm As Form, lnglastFileSize, dtlastModified, txtFilePath
Dim lngExternalFileSize, dtExternalModified, authUser
Dim tblControl As String, cmdCtrl As CommandButton

tblControl = "UploadCtrl"
authUser = "LizzaMinnelli"
Set frm = Forms(frmName)
Set cmdCtrl = frm.Controls("cmdUpload")

'Read last recorded information from the Control Table
lnglastFileSize = DLookup("FileLen", tblControl)
dtlastModified = DLookup("FileDateTime", tblControl)
txtFilePath = DLookup("FilePath", tblControl)

'Get the External File information
lngExternalFileSize = FileLen(txtFilePath)
dtExternalModified = FileDateTime(txtFilePath)

If (lngExternalFileSize <> lnglastFileSize) And (dtlastModified <> dtExternalModified) Then
    If CurrentUser = authUser Then
        cmdCtrl.Enabled = True
    Else
        cmdCtrl.Enabled = False
    End If
End If

End Function

The Main SwitchBoard, that has a Command Button with the name cmdUpload, should call the above Program through the Form_Current() Event Procedure of the Form passing the Form Name as Parameter like the following example:


Private Sub Form_Current()
    UploadControl Me.Name
End Sub

If Uploading authority is assigned to a particular User then the Current User's UserID (retrieved with the function CurrentUser) also can be checked with the UserName Field Value before enabling the Command Button cmdUpload..