Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2003
    Posts
    280

    Unanswered: Is there a way to export a access table to a text file with VBA?

    I need to export the access table to a text file because another program need to read it. When it is exported, the file extension has to be change to .OUT extension. Also, have an delimiter between each column.

    I have browse through google.com and access books but no luck with codes on what I am doing.

  2. #2
    Join Date
    Oct 2004
    Posts
    5

    Table to Text

    DoCmd.OutputTo acTable, "mytable", "MS-DOSText(*.txt)", "c:\mytable.txt", False, "", 0

  3. #3
    Join Date
    Apr 2003
    Posts
    280
    OutputTo <-----Error message: Wrong number of argument.

    The table name is "PLINK"

    btw, acTable should be acOutputTable, I think.

    Here is my code with your code.

    Dim conndb As ADODB.Connection
    Dim rsPlink As ADODB.Recordset
    Dim outFile As String

    Set conndb = CurrentProject.Connection
    Set rsPlink = New ADODB.Recordset

    rsPlink.Open "PLINK", conndb, adOpenKeyset, adLockPessimistic, adCmdTable

    DoCmd.OutputTo acOutputTable, rsPlink, "MS-DOSText(*.txt)", "C:\Programs\Plink.txt", False, "", 0
    Last edited by lansing; 10-13-04 at 17:33.

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Try

    DoCmd.OutputTo acOutputTable, TableName, acFormatTXT, "C:\Temp\yourOutPutFileName.dat"

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Also you can use

    DoCmd.TransferText acExportDelim, , YourTableName, "C:Temp\fileName.txt", true

    True means the first line will be the fields names

  6. #6
    Join Date
    Apr 2003
    Posts
    280
    The table name is PLINK

    Quote Originally Posted by hammbakka
    Try

    DoCmd.OutputTo acOutputTable, TableName, acFormatTXT, "C:\Temp\yourOutPutFileName.dat"
    Private Sub cmdExport_Click()

    DoCmd.OutputTo acOutputTable, PLINK, acFormatTXT, "C:\Programs\Plink.txt"

    End Sub

    Error: The Object Type argument for the action or method is blank or invalid

    Quote Originally Posted by hammbakka
    Also you can use

    DoCmd.TransferText acExportDelim, , YourTableName, "C:Temp\fileName.txt", true

    True means the first line will be the fields names
    Private Sub cmdExport_Click()
    DoCmd.TransferText acExportDelim, , PLINK, "C:\Programs\Plink.txt", True
    End Sub

    Error: The action or method requires a Table Name arugment.

    What am I doing wrong?
    Last edited by lansing; 10-14-04 at 11:58.

  7. #7
    Join Date
    Apr 2003
    Posts
    280
    Nevermind, I find out that I have to put a double quoatation around the table name, which I thought I did the first time.

    Anyways, now the out data is showing like this.
    "CHIHA000",19332,0,0,0,,,,,0.00,"B","PR122",,,"PRO 00","434406400 ","21"

    Is there a way to get rid of the double quotation and change the delimiter to "|" for each column? Right now, the coma is set as the delimiter now.

  8. #8
    Join Date
    Apr 2003
    Posts
    280
    -Anyone with a solution. This code works but I need for it to send to a "PLINK.OUT" file. The error show: "Cannot update. Database or object is read-only"

    DoCmd.TransferText acExportDelim, , "PLINK", "C:\Programs\Plink.OUT", False

    -This code is able to send it the "PLINK.OUT" file but it can't set the delimiter like the above code. Which also, I need to set the delimiter between each column with "|".

    DoCmd.OutputTo acOutputTable, "PLINK", acFormatTXT, "C:\Programs\Plink.OUT", False, "", 0
    Last edited by lansing; 10-14-04 at 14:33.

  9. #9
    Join Date
    Apr 2003
    Posts
    280
    Nevermind, i got it.

    1. Click on a table
    2. Go to the File menu then click on Export
    3. Set "Save Type As" as Text Files. An Export Wizard will pop up.
    4. Click on the Advance button.
    5. Change the Field Delimiter to "|" and set Text Qualifer to NONE
    6. Click on "Save As" and choose a name that you will remember to put into the code later on. I save it as "DelimiterChar"
    7. Click cancel twice to get out of the wizard.

    This is how the code looks like.

    DoCmd.TransferText acExportDelim, "DelimiterChar", "PLINK", "C:\Programs\Plink.txt", False

    The output is like this:

    CHIHA000|19332|0|0|0|||||0.00|B|PR122|||PRO00|4344 06400 |21



    The only problem I'm having is unable to send it to a .OUT extension with the above code. Anyone with ideas?

  10. #10
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you can do something like this I guess:
    DoCmd.TransferText acExportDelim, , "PLINK", "C:\Programs\Plink.txt", False
    FileCopy "C:\Programs\Plink.txt", "C:\Programs\Plink.out"

    for double quotation, they had to be there because what hapens if there is comma inthe string? double quotation is the only way for applications to figure out if it is delimiter or a comma in the string.

    I think after exporting you can open the exported file with old school methods, replace all "," with "|" and remove all doube quotation.
    ghozy.

  11. #11
    Join Date
    Apr 2003
    Posts
    280
    Well, the other program that is using the .OUT extension, requires to have a "|" between each column of data for it to read it. So, having a "|" shouldn't be a problem with the commas.

    btw, thanks for your help and everyone else...

  12. #12
    Join Date
    Apr 2003
    Posts
    280
    One more question, is there a way to use an Access query rather than the table. Example

    DoCmd.TransferText acExportDelim, , "qryTransInfo", "C:\Programs\Plink.txt", False
    FileCopy "C:\Programs\Plink.txt", "C:\Programs\Plink.out"

    I try it but it didn't work. The only way I can think of making it to work, is to make a table from the query, which I don't want to do. But if it is the only way, then I will.

  13. #13
    Join Date
    Sep 2004
    Posts
    161
    The help of Acc97 say yes if the query is a selection query. what is your problem ?

  14. #14
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    ok I wrote a Sub that exports any table or query or sql statemnt into a text file without double quotation and "|" delimitier. copy it into a module then all you need to is call this Sub with a filename and tablename/quername/SQL statement. for example:
    CustomReport "C:\test.out","Table1"
    CustomReport "C:\test.out","Query2"
    CustomReport "C:\test.out","SELECT * FROM Table1"

    Code:
    Public Sub CustomExport(Filename As String, ATable As String)
    	Dim Rst As DAO.Recordset
    	Dim AField As DAO.Field
    	Dim TempStr As String
    	Dim FileNumber
    	FileNumber = FreeFile
    	Open Filename For Output As #FileNumber
    	Set Rst = CurrentDb.OpenRecordset(ATable, dbOpenForwardOnly)
    	Do While Not Rst.EOF
    		For Each AField In Rst.Fields
    			TempStr = TempStr & AField.Value & "|"
    		Next
    		Print #FileNumber, Left(TempStr, Len(TempStr) - 1)
    		TempStr = ""
    		Rst.MoveNext
    	Loop
    	Rst.Close
    	Set Rst= Nothing
    	Close #FileNumber
    End Sub
    it was good to know that I didn't forget dealing with files.
    Last edited by ghozy; 10-15-04 at 09:45. Reason: code edit
    ghozy.

  15. #15
    Join Date
    Oct 2005
    Posts
    2

    extract all the tables to txt

    Hello
    I am having a small problem,
    I need to say that I dont know VB at all,
    but I need to extract all the tables

    I got this for VB help

    Sub AllTables()
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    ' Search for open AccessObject objects in AllTables collection.
    For Each obj In dbs.AllTables
    If obj.IsLoaded = True Then
    ' Print name of obj.
    Debug.Print obj.Name
    End If
    Next obj
    End Sub

    this may be for printing filenames,

    is there any way I can use this obj.Name to get that in the resulting text file
    with
    DoCmd.TransferText acExportDelim, , obj.Name, "C:Temp\obj.Name.txt", True

    I am not able to use obj.Name.txt in the extracted file,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •