Data Upload Controls

Data Upload Controls.

In some projects, we need to regularly import data from external sources such as dBase, Excel, or flat files like CSV and text. These external files can remain linked to the project, allowing their data to be added to a local Microsoft Access table for reporting purposes.

For example, assume that we have an MS Access application that generates monthly business profitability reports. To prepare these reports, we need to upload new data each month from a LAN location, where the file is updated and replaced from a remote source in one of the supported formats.

Our Application has a mechanism to identify when the existing linked file on the LAN Server is replaced with a new file having fresh data. When a new data file is introduced on the Server and replaces the old one, the System detects the new data and enables the CommandButton. This CommandButton is disabled after uploading the data and remains disabled till new external data file overwrites the old one. 

When the MS Access application opens, it compares the previously stored file parameters with those of the current file on the server. If the parameters match, the application assumes that the data has already been uploaded and keeps the DataUpload Command Button disabled. If the parameters do not match, it assumes that new data is available and enables the DataUpload Command Button to allow the upload process.

So, how do we detect new data in the attached file? Depending on the file type, different approaches can be used. One common method is to check the continuity of a control value, such as an Invoice Number, Last Receipt Date, or any other unique identifier that reliably distinguishes new records. These control values from the last uploaded data can be compared with the corresponding values in the attached file. If the values match, we can assume that the data has already been uploaded; otherwise, we can proceed to upload the new records.

To perform this verification, you can design a few queries to extract and filter these key values from both sources, then use a VBA routine to compare them and control the next steps in the upload process.

However, I use a simpler method to check for the new data in the attached file. Before explaining that, we need to consider a few important factors.

The Access Application's back-end Database and the upload data source both must be on the LAN Server. Only one authorized Front-End Database user is permitted to execute the upload process.

Taking these considerations into account, we must design a reliable, controlled method to ensure that data is uploaded correctly and prevent duplication.

External Data Source Files.

I have several applications that upload data from various sources — including IBM AS400 systems, dBase, Excel, and even AS400 report spool files. Over time, I’ve experimented with different methods to detect new information in these files, using queries that compare control data from internal database tables and linked tables.

You might be wondering how I handle the AS400 report spool files, which often have hundreds of pages saved directly to the LAN by the EDP department. These files cannot be linked directly to the database because they don’t follow a proper table structure — except for the detail lines that contain the actual data.

I have developed VBA procedures that will read the spool file line by line and discard unwanted lines, headers, footers, underlines, blank lines, etc., and take only the data lines, cut into text fields in a Table initially before converting each field value into its respective data types and writing it out into a new table.

But the question remains: how can we track the presence of a new report spool file that cannot be directly attached to the database? The solution is quite simple. At the end of each upload operation, I create a control file by copying the first 50 lines of the current spool file. Whenever the application is opened, a small routine compares the first 50 lines of both the current spool file and the control file. If no differences are found, it means the data has already been uploaded into the system. If any variation is detected, the system recognizes it as a new file and prepares to upload the fresh data.

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

After experimenting with several methods for different file types, I realized the need for a simple, universal approach that could work for all kinds of files—whether attached to the system or not. I eventually developed such a method, which I’m sharing below for your use, should you find it helpful.

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:

Right-click to open Large Image in New Tab/Window.

When the file contents are uploaded, we record some basic information about the attached file—such as its size (in bytes) and its last modified date and time. In addition, we store the name of the user (if Microsoft Access security is implemented) authorized to run the upload process, along with the date and time of the last upload event.

We can read the attached file size in bytes using the Function: FileLen(PathName), and the File's last modified Date and Time can be obtained with the Function FileDateTime(PathName). After the data upload, these values can be updated in the table above to cross-check the external file to check the presence of new data. If needed, we can set the attached file's Read-Only attribute ON using 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 opened, and cross-check the file size and the File Date/Time recorded in the table with the attached file's attributes. If they are different, then new data has arrived and enabled the Upload CommandButton; the new data can be uploaded.

However, when the application is in open state, and the attached file is replaced by the provider with a new one, the Upload button will remain disabled because the status-checking routine runs only when the main switchboard is opened. Instead of requiring the user to close and reopen the application, a standard but inconvenient procedure, we can add another command button labeled Refresh. When the user clicks this button, the program can recheck the file attributes and enable the Upload button if a new file is detected.

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

Alternatively, we can run a Timer-Interval Subroutine (at a 1-hour or longer interval) to check the presence of a new source file when the Access Application is active.

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 the uploading authorisation is assigned to a particular User, then the Current User's User ID (retrieved with the function CurrentUser()) can also 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