Access 2010 table export to csv

[Monoceros]

I need to export a table to a csv file with VBA code.

How do I do that ?

The name of the table is "Book".

I have Access 2010.

 

[Galaxiom]

TransferText Method

 

[Monoceros]

TransferText Method

Unfortunately it doesn't seem to work :

DoCmd.TransferText acExportDelim, "Standard Output", _
"External Report", "C:\Txtfiles\April.doc"

There is no file created in the location that I specified. What do I have to specify for "Standard Output" ?

 

[Pat Hartman]

DoCmd.TransferText acExportDelim, , "Book", "C:\txtfiles\April.csv", True
1. You asked to export a .csv file NOT a .doc file.
2. Did you look at the reference Galzxiom provided?

 

[Monoceros]

DoCmd.TransferText acExportDelim, , "Book", "C:\txtfiles\April.csv", True
1. You asked to export a .csv file NOT a .doc file.
2. Did you look at the reference Galzxiom provided?

Yes, I looked at the reference and also used the .csv extension.

But no file is transferred.

 

[Pat Hartman]

Did you notice that your transfertext expression is incorrect? Did you try the one I provided?

 

[Monoceros]

Did you notice that your transfertext expression is incorrect? Did you try the one I provided?

Yes, I simply copied it and changed the table name. No file was transfered.

In the meanwhile I have found the following code and now it works


Dim trz As Integer
Dim strCSV As String

For trz = 1 To 511
Close #trz
Next trz
trz = FreeFile
Open "C:\Users\MyName\Documents\Book.csv" For Output Access Write As #trz

With CurrentDb.OpenRecordset("Book ")
Dim x As Integer
For x = 0 To .Fields.Count - 1
strCSV = strCSV & strColumnDelimiter & .Fields(x).Name & "; "
Next x
Print #trz, Mid(strCSV, Len(strColumnDelimiter) + 1)

Do Until .EOF
strCSV = ""
For x = 0 To .Fields.Count - 1
strCSV = strCSV & strColumnDelimiter & Nz(.Fields(x), "<NULL>") & "; "
Next x
Print #trz, Mid(strCSV, Len(strColumnDelimiter) + 1)
.MoveNext
Loop
End With
Close #trz

 

[Pat Hartman]

Yes, I simply copied it and changed the table name. No file was transfered.

You said the table name was "Book". Why did you change it? I see you also changed the path. Did the path you were trying to write to with the TransferText method exist?

I guess two dozen lines of code trumps one. Glad you got it working. The TransferText works great assuming your syntax is correct.

 

[alforddh]

Great instructions Thank you

 

[Reflex_ht]

Hello,

I'm trieng to export a Table to a CSV File. With a VBA like this:

DoCmd.TransferText acExportDelim, , "tbl_Tradeshift", "C:\tbl_Tradeshift_CSV" & ".csv", True

If I use for Specification "Standard output" I become this Error Message: "Runtime Error 3625" - The Specification "Standard Output" dont exist...

If I leav it blank I´ll bekome the Error Mesaage: "Runtime Error 3441 field seperator mathces decimal seperator...."

Am I doing something wrong? Can someon help me? THX

 

[Pat Hartman]

If your export will need an export spec, you need to create the export spec by manually exporting the file once through the export wizard. On the last screen of the export, press the "Advanced" button, change any parameters that need changing and choose the save option. Once you have named and saved the export spec, you can reference it in VBA or macros using the TransferText method.

 

[Reflex_ht]

Thanks but it didn´t work with that even if I manualy export the table and save a export specification. I used a modified version of the Monoceros code and it worked greate

 

[elliotgr]

Is there a bug in Access? It seems many people are complaining about this.
The transfer text does not work as I get error 3441. Even if I create a transfer spec, the message then becomes Transfer Spec not found.
The data is pure text with no funny characters except ().
MyPathFileName is on another PC.

DoCmd.TransferText acExportDelim, , "My_Query", "MyPathFileName"

Using Access 2007 (12.0.6535.5005) SP2 MSO (12.0.6662.5000)