The text file specification "x" does not exist

[CarlSeil]

Using Access Business 365.

I am getting this error 3625 'The text file specification "x" does not exist. You cannot import, export, or link using the specification."

I created the specification by performing a successful export. I right clicked on the table, chose Export|Text, chose the delimiters I wanted, and successfully exported to the file. I then verified the file by opening it and visually inspected. I then went to External Data|Saved Exports to make sure that the export specification was indeed saved. I clicked on the name of the export specification and copied it to my clipboard. Then used it in the following code:

Private Sub btnCreateFile_Click()
  DoCmd.TransferText transfertype:=acExportDelim, _
   specificationname:="Export-tblTempPositivePayChecksRpt", _
   tablename:="tblTempPositivePayChecksRpt", _
   FileName:="p:\Carl\FileOutPut.txt", _
   hasfieldnames:=True
End Sub

When I click on button "btnCreateFile" I get the error 3625 saying the specification does not exist, but it clearly does and I can repeatedly run it by using External Data|Saved Exports.

When I search on this problem, all I can find is examples that reference some bug in Access 2010.

 

[MajP]

I am guessing, but some times names with characters will not run in code even though they are legit. This is common in file names. I would rename it and get rid of the "-" to see if that helps.

 

[CarlSeil]

That's a good suggestion. That export specification was what Access suggested, so I just went with it. I will re-export and save it under a new name.

For what it's worth I have found DoCmd.RunSavedImportExport works file. Only, I do not have control over where it is saved or what the output file is called. If I cannot get DoCmd.TransferText to work, I may go with this RunSavedImportExport and rename the file pretty sure that will work, so I do have a workaround, it seems.

I mainly use DoCmd.TransferSpreadsheet for this, but since I need a different delimiter, I having to go back to the drawing board on code that has been happily working for seven years.

 

[Gasman]

Isn't there a way that you have to save a specification in a certain way to use on a particular function.?
I seem to recall this caught me out before?

This is the sort of thing I am thinking of?
https://anthonysmoak.com/2018/04/10/how-to-fix-an-import-specification-error-in-microsoft-access/

 

[CarlSeil]

I am guessing, but some times names with characters will not run in code even though they are legit. This is common in file names. I would rename it and get rid of the "-" to see if that helps.

That seemed to do it. Created a new one and gave it a simple name of "pp1" and away it went.

 

[CarlSeil]

Gasman,
You may be onto something with that. And I may have inadvertently fixed it because the second time I did it, I used the Advanced button and the Save As... button. The simpler name might have been a coincidence.

That's a great link

 

[Gasman]

Yes, confused the hell out of me (not hard I admit ) as you can see the file name sitting there, but Access says it cannot find it.?

 

[CarlSeil]

And to top it off, I rarely save the "steps" since I rarely do the same thing twice, and if I do, I do it in VBA. This is the first time I have tried to exports something with a non-standard delimiter. Usually, I just use the .TransferSpreadsheet method, and I have a little wrapper function I have and repeatedly use, so everything goes out as an Excel file or CSV.

I learned something today, because I rarely save exports and would have done the same thing as the author did (and probably over and over because I didn't catch the distinction).

 

[Pat Hartman]

There are two methods of repeating import/export actions. The original one was confusing to people so rather than fix the wizard to eliminate the confusion, they compounded it by making a second method which doesn't work nearly as well.

So, it looks like you saved the import/export using the "new" method and then tried to use the "old" method in code.

Personally, I never use the "new" method because when I do import/export actions, I almost always need to pick the file to import for a list and the "new" method only works is you NEVER change the file name and you NEVER change the location.

The "old" method requires that when going through the wizard you press the Advanced button at some point to save the spec. Once you've done that, then you can reference it in the Transfer actions.

The method to rerun a "new" saved import/export action is different and I don't have the code handy.

 

[Gasman]

I seem to recall that @isladogs created a function to allow changes to those specs, but that adds another level of complexity to a new user?
It is on here somewhere.

 

[Pat Hartman]

I think I do remember seeing something like that but I would never adopt it since there is a method which actually works and is not difficult to use with a little instruction.

 

[isladogs]

Thanks for the alert @Gasman
The code/example app is at View & Edit IMEX Data Task Specifications

And for info, the method also works and isn't difficult to use - even without instruction