Question re: Microsoft Access VB runtime error 3625. The text file <name> does not exist. You cannot import, export or link using the spec.

The code I've been using is suddenly not working. I'm trying to export models from a database as ".txt" files and move it to specified location.  Here's the code:
Public Function AsapExport()

    Dim MyDB As Database
    Dim Manual As Recordset
    Dim FILENAME As String
    Dim MANUALNUMBER As String


    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    Set Manual = MyDB.OpenRecordset("Parts Manual Information")
   
    Manual.MoveFirst
    If Len(Manual![MANUALNUMBER]) > 9 Then
        MsgBox "ERROR Length of Manual Number Incorrect!"
        GoTo EndFunction
    End If
    If Len(Manual![MANUALNUMBER]) = 6 Then
        MANUALNUMBER = Right(Manual![MANUALNUMBER], 5) 'Remove the letter "R" from string
        FILENAME = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & MANUALNUMBER & "_Mod" & ".txt"
         DoCmd.TransferText A_EXPORTFIXED, "ExportMOD", "qryAsapModelExport", FILENAME
         FILENAME = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & MANUALNUMBER & "_Gra" & ".txt"
         DoCmd.TransferText A_EXPORTFIXED, "ExportGRA", "qryAsapGraphicFile", FILENAME
         FILENAME = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & MANUALNUMBER & "_Prt" & ".txt"
         DoCmd.TransferText A_EXPORTFIXED, "ExportPRT", "qryAsapPartsExport", FILENAME
    End If
    If Len(Manual![MANUALNUMBER]) = 7 Then
        MANUALNUMBER = Right(Manual![MANUALNUMBER], 6) 'Remove the letter "R" from string
        FILENAME = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & MANUALNUMBER & "_Mod" & ".txt"
         DoCmd.TransferText acExportFixed, "ExportMOD", "qryAsapModelExport", FILENAME
         FILENAME = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & MANUALNUMBER & "_Gra" & ".txt"
         DoCmd.TransferText A_EXPORTFIXED, "ExportGRA", "qryAsapGraphicFile", FILENAME
         FILENAME = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & MANUALNUMBER & "_Prt" & ".txt"
         DoCmd.TransferText A_EXPORTFIXED, "ExportPRT", "qryAsapPartsExport", FILENAME
    End If
    If Len(Manual![MANUALNUMBER]) = 9 Then
        MANUALNUMBER = Right(Manual![MANUALNUMBER], 8) 'Remove the letter "R" from string
        FILENAME = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & MANUALNUMBER & "_Mod" & ".txt"
         DoCmd.TransferText A_EXPORTFIXED, "ExportMOD", "qryAsapModelExport", FILENAME
         FILENAME = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & MANUALNUMBER & "_Gra" & ".txt"
         DoCmd.TransferText A_EXPORTFIXED, "ExportGRA", "qryAsapGraphicFile", FILENAME
         FILENAME = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & MANUALNUMBER & "_Prt" & ".txt"
         DoCmd.TransferText A_EXPORTFIXED, "ExportPRT", "qryAsapPartsExport", FILENAME
    End If
       
       
    MsgBox "Export Complete!"
   
EndFunction:
776PMTAsked:
Who is Participating?
 
rockiroadsCommented:
Ok, that should be fine.
I discovered a copy/replace typo in the code posted

where I have  rsManual!sManualNumber (in two places)

it should be

rsManual!MANUALNUMBER


The reason for creating exportmyfile was that it validates the directory and returns error message on transfer text

if not done already, can you make the corrections I stated

then from menu, select debug then click on compile


0
 
rockiroadsCommented:
It says the text file does not exist. Perhaps its advisable to check the file exists beforehand?
Also with all the specifications, do they all exist?

0
 
rockiroadsCommented:
Recommendation here is when you see common code, try put it into a reusable function, saves you duplicating code

Change 1

See how I call importFile passing in the file, spec and queryname

In this function you validate and import.

Also note, you must close recordsets properly and any table or query names you use must be wrapped in []

Public Sub BulkImportFiles()
 
    Dim rsManual As Recordset
    Dim sFile As String
    Dim sManualNumber As String
 
 
    Set rsManual = CurrentDb.OpenRecordset("[Parts Manual Information]")
    
    If rsManual.EOF = True Then
        MsgBox "No Records Found"
    Else
    
        If Len(rsManual![sManualNumber]) > 9 Then
            MsgBox "ERROR Length of Manual Number Incorrect!"
        Else
            If Len(rsManual![sManualNumber]) = 6 Then
                sManualNumber = right(rsManual![sManualNumber], 5) 'Remove the letter "R" from string
        
                sFile = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & sManualNumber & "_Mod" & ".txt"
                importfile sFile, "ExportMOD", "qryAsapModelExport"
         
                sFile = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & sManualNumber & "_Gra" & ".txt"
                importfile sFile, "ExportGRA", "qryAsapGraphicFile"
         
                sFile = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & sManualNumber & "_Prt" & ".txt"
                importfile sFile, "ExportPRT", "qryAsapPartsExport"
            End If
    
            If Len(rsManual![sManualNumber]) = 7 Then
                sManualNumber = right(rsManual![sManualNumber], 6) 'Remove the letter "R" from string
                
                sFile = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & sManualNumber & "_Mod" & ".txt"
                importfile sFile, "ExportMOD", "qryAsapModelExport"
         
                sFile = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & sManualNumber & "_Gra" & ".txt"
                importfile sFile, "ExportGRA", "qryAsapGraphicFile"
                
                sFile = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & sManualNumber & "_Prt" & ".txt"
                importfile sFile, "ExportPRT", "qryAsapPartsExport"
            End If
    
            If Len(rsManual![sManualNumber]) = 9 Then
                sManualNumber = right(rsManual![sManualNumber], 8) 'Remove the letter "R" from string
                
                sFile = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & sManualNumber & "_Mod" & ".txt"
                importfile sFile, "ExportMOD", "qryAsapModelExport"
         
                sFile = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & sManualNumber & "_Gra" & ".txt"
                importfile sFile, "ExportGRA", "qryAsapGraphicFile"
                
                sFile = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\" & sManualNumber & "_Prt" & ".txt"
                importfile sFile, "ExportPRT", "qryAsapPartsExport"
            End If
        End If
    
        MsgBox "Export Complete!"
    End If
       
    rsManual.Close
    Set rsManual = Nothing
       
    
EndFunction:
 
End Sub
 
Private Function ImportMyFile(ByVal sFile As String, ByVal sSpec As String, ByVal sQuery As String)
 
    On Error Resume Next
    
    If DIR$(sFile) = "" Then
        MsgBox "file " & sFile & " does not exist"
    Else
        err.clear
        DoCmd.TransferText A_EXPORTFIXED, sSpec, sQuery, sFile
        If err.Number > 0 Then
            MsgBox "Error importing " & sFile & vbCrLf & vbCrLf & err.Description
        End If
    End If
 
End Function

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rockiroadsCommented:
Change 2 - To further reduce this code, I see the imports are the same, only difference is how you calculate manual number
Because you are stripping first character, you can do this in one line

Here is updated code with recommendations in the comments


Public Sub BulkImportFiles()
 
    Dim rsManual As Recordset
    Dim sFile As String
    Dim sManualNumber As String
    Dim iLen As Integer
    Dim sRootFolder As String
 
 
    Set rsManual = CurrentDb.OpenRecordset("[Parts Manual Information]")
    
    'RECOMMEND YOU CHECK FOR EMPTY TABLE
    If rsManual.EOF = True Then
        MsgBox "No Records Found"
    Else
    
        'RECOMMEND YOU ASSIGN TO A VARIABLE SO YOU ONLY EVER HAVE TO CALCULATE LENGTH ONCE
        iLen = Len(rsManual!sManualNumber)
        
        'RECOMMEND YOU ASSIGN COMMON STRINGS TO A VARIABLE, ANY CHANGE ONLY THEN NEEDS TO BE DONE ONCE
        sRootFolder = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\"
        
        If iLen > 9 Then
            MsgBox "ERROR Length of Manual Number Incorrect!"
        Else
            'NOTE USE OF iLEN - HOW CODE IS MORE CLEANER AND READABLE
            If iLen = 6 Or iLen = 7 Or iLen = 9 Then
                sManualNumber = right(rsManual!sManualNumber, iLen - 1) 'Remove the letter "R" from string
        
                'NOTE USE OF sRootFolder - HOW CODE IS MORE CLEANER AND READABLE
                sFile = sRootFolder & sManualNumber & "_Mod" & ".txt"
                importfile sFile, "ExportMOD", "qryAsapModelExport"
         
                sFile = sRootFolder & sManualNumber & "_Gra" & ".txt"
                importfile sFile, "ExportGRA", "qryAsapGraphicFile"
         
                sFile = sRootFolder & sManualNumber & "_Prt" & ".txt"
                importfile sFile, "ExportPRT", "qryAsapPartsExport"
            End If
    
        End If
    
        MsgBox "Export Complete!"
    End If
       
    'Close down
    rsManual.Close
    Set rsManual = Nothing
       
