Access 2007 vba TransferText Error 3625

Kyakye used Ask the Experts™
Good Morning All,

I am trying to automate the import of Data to my Access 2007 database and doing this using the DoCmd.TransferText

I am getting the following Error details at this line ".TransferText acExportDelim, strImpSpc, strImpTbl, strFilPth"

Number = 3625
Description = The text file specification '<ImpSpcName>' does not exist.  You cannot import, export, or link using the specification.

Although i have created the Import Specification using the Import Wizard, the record is not visable in MSysIMEXSpecs.

This is the code that i am using:
Option Compare Database
Option Explicit
Const strImport = "\\gbhgmercser0021\GMCataloguing\GM Central Stock Management Files\Ben.James_Projects\OTB Tool\Development files\PlanFore\Import\"
Const strArchive = "\\gbhgmercser0021\GMCataloguing\GM Central Stock Management Files\Ben.James_Projects\OTB Tool\Development files\PlanFore\Archive\"
Const strImported = "\\gbhgmercser0021\GMCataloguing\GM Central Stock Management Files\Ben.James_Projects\OTB Tool\Development files\PlanFore\Imported\"

    Public Sub mdlImport()
    ' Short Desc:       This sub is used to import all of the relevant data
    ' Date Created:     Wednesday 15th February 2012
    ' Long Desc:        This sub will iterate through the import folder and check
    '                    each file to see if it should be imported. If it is then
    '                    it will be imported to a temp table, then pushed to the
    '                    live table with the temp being emptied afterwards. Once
    '                    the data has been imported the file will be moved to the
    '                    Archive location and a copy converted to a .txt and moved
    '                    to the Imported location.
    ' Input Variables:  None

    ' Dimension variables
    Dim strImpFil As String
    Dim objDB As Database
    Dim rstImpDtl As DAO.Recordset
    Dim strImpTbl As String
    Dim strImpSpc As String
    Dim strImpName As String
    Dim strFilPth As String
    ' Start up Error-Handling
    On Error GoTo errStart
    ' Set Variables
    Set objDB = CurrentDb
    With objDB
        Set rstImpDtl = .OpenRecordset("tblImportDetail", dbOpenDynaset)
    End With
    ' Work through the Import folder and assign the file name to a variable
    Do Until Len(Dir(strImport)) = 0
        strImpFil = Dir(strImport)
        ' Strip out the DateStamp off of the front of the file name
        strImpName = Replace(Replace(Mid(strImpFil, 8, 255), ".xlsx", ""), ".csv", "")
        ' Iterate through the Import-Details recordset, and ensure there is no
        '  currently assigned Import-Spec
        Do While Not rstImpDtl.EOF And strImpSpc = ""
            ' With the refined file name, check this against the table of
            '  Import-Details
            If strImpName = rstImpDtl![fldFileName] Then
                ' Once the correct record is found assign the Import-Table,
                '  Import-Spec and File-Path variables
                strImpTbl = rstImpDtl![fldTmpTbl]
                strImpSpc = rstImpDtl![fldImpSpec]
                strFilPth = strImport & strImpFil
                ' Import the data using TransferText and the newly assigned variables
                With DoCmd
                    '.SetWarnings False
                    .TransferText acExportDelim, strImpSpc, strImpTbl, strFilPth
                    .SetWarnings True
                End With
                ' After the import to the Temp-Table, update the Import details with
                '  the, last date imported.
                With rstImpDtl
                    rstImpDtl![fldImpDate] = Format(Now(), "Short Date")
                End With
                ' Run the query to push the Temp-Table data over to the Live-Table,
                '  and then clear out the Temp-Table
                With DoCmd
                    .SetWarnings False
                    .OpenQuery rstImpDtl![fldTransQry]
                    .OpenQuery rstImpDtl![fldTmpDelQry]
                    .SetWarnings True
                End With
                ' If the current record within the Recordset is not the correct
                '  one, then move to the next in preparation of trying again.
            End If
        ' Loop back to continue the iteration through the Recordset
        ' Clear down the Import Variables
        strImpTbl = Null
        strImpSpc = Null
        strFilPth = Null
        ' Move the imported file to the archive location, and make a copy that is
        '  converted to a .txt and placed in to the Imported folder.
        Name strImport & strImpFil As strArchive & strImpFil
        FileCopy strArchive & strImpFil, strImported & Replace(strImpFil, ".xlsx", ".txt")

    ' Clear down and exit
    Set rstImpDtl = Nothing
    Set objDB = Nothing
    Exit Sub

    With Err
        Debug.Print .Number
        Debug.Print .Description
    End With
    Set rstImpDtl = Nothing
    Set objDB = Nothing

    Exit Sub
    End Sub

Open in new window

I have been reading a few sites about this and am almost at the point of creating a new DB and then importing all the tables/queries/modules then create an Import Spec again. This seems to have fixed the issue for some people in 2003 (.mdb) versions, however i was wondering if there is a confirmed solution for 2007 (.accdb) users?

If there are any questions about this, please let me know.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Have you created the specification?


Yes i had created the Specification and it is viewable via the Import Wizard, however there is no line for it in MSysIMEXSpecs.

I have also created more Import Spcifications as a test, and these are not there either. Just to clarify it is not that they have been there before and are suddenly not, i created them today.

I have used the Compact and Repair otion to rule out that as a fix.
There is a different in saving the import specs at the end of the Wizard and during the Wizard (Advanced button > Save As).

When you use the Advanced button, the spec will be saved in Table MSysIMEXSpecs.

This spec  you can use with the DoCmd.TransferText command.

The import spec saved at the end, can be used with the DoCmd.RunSavedImportExport Method. See below link;

Hope this helps,
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
<Yes i had created the Specification and it is viewable via the Import Wizard, however there is no line for it in MSysIMEXSpecs.>
Then it seems that you may not have created a true import Specification.

In Access 2007 and newer you can also save the "Import Steps".
This *is not* the same thing as in Import Specification.

A "Saved Import", will not show up in MSysIMEXSpecs, but a true Import specification should.

To be sure, ...*during* the import process steps, if you click the "Advanced" button to actually create the Import Specification.

The last step of the import process is the "Save Import Steps" dialog box, ...again, this is *not* the same thing as an Import Spec.
A "Saved Import/export" will be available under:
    External Data-->Export-->Saved Export check here as well

Are you saying that if you created a brand new DB and created a import Spec, it is not in MSysIMEXSpecs?



Afternoon danishani and boag2000,

Thank you for you comments. I'll work with these as you both seem to be saying the same thing.

I'll just confirm that this fixes my issue and then close this questions down.


Thank you both for your assistance in this, you were both right. This was compiled with the fact I was importing an .xlsx file and so did not see an 'Advanced' button.

I was under the impression that this was just another UI change to 2007 from 2003, but I was wrong. Thank you again for both of your help.

I have altered my file manipulation to save the file as a .csv, and after I tried to import this I could once again could see the 'Advanced' button that I am used too.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012


Yes, this was confusing for me as well...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial