Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-04-30
11
Medium Priority
?
1,314 Views
Last Modified: 2012-05-06
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:
0
Comment
Question by:776PMT
  • 7
  • 4
11 Comments
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 2000 total points
ID: 24270415
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 2000 total points
ID: 24270588
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 2000 total points
ID: 24270687
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 2000 total points
ID: 24270730
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
 

Author Comment

by:776PMT
ID: 24271093
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 2000 total points
ID: 24271525
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
 

Author Comment

by:776PMT
ID: 24271841
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
 

Author Comment

by:776PMT
ID: 24272580
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 2000 total points
ID: 24273565
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
 

Author Comment

by:776PMT
ID: 24273594
The code is in a module. The information came from the query table. The Function name was
Public Function AsapExport()
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 24273706
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question