R – MS Access Freezes Up After Text Import

importms-accessvba

I have the following code to import a delimited file into an Access 2003 database:

Public Function importTextFile(sFile As String,  _
                                sTable As String, _
                                sSpecification As String)
On Error GoTo importTextFile_EH

' Validate arguments to see if the objects exist; if not, give a message 
' and exit
If Not FileExists(sFile) Then
    MsgBox "File " & sFile & " does not exist; import terminated.",  _
                                vbCritical + vbOKOnly,  _
                                "Error"
    Exit Function
End If

If Not TableExists(sTable) Then
    MsgBox "Table " & sTable & " does not exist; import terminated.",  _
                                vbCritical + vbOKOnly,  _
                                "Error"
    Exit Function
End If

If Not SpecExists(sSpecification) Then
    MsgBox "Import Specification " & sSpecification &  _
                                " does not exist; import terminated.",  _
                                vbCritical + vbOKOnly,  _
                                "Error"
    Exit Function
End If

' Display a warning to let the user cancel if this is run by mistake.
If vbYes = MsgBox("WARNING: This will delete all data currently in " &  _
                                sTable & "; do you wish to continue?",  _
                                vbExclamation + vbYesNo,  _
                                "Import Text File") Then
    DoCmd.Hourglass Yes

    ' Cleardown the data in the table.
    DoCmd.Echo Yes, "Deleting data in " & sTable & " table..."
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE " & sTable & ".* FROM " & sTable & ";"
    DoCmd.SetWarnings True

    ' Import the text file into the table. 
    DoCmd.TransferText acImportDelim, sSpecification, sTable, sFile

    DoCmd.Echo Yes, "Import complete"
    DoCmd.Hourglass No
Else
    DoCmd.Echo Yes, "Import cancelled."
End If
Exit Function

importTextFile_EH:
    Debug.Print Err.Number & "-" & Err.Description

End Function

I can call this function from a Macro using RunCode with argument Function Name valued as 

importTextFile (Application.CurrentProject.Path & "\" &  _
                                "batch_results.txt",  _
                                "BatchEngineResults", _
                                "specResults")

and it works fine. I can also call it from the Immediate window and it works without any problems.

However, if I call the function from a form (from the Click event of a command button), then Access freezes up. It looks like the import completes (the Import progress bar in the Access Database window status bar shows the import running and finishing), but then Access becomes unresponsive, both the form and the Access database window. Task Manager doesn't indicate that Access is hung (the task status is "Running") and I can close Access via the Close button on the title bar. When I open the database again, my table has all the data from the text file, so the import did work.

I've also tried calling the macro from the form, but get the same results.

Anyone have any ideas?

UPDATE: I tried a call to MsgBox after I call the function:

importTextFile (Application.CurrentProject.Path & "\" &  _
                                "batch_results.txt",  _
                                "BatchEngineResults",  _
                                "specResults") 
MsgBox "After Import"

A message box appears, and is responsive. When I dismiss it, Access freezes up as before. Do you think this means I may have an issue somewhere else with the form, and not with this function?

Best Answer

You have Yes and No after DoCmd.[something] where you should have True and False. In addition to changing those, please consider adding Option Explicit at the beginning of your module.

I think Yes is being treated as an empty variable, so when evaluated in a boolean context has the same result as False. For example, the following code (without Option Explicit) will print False in the Immediate Window:

Public Sub evaluateYes()
    If Yes Then
        Debug.Print "True"
    Else
        Debug.Print "False"
    End If
End Sub

With Option Explicit, Access will complain about a compile error, "Variable not defined", and highlight the word Yes.

Update: Try commenting out your DoCmd.Echo statements. Does Access still freeze when your code doesn't call DoCmd.Echo?

In reply to your comment, I have no idea why your code works when called from a macro or the Immediate Window, but not when called from a button click on a form.

Related Topic