Access 2007 vba TransferText Error 3625

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
                    .Edit
                    rstImpDtl![fldImpDate] = Format(Now(), "Short Date")
                    .Update
                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
            Else
                ' If the current record within the Recordset is not the correct
                '  one, then move to the next in preparation of trying again.
                rstImpDtl.MoveNext
            End If
        ' Loop back to continue the iteration through the Recordset
        Loop
        
        ' 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")
        
    Loop

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

errStart:
    With Err
        Debug.Print .Number
        Debug.Print .Description
    End With
    
    rstImpDtl.Close
    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.
KyakyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CodeCruiserCommented:
Have you created the specification?

http://msdn.microsoft.com/en-us/library/ff835958.aspx
KyakyeAuthor Commented:
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.
danishaniCommented:
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;

http://msdn.microsoft.com/en-us/library/ff834375.aspx

Hope this helps,
Daniel

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Jeffrey CoachmanMIS LiasonCommented:
<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
...so 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?

JeffCoachman
KyakyeAuthor Commented:
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.
KyakyeAuthor Commented:
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 LiasonCommented:
LOL

Yes, this was confusing for me as well...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.