CODEWITHSUNDEEP
ms-accessvbams-access-2010
iraserd
iraserd

Reputation: 689

TransferText export to CSV not working, but TransferSpreadsheet to XLSX working

I created a Form in which I created a button and through the context menu I activated the Code-generator for VBA. On a click on the button, the query is correctly exported as .xlsx:

Option Compare Database

Private Sub Befehl0_Click()

            DoCmd.TransferSpreadsheet acExport, , "queryname", "C:\test\queryname.xlsx", -1

End Sub

However, when I modify the export (according to this FAQ):

Option Compare Database

Private Sub Befehl0_Click()

            DoCmd.TransferText acExportDelim, , "queryname", "C:\test\queryname.csv", -1

End Sub

Nothing happens.

I've also tried setting an Export Specification, but that doesn't help either.

What is wrong here?

Upvotes: 2

Views: 8358

Answers (1)

iraserd
iraserd

Reputation: 689

The answer to this was that there was an error '3441' "Text file specification field separator matches decimal separator or text delimiter", which was only visible when trying to run it in the Direct-window (Ctrl+G).

Apparently, when you use a non-English OS this is a problem with the default setting for the delimiters.

To solve it, an Export Specification has to be provided. However, there are two ways to create such, one of which is wrong.

The same is true for importing, for which I found this on the web:

"Yes i had created the Specification and it is viewable via the Import Wizard, however there is no line for it in MSysIMEXSpecs." Then it seems that you may not have created a true import Specification.

In Access 2007 and newer you can also save the "Import Steps". This is not the same thing as in Import Specification.

A "Saved Import", will not show up in MSysIMEXSpecs, but a true Import specification should.

To be sure, ...during the import process steps, if you click the "Advanced" button to actually create the Import Specification.

The last step of the import process is the "Save Import Steps" dialog box, ...again, this is not the same thing as an Import Spec. A "Saved Import/export" will be available under: External Data-->Export-->Saved Export ...so check here as well

Using this knowledge, it works by adding the Export Specification "exportcsv" created in the above process.

        DoCmd.TransferText acExportDelim, "exportcsv", "queryname", "C:\test\queryname.csv", -1

Upvotes: 2

Related Questions