TransferText Error - "The Microsoft Access database engine could not find the..."

[tgroneck]

TransferText Error - "The Microsoft Access database engine could not find the..."

I'm having an issue with the following code. I've attempted to build in the ability to select a zip file for import. The code references a function that successfully unzips the folder and loops over each subsequent file. My issue is that on the import piece, I get the following error: "The Microsoft Access database engine could not find the object '20130411012402_BKCHKY_Unusual_Lo.tsv.txt'. Make sure...".

I can import the files themselves using the same module once they are extracted but Access is having an issue with doing this all in one go.

If f.SelectedItems.Count > 0 Then
    importStart = Now()
    For Each file In f.SelectedItems
        Set objFileSystem = CreateObject("Scripting.FileSystemObject")
        Set objFile = objFileSystem.getfile(file)
        intExtPosition = InStr(objFile.Name, ".")
        If intExtPosition > 0 Then
            If Right(objFile.Name, Len(objFile.Name) - intExtPosition) = "txt" Then
                strFileCopy = objFile.Name
                GoTo textFile
            ElseIf Right(objFile.Name, Len(objFile.Name) - intExtPosition) = "zip" Then
                parentFolder = objFileSystem.GetParentFolderName(objFile)
                Call UnZip(parentFolder, objFile)
                Set oApp = CreateObject("Shell.Application")
                parentFolder = objFileSystem.GetParentFolderName(objFile)
                For Each fileNameInZip In oApp.Namespace(objFile).Items
                    Set objFile = objFileSystem.getfile(parentFolder & "\" & fileNameInZip)
                    strFileCopy = objFile & ".txt"
                    objFile.Copy strFileCopy, True
                    With DoCmd
                        .SetWarnings False
                        .TransferText acImportDelim, "UnusualLoginImport", "Unusual Logins", strFileCopy, True
                        .SetWarnings True
                    End With
                    If strFileCopy <> objFile.Name Then Kill strFileCopy
                    Name objFile.Name As objFile.Name & ".imported"
                Next
                GoTo zipFile
            End If
            strFileCopy = Left(objFile.Name, intExtPosition - 1) & ".txt"
        Else
            strFileCopy = objFile.Name & ".txt"
        End If
        objFile.Copy strFileCopy, True
textFile:
        With DoCmd
            .SetWarnings False
            .TransferText acImportDelim, "UnusualLoginImport", "Unusual Logins", strFileCopy, True
            .SetWarnings True
        End With
        If strFileCopy <> objFile.Name Then Kill strFileCopy
        Name file As file & ".imported"
zipFile:
    Next

 

[CJ_London]

Re: TransferText Error - "The Microsoft Access database engine could not find the..."

Just speculation but you are using

error - the file name is '20130411012402_BKCHKY_Unusual_Lo.tsv.txt'

and you are appending ".txt" to the filename

So looks like your file is actually '20130411012402_BKCHKY_Unusual_Lo.tsv

So think you need to be applying this 'rule'

If Right(objFile.Name, Len(objFile.Name) - intExtPosition) = "txt" Then
strFileCopy = objFile.Name

 

[tgroneck]

Re: TransferText Error - "The Microsoft Access database engine could not find the..."

Just speculation but you are using

error - the file name is '20130411012402_BKCHKY_Unusual_Lo.tsv.txt'

and you are appending ".txt" to the filename

So looks like your file is actually '20130411012402_BKCHKY_Unusual_Lo.tsv

So think you need to be applying this 'rule'

If Right(objFile.Name, Len(objFile.Name) - intExtPosition) = "txt" Then
strFileCopy = objFile.Name

The following copies the tsv file to a txt file because Access has an issue with tsv. So, at the time of error, the file '20130411012402_BKCHKY_Unusual_Lo.tsv.txt' does exist.

objFile.Copy strFileCopy, True

 

[tgroneck]

Re: TransferText Error - "The Microsoft Access database engine could not find the..."

Instead of adding the .txt to the end, I replaced .tsv with .txt and it worked.

               parentFolder = objFileSystem.GetParentFolderName(objFile)
                Call UnZip(parentFolder, objFile)
                Set oApp = CreateObject("Shell.Application")
                parentFolder = objFileSystem.GetParentFolderName(objFile)
                For Each fileNameInZip In oApp.Namespace(objFile).Items
                    Set objFile2 = objFileSystem.GetFile(parentFolder & "\" & fileNameInZip)
                    intExtPosition = InStr(objFile2.Name, ".")
                    strFileCopy = Left(objFile2.Name, intExtPosition - 1) & ".txt"
                    objFile2.Copy strFileCopy, True
                    With DoCmd
                        .SetWarnings False
                        .TransferText acImportDelim, "UnusualLoginImport", "Unusual Logins", strFileCopy, True
                        .SetWarnings True
                    End With
                    Kill strFileCopy
                    Kill objFile2
                Next
                Name objFile As objFile & ".imported"
                GoTo zipFile

 

[CJ_London]

Re: TransferText Error - "The Microsoft Access database engine could not find the..."

that was going to be my next suggestion! Glad it is resolved