Data Upload Controls

Introduction

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 to the local MS-Access Table for preparing Reports

For example, we have an MS-Access Application that prepares monthly Reports, on the Profitability of Businesses, and to do that we must upload new data from the LAN, replaced every month from a remote location, in one of the file-formats mentioned above. 

If our Application is designed in such a way that when the existing linked file is replaced with a new one on a LAN, with the same name, then the Project 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 implemented it becomes our responsibility as a developer to see that the data once uploaded is not duplicated in the System.

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 the 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 in both files are the 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 a 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 a 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 the 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 devise a method to upload the data correctly into the database.

External Data Source Files.

I have several Applications that upload data from the IBM AS400 System, 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 linked tables. Perhaps, you may wonder what I did for AS400 Report Spool File with hundreds of Pages saved directly on the LAN by the EDP Department. This File cannot be kept attached to the database because it doesn't have the correct data table format except for 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 the data lines cut into text fields in a Table initially before converting each field value into their respective data types and write it out into a new table.

But, the question remains as to how to keep track of the presence of the 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 the 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 the new file.

A Common Simple Method is suitable for all Types of Files.

After trying several methods with different file types I thought we needed a simple method that should work for all kinds 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 about the attached file, like File-Size in a 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 the 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 in 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 if found different then we can be sure that new data have arrived and enabled 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 a 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 indicate the presence of a 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.

The UploadControl() Function Code.

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 Switch Board, which 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 User ID (retrieved with the function CurrentUser()) also can be checked with the UserName Field Value before enabling the Command Button cmdUpload.

• Run Slideshow when Form is idle
• Filter Function Output in ListBox-2
• Filter Function Output in ListBox
• Dynamic List Box Contents
• Office Assistant and MsgBox Menus-3