Very strange VB code error... please help.

[Guest]

First of all, here is the code in question:

Function AutoImportImportFiles() As Variant
Dim strFileDateMonth
Dim strFileDateDay
Dim strFileType As String
Dim strDestinationName
strFileType = "BD"
For strFileDateMonth = "10" To "12"
For strFileDateDay = "10" To "31"
strDestinationName = "C:\Import\" & strFileType & strFileDateMonth &
strFileDateDay & "05.txt"
If Len(Dir(strDestinationName)) > 0 Then
DoCmd.TransferText acExportDelim, "BD Import Specification", "Declined
Transactions BD", strDestinationName, No
DoCmd.OpenQuery "Update Date BD"
DoCmd.OpenQuery "Append Declined BDs"
DoCmd.OpenQuery "Delete BD Table"
Else: End If
Next strFileDateDay
Next strFileDateMonth
End Function


The problem I'm getting is when the code gets to the DoCmd.TransferText
line, Access gives me the following error:

****
Run-time error '3011':

The Microsoft Jet database engine could not find the object 'BD101005#txt'.
Make sure the object exists and that you spell its name and the path name
correctly.
****

Why is it changing the ".txt" to "#txt"?

 

[Guest]

Not exactly sure, but try inserting this:

MsgBox strDestinationName

on the line after you build the string to see what the entire string is. See
if it is inserting more than just the one # symbol.

The # could indicate that it is interpreting a variable's type incorrectly
(e.g. as a date instead of a string), so you may need to Dim
strDestinationName As String instead of just Dim strDestinationName

Just an idea.

 

[John Nurick]

You can get this error if there's a discrepancy between the
import/export specification you're using and the structure of the table
or query you're exporting.