The text file specification does not exist when importing into Access

Question

I'm trying to import a semi colon delimited .csv file into an access database using the following code. I've set up an import specification which works called "Import-FACTS". and VBA can see the specification (by putting OLEacc.CurrentProject.ImportExportSpecifications(0) into the watch window.

Sub ImportFacts()

Dim OLEacc As Access.Application

Set OLEacc = GetObject("", "Access.Application")

OLEacc.OpenCurrentDatabase (ThisWorkbook.Path & "\" & "LargeData.accdb")

OLEacc.DoCmd.TransferText TransferType:=acImportDelim, _ 
   SpecificationName:="Import-FACTS",_
   TableName:="Facts",_
   Filename:=ThisWorkbook.Path & "\Facts.csv",_
   HasFieldNames:=False

End Sub

However I get the debug message:

Run-time error '3625' The text file specification "Import-Facts" does not exist You cannot import export or link using the specification.

Any ideas?

ImportExportSpecifications are saved jobs. SpecificationName is something else. What happens if you open an Access session (without Excel) and attempt the same TransferText ... replacing ThisWorkbook.Path with the path to the folder which contains the CSV file? — HansUp
@HansUp It returns the same error message. as suggested I used: DoCmd.TransferText TransferType:=acImportDelim, SpecificationName:="Import-FACTS", TableName:="Facts", FileName:="C:\***\Facts.csv", HasFieldNames:=False. Do you know how I create an import specification rather than a saved job? I hadn't realised there was a difference. — Oliver Humphreys
@HansUp I've managed it! Many thanks for pointing me in the right direction! There's a way of saving import specifications in the Advanced options of the import wizard. — Oliver Humphreys

Serge Salvaille Serge Salvaille · Accepted Answer · 2016-10-28T12:19:08

Important note for imports

There is a problem with the text file specification as the name may not be the same that is displayed when you check your saved imports.

To know the true name, run the following SQL:

SELECT MSysIMEXSpecs.SpecName, 
       MSysIMEXColumns.FieldName, 
       MSysIMEXColumns.Start, 
       MSysIMEXColumns.Width, 
       MSysIMEXColumns.SkipColumn

FROM MSysIMEXColumns INNER JOIN 
     MSysIMEXSpecs ON MSysIMEXColumns.SpecID = MSysIMEXSpecs.SpecID

ORDER BY MSysIMEXSpecs.SpecName, 
         MSysIMEXColumns.Start, 
         MSysIMEXColumns.Width;

The SpecName field contains the right name you are looking for!

The text file specification does not exist when importing into Access

1 Answers