Importing text files - Vb/Access

Question

What I am trying to do is map my button (import button on my form) to import text files (the text file would actually be on a network drive). These text files are fixed columns. I am confused on how to merge a form and module to work together. How does the button on the form, call out this module for execution? Also, if there is a more efficient way to import these fixed text files, I would appreciate it.

I currently have the following VBA code setup for my form (will be used to Import text files into my Access database):

Private Sub cmdImport_Click()

On Error GoTo Click_Err

    reportDate = Format(txtReportDate, "YYMMDD")
    reportGenDate = Format(textReportDate, "YYYYMMDD")
    rDate = txtReportDate

    If Nz(txtReportDate, "") = "" Then
        MsgBox "NOTICE! Please enter the Report Month you wish to Import."
        Exit Sub
    End If

    DoCmd.Hourglass True
    DoCmd.SetWarnings False

    ImportAll

    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    MsgBox "Finished Importing!"
    DoCmd.OpenQuery "query_Files_Loaded_CE", acViewNormal, acReadOnly

click_Exit:
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Exit Sub

Click_Err:
    DoCmd.Hourglass False
    MsgBox "Error Detected: " & Err.Number & " - " & Err.Description, vbCritical, "Error"
    Resume click_Exit
End Sub

For my module (please excuse the notes):

    Option Compare Database
Public reportDate As String
Public reportGenDate As String
Public rDate As Date

    Public Function Import2010()
    'Used to import a date range
    Dim funcDate As Date '
    funcDate = #2/1/2016#
    reportDate = Format(funcDate, "YYMM")
    rDate = funcDate

    'Basically Do While is a loop so what your doing here as long as the value of the date does not EQUAL 3/1/2016
    'excute the nexxt line of code other wise exit this loop
    Do While funcDate <> #3/1/2016#
        DoCmd.SetWarnings False
        'ImportAll
        ImportFile "H3561"
        'Msg Box reportDate
        funcDate = DateAdd("m", 1, funcDate)
        reportDate = Format(funcDate, "YYMM")
        rDate = funcDate
    Loop

    DoCmd.SetWarnings True

End Function

Public Function ImportAll() ' Import button on FrmIMport

    'A recordset is a selection of records from a table or query.
    'Dim is short for the word Dimension and it allows you to declare variable names and their type.
    'When you read data from the database in VBA, the result will be in a recordset (with the exception of scalar data).
    Dim rs As Recordset
    Dim sql As String

    'This code loops through the recordset of all contracts and import files, as in it looks for
    'Specific value based off a specific condition.

    sql = "SELECT DISTINCT Contract FROM Contract_CE"
    Set rs = CurrentDb.OpenRecordset(sql)
    rs.MoveLast 'This method is used to move to the last record in a Recordset object. It also makes the last record the current record.
    rs.MoveFirst 'This method is used to move to the first record in a Recordset object. It also makes the first record the current record.
    If rs.RecordCount > 0 Then
        Do While rs.EOF = False
            ImportFile rs!contract
            rs.MoveNext 'This method is used to move to the next record in a Recordset object. It also makes the "next" record the current record.
        Loop
    End If

End Function

Public Function ImportFile(contract As String)

    Dim filepath As String
    Dim tempPath As String
    Dim zipFile As String

    'Set paths
    filepath = "\\XXXXX\XXXXX\XXXXX\XXXXXXX"
   'tempPath = 
    tempPath = "\\XXXXXX\XXXXX\XXXXX\XX"

    'Find the file
    zipFile = GetFile(filepath)

    'check if file exists
    If zipFile = "" Then
        'DoCmd.Hourglass False
        'MsgBox contract & " " & reportDate & " File could not be located."
        'DoCmd.Hourglass True
        LogFail (contract)
        Exit Function
    End If

    'Clearing out existing Contract/ReportDate data from Table
    DeleteContract (contract)

    'Delete all files in temp folder
    DeleteAllFiles (tempPath)

    'UnzipFile txt to temp folder
    UnZip filepath & zipFile, tempPath

    'Get txt file namee
    txtFile = Replace(zipFile, ".zip", ".txt")

    DoEvents
    Sleep 10000 'wait for file to unzip

    'The TransferText method is used to import/export text between the current Access database or Access project and a text file located
    'externally to your database. You can also use this command to link to data in a text file. Additionally, can import from, export to, and link to a table in an HTML file.
    'Importing txt file
    'Depcreated - Alec Johnson - 5/12/2016 - Created new import spec
    'DoCMD.TransferText acImportFixed, "ImportSpec_COMPRPT", tempPath & txtfile, False
    DoCmd.TransferText acImportFixed, "COMPRPT_2016", "COMPRPT_CE", filepath & txtFile, False  '<--does path go here?

    'Update FileName
    UpdateFileName (zipFile)

    'Delete txt file from location
    DeleteAllFiles (tempPath)

    'Delete any Null records added to main table
    DeleteNulls

    'Log to table if successful
    LogSuccess (contract)

End Function

Public Function DeleteAllFiles(path As String)

'Delete all files in this folder
On Error Resume Next
Kill path & "*.*"
End Function

