Creating a text file from VBA and exporting data to .txt

[BennyLinton]

First I'm developing in Access 2010.
I am struggling with code that is supposed to create a text file and populate it from a query. The Query "cbt_Candidate_Export_Temp" is working fine but I keep getting the error in the second argument:

"Run-time error '3625': The text file specification 'cbtTab' does not exist... "

DoCmd.TransferText acExportDelim, "cbtTab", "cbt_Candidate_Export_Temp", "\\denali\nbcc\databases\BCC\CBT_Export\" & strFileName & ".txt", True

What object is "cbtTab"? I didn't write this base code and I can't find it nor do I know how to create "cbtTab"

Thanks in advance!

 

[burrina]

Your telling it to export a file named cbtTab , is your file not named this?

 

[BennyLinton]

I'm not sure what part of the argument that part fulfills the "cbtTab".

Thename of the file will be built from this in bold:

DoCmd.TransferText acExportDelim, "cbtTab", "cbt_Candidate_Export_Temp", "\\denali\nbcc\databases\BCC\CBT_Export\" & strFileName & ".txt", True

 

[MarkK]

You should read the manual on DoCmd.TransferText. The second parameter, which you supply as "cbtTab", seen in bold below ...

DoCmd.TransferText acExportDelim, "cbtTab", "cbt_Candidate_Export_Temp", "\\denali\nbcc\databases\BCC\CBT_Export\" & strFileName & ".txt", True

... is called "SpecificationName."

To read VBA help on this topic, open a code window, click the Help button on the main menu, click the first item, called Microsoft Visual Basic Help, and then type "TransferText" into the search box. This will provide you with very clear and detailed information about how to use this command.

Hope this helps,

 

[BennyLinton]

I've read through the Access documentation about the SpecificationName. I have one database that is successfully using almost the exact same code. It uses:

DoCmd.TransferText acExportDelim, "cbtTab", "cbt_Candidate_Export_Temp", "\\denali\nbcc\databases\BCC\ABC_Export\" & strFileName & ".txt", True

I look through that database, its destinaton folder, the code, saved exports and nowhere is an object to be found called "cbtTab"

 

[jdraw]

Is it possible you have some corruption??
You could try compact and repair, or even copy all to a new empty database.

 

[BennyLinton]

I tried compact, repair, creating a new database moving all the objects over. Still the same error and pointing to that specific "cbtTab" not found.

 

[jdraw]

Can you post database or is it not practical?

 

[MarkK]

I look through that database, its destinaton folder, the code, saved exports and nowhere is an object to be found called "cbtTab"

I don't see the mystery. Your TransferText command is using the name of an import/export specification that doesn't exist. What you can do is manually run the export, and after that happens, a dialog opens and asks if you want to save those steps. Answer yes, save the specification, call it cbtTab, and then your future exports will not fail, because they will find the spec named "cbtTab."

Or am I missing something?

 

[BennyLinton]

Here's the steps I took - Keep in mind I want my code to create a text file, not just populate an existing one. I went to my External Data tab while I had the table open that has the data to be exported. Then I choose Text File as my selection from the tab. I see the wizard come up that is labeled "Export - Text File", and in that I type the path to the folder where I want my export to go along with the file name which I created "\denali\nbcc\databases\BCC\CBT_Export\cbtTab.txt". From there I check the box labeled "Open the destination file after the export operation is complete." A dialog box comes up with the radio buttons for the encoding "Windows (default)", "MS-DOS", "Unicode", and "Unicode (UTF-8)". I chose Windows. I don't choose any of the "Specify export options". Under Advanced I choose "Fixed Width" set my lengths, preview, save the file to my destination folder, save the export steps as "cbtTab". I run my program and the error is still there.

 

[MarkK]

Try paste this code into a standard module, run it, and report back on what gets printed out in the immediate pane. <Ctrl>+<G> to show the immediate pane. This shows a list of what import/export specs exist in your project.

Private Sub Test1083247129487()
    Dim ixs As Access.ImportExportSpecification
    
    For Each ixs In CurrentProject.ImportExportSpecifications
        Debug.Print ixs.Name
    Next
End Sub

Is "cbtTab" in there?

 

[BennyLinton]

I ran the code and "cbtTab" is not there. I looked at a similar database that references "cbtTab" successfully and I don't see it in the list there either.

 

[MarkK]

A few thoughts . . .
1) Try using the name of a specification that does exist. Does that change the error?

2) Post a database the demonstrates the problem.

3) Consider just using an export specification, not TransferText . . .

dim ixs as Access.ImportExportSpecification
set ixs = Application.ImportExportSpecifications("YourSpec")
ixs.Execute

 

[BennyLinton]

I figured it out another way... you can right click in your object navigator, select 'show system objects', find the spec that is working in one database (I found "cbtTab" hiding there in a the table) go into the greyed-out table called "MSysIMEXSpecs" copy the working object (my cbtTab) into the misbehaving database, the code then finds it and it works perfectly now.