TransferText and Common Dialog Selection

[EddiRae]

Hello,
I have created a subroutine that allows the user to select the file that they are going to import into the Access database.
My issue is that after the file is selected, the variable is displaying the complete path and file name, but I am getting an error that stated:

"Run-Time error '3011':
The Microsoft Access database engine could not find the object <file name> . Make sure the object exists and that you spell its name and the path correctly.

The <file name> is not showing the path, but the string field that I used in the statement does have the path and the file name.

What am I missing? Here is the subroutine:

Private Sub cmdPW_Click()
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim sFileName As String
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryDeleteCMSTransferFile"
    
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
        ' Allow user to make multiple selections in dialog box '
        .AllowMultiSelect = False
        ' Set the title of the dialog box. '
        .Title = "Please select the CMS Transfer File that you want to check"
        ' Clear out the current filters, and add our own.'
        .Filters.Clear
        .Filters.Add "All Files", "*.*"
        ' Show the dialog box. If the .Show method returns True, the '
        ' user picked at least one file. If the .Show method returns '
        ' False, the user clicked Cancel. '
        If .Show = True Then
            'Loop through each file selected and add it to our list box. '
            For Each varFile In .SelectedItems
                DoCmd.TransferText acImportFixed, "tblCMSTransferFile Import Specification", "tblCMSTransferFile", varFile, vbNo
            Next
        Else
            MsgBox "You clicked Cancel in the file dialog box."
        End If
    End With
    
    DoCmd.OpenQuery "qryPremiumWithhold"
    DoCmd.SetWarnings True
    MsgBox "Process has Completed"
End Sub

Thanks for your help in advance!!
Eddi Rae

 

[CBrighton]

Where does it actually fail?

Also, have you tried using a messagebox to confirm the contents of the string 1 line before it fails?

 

[EddiRae]

On the transfertext statement. It says it cannot find the file

 

[CBrighton]

Change:

For Each varFile In .SelectedItems
DoCmd.TransferText acImportFixed, "tblCMSTransferFile Import Specification", "tblCMSTransferFile", varFile, vbNo
Next

To:

For Each varFile In .SelectedItems
MsgBox varFile
'DoCmd.TransferText acImportFixed, "tblCMSTransferFile Import Specification", "tblCMSTransferFile", varFile, vbNo
Next
Exit Sub

This will loop through and display the contents of varFile, but not take any action. It will then exit the sub without running the queries at the end.

This will let you check to ensure varFile contains the whole file path.

 

[EddiRae]

It is showing the file and the path for that file.

 

[EddiRae]

FYI!!! I created a macro doing the same process as the vba code and I am getting this same error. I am working in Access 2007.

Is there an option that I need to change?

 

[CBrighton]

My knowledge of 2007 is poor, so I'll let someone else answer that. I doubt it though.

It's hard to trouble shoot this because I still suspect it's the file path which contains an error, but I suspect you intentionally didn't post the file path as it's something on your business system.

Of course, even if you did post it we would only be able to look for obvious errors (missing file extension, obvious spelling errors, etc) as we can't access the path.


Try copying the path from the macro and pasting it into Windows => run. Rather than reading it and thinking "that looks right", make sure you have copied it directly from the code / macro and seen if Windows is able to open it with the same data as Access has.