Function UnZip(filename As String, destinationPath As String)
'FileSystemObject also called as FSO, provides an easy object based model to access computer’s file system.
'You simply have to create an instance of FileSystemObject in VBA and then you can generate files, read files, delete files,
'iterate though folders and do many other operations on your computer’s file system.


    'Unzip file (s) to destination
    Dim app As Object
    Dim zipFile As Variant, unzipTo As Variant

    zipFile = filename
    unzipTo = destinationPath

    Set FSO = CreateObject("Scripting.FileSystemObject")

    If Not FSO.FolderExists(unzipTo) Then
        FSO.CreateFolder (unzipTo)
    End If

    'If you want to extract only file you can use this:
    'oApp.Namespace(FileNameFolder).CopyHere _
    'oApp.Namespace(Fname).items.items("test.txt")

    Set oApp = CreateObject("Shell.Application")

    oApp.Namespace(unzipTo).CopyHere oApp.Namespace(zipFile).Items

    Set FSO = Nothing

End Function

Public Function GetFile(filepath As String) As String

    Dim fileNamePart As String
    Dim fCheck

    fileNamePart = "COMPRPT_" + reportDate
    fCheck = ""
    fFound = ""

    Set oFolder = CreateObject("scripting.filesystemobject").GetFolder(filepath)
    For Each aFile In oFolder.Files
        Set fCheck = aFile
        If InStr(fCheck.Name, fileNamePart) Then
            Set fFound = aFile
            End If
        Next

        If fFound = "" Then
            GetFile = ""
        Else
            GetFile = fFound.Name
        End If

End Function

Public Function DeleteContract(contract As String)

    Dim sql As String
    sql = "Delete * FROM COMPRPT WHERE ContractNumber = '" & contract & "' AND ReportGenerationDate = '" & reportGenDate & "'"
    DoCmd.RunSQL sql
End Function

Public Function LogSuccess(contract As String)

    Dim sql As String
    sql = "INSERT INTO FilesLoaded (Contract, ReportDate, Loaded) VALUES ('" & contract & "', #" & rDate & "#, -1)"
    DoCmd.RunSQL sql

End Function


Public Function DeleteNulls()

    Dim sql As String
    sql = "DELETE * FROM COMPRPT WHERE ContractNumber Is Null"
    DoCmd.RunSQL sql


End Function

Public Function lksjdlaskjd()

    ImportFile "H0351", #4/1/2009#
End Function

Here is an example of a text file:

Answer 1

If I understand it correctly, your problem lies here:

DoCmd.TransferText acImportFixed, "COMPRPT_2016", "COMPRPT_CE", filepath & txtFile, False  '<--does path go here?

But you have unzipped to tempPath, so that should be

DoCmd.TransferText acImportFixed, "COMPRPT_2016", "COMPRPT_CE", tempPath & txtFile, False

Working with network files is generally slower than with local files, so I would make tempPath a local path.

Edit: Note that to make tempPath & txtFile work, tempPath must end with a \:
tempPath = "C:\XXXXXX\XXXXX\XXXXX\XX\"

---

Additional problems with your code:

1 - First and foremost, use Option Explicit, see this question for details.

You have multiple undeclared or misspelled variables, e.g. fFound, and oApp vs. app.

2 - This is an error just waiting to happen:

reportDate = Format(txtReportDate, "YYMMDD")
reportGenDate = Format(textReportDate, "YYYYMMDD")

Name the second textbox txtReportGenDate, not textReportDate.

3 - In ImportAll(), all this isn't needed, since you don't use the RecordCount:

rs.MoveLast 
rs.MoveFirst 
If rs.RecordCount > 0 Then

4 - This is wrong syntax:

DeleteContract (contract)

It works for a single argument, but will fail for subs with >1 parameters.

Use

DeleteContract contract

or

Call DeleteContract(contract)

or

retVal = DeleteContract(contract)

Answer 2

I am confused on how to merge a form and module to work together. How does the button on the form, call out this module for execution?

Objects and procedures can be considered public or private. For example: -

Private Sub Test
    Msgbox "Hello World!"
End Sub

Is private, this means only its parent can call upon it. To elaborate on this, Lets create two modules Module1 and Module2 and place our private sub Test in Module1.

Also in Module1 we another private procedure: -

Private Sub Test2
    Msgbox "Back at ya"
End Sub

Module1 is the parent of Test and Test2, as they have the same parent they can run each other: -

Private Sub Test
    Msgbox "Hello World!"
    Test2 'This will run the Test2 procedure
End Sub

Module2 can not run any of them because it has no view f them, its not involved.

Now if we change Test to be public (Public Sub Test), Module2 will be able to see it as it has been exposed.

In Module2 we have: -

Public Sub Test3
    Module1.Test    'This will run as it is public
    Module1.Test2   'This will fail as it is private
End Sub

There is also this way too call them from Module two: -

Public Sub Test3
    Test    'This will run as it is public
    Test2   'This will fail as it is private
End Sub

This is not explicit though and can cause error and confusion, you can have a procedure in Module2 that is also called Test, how would you know which test Test3 is running? To be safe you explicit write its location as Module1.Test.