DoCmd.TransferTest wrapping output data in double quotes

Hi
I have built a SQL Table (say tblString) that consist of one field - which holds comma separated data - for example: 24/04/2010,$220.00,$22.00,12345   - Record 1
                               24/04/2010,$440.00,$44.00,12346  - Record 2
                               etc
I need to write these records to an output text (.txt) file which I attempted to do via a
DoCmd.TransferText acExportDelim,  "tblString", "C:\OutFile.txt"
This works in the main except the records in the Output.txt file are wrapped in double quotes
for example based on the above sample the records appear as
                             "24/04/2010,$220.00,$22.00,12345"
                             "24/04/2010,$440.00,$44.00,12346"
It is important that the Output.txt records reflect the input record structure - in other words without the double quotes as the next step in the process is to use the Output.txt file as a CSV file to be import into a separate application.
Is there any way I can write the records to the Output.txt file with out the double quotes. I've tried a number of alternatives - Do.Cmd.OutPutTo acOutputReport, DoCmd.OutPutTo acOutputFile etc but I still run into problems - for example DoCmd.OutputReport imbeds blank lines.
AdlermAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:
You can use in Access another export method (without double quotes) in the following way:
1. File->Export wizard you have to create an export specification (semicolon as separator, nothing as text qualifier) - there is a possibility in the last page of the wizard, you should give a name for the spec.
2. In the VBA code you can set this name DoCmd.TransferText SpecificationName:="NameOfExportSpec"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AdlermAuthor Commented:
Hi aqux3e
Yes I have tried that approach and just retried it using your suggestion of using nothing as text qualifier but it still writes out the records with double quotes wrapped around the data string.
I should have also mentioned that  the application that imports the comma separated CSV file also expects a blank line to distinguish between different group of records. In other words the out put should look like      
            AB,24/04/2010,$220.00,$22.00,12345
            AB,24/04/2010,$440.00,$44.00,12346  
            Blank line
            CD,24/04/2010,$1100.00,$11.00,34567
            CD,24/04/2010,$330.00,$33.00,34568  
where AB and CD distinguish the different groups

I used the semi colon as a separator - as you suggested -but this causes a semi colon to be inserted in the blank line - again not what I want
0
AdlermAuthor Commented:
Hi aqux3e
I'm going to award you the points even though your suggestion didn't work - at least you tried - more than can be said for the other EE "experts. It makes you wonder why you pay EE membership fees from the poor response I've  got from a couple of questions.
Below is my solution to the problem - I searched the EE question library and came across a similar (if not identical) problem to mine so I lifted the code from the EE question library and adopted it to my requirements. I apologise for not crediting the original author but I simply cut and pasted the code without thinking about who supplied the code - so if look familiar please accepty my thanks .

Public Function ExportMYOBInterfaceDetailsToTxtFile(inpDateSuffix As String, inpPathName As String, inpBatchNo As String) As String

    Dim db                             As Database
    Dim rsString                    As New ADODB.Recordset
    Dim strCNN                     As String
    Dim sMyPath                   As String
    Dim sPathFileName         As String
    Dim myfile                       As String
   
   
    strCNN = CNN
   
    rsString.Open "Select * From vu1080_sel_MYOBInterfaceStrings", strCNN, adOpenForwardOnly, adLockReadOnly
   
    myfile = "MYOB Interface" & inpDateSuffix & inpBatchNo & ".txt"
   
    sPathFileName = inpPathName & myfile
   
    Open sPathFileName For Output As #1
    Do Until rsString.EOF
       Print #1, rsString!StringDetails
       rsString.MoveNext
    Loop
    rsString.Close
    Close #1
   
End Function
0
AdlermAuthor Commented:
The solution offered didn't work but at least "aqux3e" tried. His/Her solution was the only offering I received in approximately 2 days and as far as I'm concerned this is less than acceptable. This appears to be an EE trend as a question I posted the other week was abondoned as not one response was received. If I'm not offering enough points then let me know and I will adjust them. On the other hand if so call EE experts are only picking high point qustions and leaving the rest then they are only in the business for self glory rather that trying to help people who have problems and are seeking help.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.