Re: TransferText Export Problem

On 4 Sep, 23:22, Rich P <rpng...@xxxxxxx> wrote:
Hi Andrew,

Here is the easiest, most reliable way to export data to a csv file (or
text file) from Access -- use VBA.

Sub ExportDataToCSV()
Dim DB As DAO.Database, RS As DAO.Recordset, fld As DAO.Field, str1 As
String
Set DB = CurrentDb
Set RS = DB.OpenRecordset("tbl1")
Open "c:\1A\ExportTest1.csv" For Output As #1
Do While Not RS.EOF
str1 = ""
For Each fld In RS.Fields
str1 = str1 & fld & ","
Next
str1 = Mid(str1, 1, Len(str1) - 1) '--remove last ","
Print #1, str1
RS.MoveNext
Loop
Close #1
RS.Close
End Sub

Note: the Print command in the loop (Print #1, str1) prints/writes the
data to the csv file without quotations. There is also a Write command
(Write #1, str1) which would include double quotations around the
string. In my example I am not using quotes (using Print) so I combine
all the table fields into one string. If I wanted to surround each
table field with double quotes I would use the Write Command and do each
table field individually instead of combining the fields all into one
string. This is way more reliable than using Specs and much easier to
manipulate, maintain,...

You can find more information in the Access Help Files. Just copy this
code into a standard code module. If you have any questions on anything
-- just place the mouse cursor over the object in question (say the Open
command in Open "c:\1A\ExportTest1.csv"...) and press the F1 key. That
will bring up Access help on that topic.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Thanks for the tip. Will give it a go!

I still don't understand why the field is exporting as text, but if
need be, when the file gets re-imported into access I can use a query
to change it to an integer.

Cheers

.

Relevant Pages

  • Re: TransferText Export Problem
    ... most reliable way to export data to a csv file (or ... Dim DB As DAO.Database, RS As DAO.Recordset, fld As DAO.Field, str1 As ... String ... There is also a Write command ...
    (comp.databases.ms-access)
  • TIP #185: Null Handling
    ... nulls, and command modifications for manipulating them. ... Tcl deals with strings, the universal medium for representing data. ... is know and it is an empty string, but if a respondent forgets to give ...
    (comp.lang.tcl)
  • Re: Determine how Word was launched
    ... inspect the command line used to launch Word. ... Private Declare Function GetCommandLineA Lib "kernel32" As Long ... Public Function GetCommandLineAs String ... Dim lngCmdLinePtr As Long ...
    (microsoft.public.office.developer.vba)
  • Re: Class.getMethod in classs static initializer block
    ... I just wanted to add that I had some similar code for a "command line server" project. ... static private final boolean DEBUG = true; ... {public ReturnCode runCommand(String s) ... public ReturnCode parseLine(String line) ...
    (comp.lang.java.programmer)
  • Re: New "base document" available
    ... How to go about retrieving command line parameters? ... the command line arguments (and environment variables) ... Keyword and positional parameters can be mixed in the same command line. ... clp-name points to a string containing "P1" ...
    (comp.lang.cobol)