VBA runtime error 3625 The text file specification Export

I am running Access 2016. I am trying to export the results of a query into a text file, I keep on getting an error 3625 no spec is found. I created the spec and if I run the spec it works as expected. I tried putting quotes instead of the export spec, but there was no formatting on the file. The solutions I found on the web were saying to use the advanced tab to define formatting, On my version of Access 2016 there is no advanced tab in the spec creation process. I have stepped through the process and all the directories and the file name is created properly. The error occurs on the line : DoCmd.TransferText TransferType:=acExportDelim, SpecificationName:=strExportSpec, TableName:=strQueryName, FileName:=strFullName, HasFieldNames:=True

Any help is appreciated.

Private Sub Export_Click()
Dim strFileName As String
Dim lFileName As Long
Dim strCurrentDate As String
Dim strFormattedDate As String
Dim dtCurrentDate As Date
Dim strDir As String
Dim strFullName As String
Dim strExportSpec As String
Dim strQueryName As String
Dim strYear As String
Dim strMonth As String
Dim strPath1 As String
Dim strPath2 As String


strYear = Format(Date, "yyyy")
strMonth = Format(Date, "mm")

'Check if Directory Year exists
strPath1 = "C:\Users\Owner\Google Drive\Employment\Mass Unemployment\" & strYear


'Check if year exists
If Dir(strPath1, vbDirectory) = "" Then
    MkDir strPath1
End If
'Create

strPath2 = "C:\Users\Owner\Google Drive\Employment\Mass Unemployment\" & strYear & "\" & strMonth & "\"

If Dir(strPath2, vbDirectory) = "" Then
    MkDir strPath2
End If

strCurrentDate = Date
strFormattedDate = Format(strCurrentDate, "mmddyyyy")
lFileName = InputBox("Enter Week Number", "Enter Week Number")

strFileName = strFormattedDate
strFullName = strPath2 & strFileName & ".txt"
strExportSpec = "Export_Spec"    ' error 3625 export spec does not exist
strQueryName = "qryUnEmployment"

DoCmd.TransferText TransferType:=acExportDelim, SpecificationName:=strExportSpec, TableName:=strQueryName, FileName:=strFullName, HasFieldNames:=True 
 
End Sub

CodePudding user response：

I believe what Parfait is telling you is that the Saved Import/Saved Exports are far different than an Import/Export Specification. You are trying to put a Saved Export into the TransferText parameter where a Specification is called for. You likely did an export at some point and saved the steps as a Saved Export.

If you're truly interested in using a specification for this export then you will want to create one by walking through the import of an already existing text file in the format you would like. See Parfait answer above.

Otherwise, just leave the specification parameter blank and the query will be exported.

CodePudding user response：

The

Export Text Wizard

To date, there may not be any other GUI way to adjust these saved specifications. They are stored in system tables, MSysIMEXspecs and MSysIMEXColumns. Again, do not confuse above text file specific method for the generalized external data methods: ImportExportSpecifications and DoCmd.RunSavedImportExport.