End Sub

Open in new window

0
 
rockiroadsCommented:
Now if you run with the revised code, hopefully we will get a better understanding of the error, whether file is missing or problem with transfertext

its not a import as such is it, more of a export. You can rename the function.

here is revised code again


Public Sub BulkImportFiles()
 
    Dim rsManual As Recordset
    Dim sFile As String
    Dim sManualNumber As String
    Dim iLen As Integer
    Dim sRootFolder As String
 
 
    Set rsManual = CurrentDb.OpenRecordset("[Parts Manual Information]")
    
    'RECOMMEND YOU CHECK FOR EMPTY TABLE
    If rsManual.EOF = True Then
        MsgBox "No Records Found"
    Else
    
        'RECOMMEND YOU ASSIGN TO A VARIABLE SO YOU ONLY EVER HAVE TO CALCULATE LENGTH ONCE
        iLen = Len(rsManual!sManualNumber)
        
        'RECOMMEND YOU ASSIGN COMMON STRINGS TO A VARIABLE, ANY CHANGE ONLY THEN NEEDS TO BE DONE ONCE
        sRootFolder = "\\Gmffs1fay\service\Publications\PRODUCTION\PARTS CATALOGS\ASAPX\"
        
        If iLen > 9 Then
            MsgBox "ERROR Length of Manual Number Incorrect!"
        Else
            'NOTE USE OF iLEN - HOW CODE IS MORE CLEANER AND READABLE
            If iLen = 6 Or iLen = 7 Or iLen = 9 Then
                sManualNumber = right(rsManual!sManualNumber, iLen - 1) 'Remove the letter "R" from string
        
                'NOTE USE OF sRootFolder - HOW CODE IS MORE CLEANER AND READABLE
                sFile = sRootFolder & sManualNumber & "_Mod" & ".txt"
                ExportMyFile sFile, "ExportMOD", "qryAsapModelExport"
         
                sFile = sRootFolder & sManualNumber & "_Gra" & ".txt"
                ExportMyFile sFile, "ExportGRA", "qryAsapGraphicFile"
         
                sFile = sRootFolder & sManualNumber & "_Prt" & ".txt"
                ExportMyFile sFile, "ExportPRT", "qryAsapPartsExport"
            End If
    
        End If
    
        MsgBox "Export Complete!"
    End If
       
    'Close down
    rsManual.Close
    Set rsManual = Nothing
       
End Sub
 
Private Function ExportMyFile(ByVal sFile As String, ByVal sSpec As String, ByVal sQuery As String)
 
    On Error Resume Next
    
    If DIR$(sFile) = "" Then
        MsgBox "file " & sFile & " does not exist"
    Else
        err.clear
        DoCmd.TransferText acExportFixed, sSpec, sQuery, sFile
        If err.Number > 0 Then
            MsgBox "Error " & err.Number & " when exporting " & sFile & vbCrLf & vbCrLf & err.Description
        End If
    End If
 
End Function

Open in new window

0
 
776PMTAuthor Commented:
It is indeed more elegant than what I have. I'm not that fluent in code. I'm currently the "copy and splice" type right now.
The original code was a Public Function, your code changes it to a Public Sub. Does this make a difference in how the code works?
0
 
rockiroadsCommented:
No not really
Only use it as a function if you want it to return a value or use it within a macro

I gathered from your code you might be a bit unused to it which is why I tried to explain and also write comments. Im hoping you have understood that.
0
 
776PMTAuthor Commented:
It's pretty clear. OK. I went back to my code, replaced it with yours and got an error regarding changing the Function to a Sub.  
The error: The expression on Mouse Down you entered as the event property setting produced the following error: The expression you entered has a function name that Goodman Parts Catalog Database can't find. The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. There may have been an error evaluating the function, event, or macro,
0
 
776PMTAuthor Commented:
OK, here's what I did to get it to work. I changed

ExportMyFile sFile, "ExportMOD", "qryAsapModelExport"
back to
DoCmd.TransferText A_EXPORTFIXED, "ExportMOD", "qryAsapModelExport", sFile

and it worked.  The database really didn't like the ExportMyFile function.

0
 
rockiroadsCommented:
Where was this import code in the first place? I didnt see your function name
Also where is this code, in a form or a module?
0
 
776PMTAuthor Commented:
The code is in a module. The information came from the query table. The Function name was
Public Function AsapExport()
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.