?
Solved

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

Posted on 2003-03-24
8
Medium Priority
?
195 Views
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

    'EXPORTS TABLE IN ACCESS DATABASE TO EXCEL
    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 & "]")
    DAODB.Close
0
Comment
Question by:uncensored
8 Comments
 
LVL 4

Expert Comment

by:P1
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
0
 

Author Comment

by:uncensored
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.
0
 
LVL 4

Expert Comment

by:P1
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

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

 

Author Comment

by:uncensored
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 & "]")
   
0
 
LVL 4

Expert Comment

by:P1
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
0
 

Expert Comment

by:CleanupPing
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.
0
 

Accepted Solution

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

YensidMod
Expert Exchange Moderator
0
 

Expert Comment

by:cdiscenna
ID: 13445217
Was This Question ever answered ?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

571 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