docmd.transfertext bug?

[arichins]

I'm trying to export data to a csv file from access. It works great, so long as I don't specify an export specification in the vb command.

But the reason I need to use an export specification, is that otherwise, Access will use double-quotes as a text qualifier, and I need there to be no text qualifier.

So I make an export spec, and it runs fine, output looks good, but when I try to use it with the docmd.transfertext acexportdelim command, Access throws a "3011" error.

"The microsoft access database engine could not find the object 'hoursexport.csv". The same command works fine if I simply delete the reference to the export spec, which is why I think the issue is some kind of bug in Access.
DoCmd.TransferText acExportDelim, "hoursexport_spec", "HoursExport_ToFile", "C:\Users\richinsaa\desktop\hoursexport.csv"

I'm about to give up and do this with a batch command or something. :banghead:

But I figured I'd give this forum a whirl, in case any of you have any ideas. I've googled this issue and see it's a common problem, but I haven't found any solutions that pertain to me.

 

[theDBguy]

Hi. Can you show us your full code and a screenshot of your "export" spec?

 

[arichins]

I'm not sure how to share the export spec. I create it by going through Access export data wizard (right click on table---> export data---> to text file). There's a point where you hit advanced, and have the opportunity to remove the text qualifier and save the spec.

As far as the code, that line I posted is literally the only line within a button click event that I made to test this functionality.

If I delete the reference to the "hoursexport_spec" it works (but the resulting file has text qualifiers), if I intentionally mis-spell the export, it gives me an appropriate error on that, and the export works stand alone.

That's why I'm thinking it's some kind of bug.

Another odd thing..If I create the file in advance, and click the test button, this code actually deletes that file. So it's obviously finding the file.

 

[theDBguy]

I'm not sure how to share the export spec. I create it by going through Access export data wizard (right click on table---> export data---> to text file). There's a point where you hit advanced, and have the opportunity to remove the text qualifier and save the spec.

You should be able to go to the same "advanced" window and see the list of specs you have saved. Make sure you use the correct name. You can then capture a screen dump once you've selected the one you want to show the spec detail.

 

[arichins]

Yeah! I figured it out, hopefully this will help someone in the future.

Basically, if you are using a query to select the data to be output, and the columns of your query don't match the table from which you built your export spec (in my case, they were in a different order), it'll cause this error.

Changing the field names within the spec itself won't resolve it.

So what I did, is actually build the export spec based on the query used to generate the data. (rather than right clicking on table --->export right click on the query object itself in Access)

 

[theDBguy]

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.