Need a way to export ms access table to text with tab delimitation using vb sql call?

Posted on 2003-03-24
Medium Priority
Last Modified: 2011-09-20
I have the following sql statement that I use right now to export a database table into an excel spreadsheet using an automation process.  Unfortunantly not all can be exported as excel files and instead need to be exported as a text file with tab delimitations.  Can anyone help me change the sql statement I included below to make it instead export to a tab delimited text file.  You help is welcomed.  Thanks,

    Dim DAODB As Database

    Set DAODB = OpenDatabase(strDB)

    'If excel file already exists, you can delete it here
    If Dir(strExcel) <> "" Then Kill strExcel

    DAODB.Execute ("SELECT * INTO [Excel 8.0;DATABASE=" & strExcel & "].[" & _
        strWorksheet & "] FROM " & "[" & strTable & "]")
Question by:uncensored
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8197433
Here is quickie, but you will need to reformat the output yourself.  Delete every other line amd strip white space and the "|" character as a field delimiter.

DoCmd.OutputTo acOutputTXT, "TableName", acFormatTXT, Access.CurrentProject.Path & "\TableName.TXT"

Regards,  P1

Author Comment

ID: 8198354
I already know that way, that is in access.  I need to do this through VB instead for automation in my VB6 program.

Expert Comment

ID: 8198759
What are you trying to export to, that needs a tab delimited format?  Also please explain format desired?

It is not that hard to write a output formatter running off a ADO query for what you want.

What version of Access are we talking about?

Regards,  P1
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 8203562
ok it is access 97 and what I need to do is basically export a table into a tab delimited text format.  I know I can loop through the fields to export but I didn't want to have to do that if there is an easier way using a sql statement like the one I included in my first example above that will export a table into an Excel 97 spreadsheet.  In my example above it takes less time to export this way then it would to loop through the fields, so what I really want is a modified version of the sql statement that will export to a text file instead of and Excel spreadsheet with tabs.  I hope this explains it somewhat better if not see below but I know this does not work since I tried already!

DAODB.Execute ("SELECT * INTO [Wordpad;DATABASE=" & strDoc & "] FROM " & "[" & strTable & "]")

Expert Comment

ID: 8203904
For me personally, The quickest way between what you want and the programming to get you there is the text output from Access and re-format it with VB to the final format.

Using the 1st ( check ) and 3rd ( double check ) lines build a field width array using the "|" character as the delimiter.  This allows dynamic outputting when you change number of fields ( or widths ) in the MDB.  This is to help you in case you have "|" in the text of your fields.  Then decide how much format checking you want to do.  Then parse the file every other line using the field width array to delimit cells and checking for the "|" character.  Then "Trim" your data cells and output with vbTab between them.

You will not believe how fast VB does the re-formatting.  I do this all the time, glueing data from one system to another when compatable data formats are un-available.

If you want and can wait a bit, I'll write the re-formater for you.

Regards,  P1

Expert Comment

ID: 8531723
Hi uncensored,
This old question (QID 20561136) needs to be finalized -- accept an answer, split points, or get a refund.  Please see http://www.cityofangels.com/Experts/Closing.htm for information and options.

Accepted Solution

YensidMod earned 0 total points
ID: 9496119
This question is PAQed and no points refunded (of 50)

Expert Exchange Moderator

Expert Comment

ID: 13445217
Was This Question ever answered ?

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month11 days, 1 hour left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question