Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    16

    Unanswered: exporting large query

    I have a query I am wanting to export into some format.
    The query has results totaling around 130000 records. I know Excel has a limit of around 65000 rows and 256 colums it can handle, so I was wondering what the best approach would be to export these query results and the method for doing so? Any help would be greatly appreciated!!

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Could export them to a txt file (DoCmd.TransferText [transfertype][, specificationname], tablename, filename[, hasfieldnames][, HTMLtablename][, codepage])

    But if you want them into excel, you'd have to write a piece of VB that selects the first 64000 records. Then insert a new page and do the next 64000..and so on (not the best way)

    I dont know the limit of Access exporting records, but if your export is very large it wont export, without intervention.

    Does this help ?

    I could write some code if you need.

    Ken

  3. #3
    Join Date
    Feb 2004
    Posts
    16

    thanks

    Ken,

    Thanks for the advice...

    I have not ever done an export of this size nor one to a text file that you speak of.

    If I exported to the text file, how would the formatting work?

    Could you give me an example for using the function you displayed, please?

    Thanks very much for the help!

  4. #4
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Ok

    I'm working on a project that uses C.A.A Airport Codes. Its freely available so the text file you can have !

    I have a table in my database called Tbl_Clean_Airport_Codes_KH

    The command line below exports the table to a location, in this case being lazy c:\Cavall.txt

    The last piece I included True is to export the headers as well.

    DoCmd.TransferText acExportDelim, , "Tbl_Clean_Airport_Codes_KH", "C:\Cavall.txt", True

    You'll see from the textfile, that there isnt really any formatting at all. It can be imported with ease to other applications.

    If this isnt what you are trying to do, I will write you some code to output the table to excel, if formatting is an issue.

    Hope it helps

    Ken.
    Attached Files Attached Files

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If this is a one time thing or you can do it manually each time you need it, just highlight the query, choose File|Save As/Export (might be different in more recent versions) and use the export query to export it to a file. The wizard will allow you to pick a the type of file you want to save it to. It defaults to Access, but you can change it to text. When you choose text it will allow you to pick a Fixed file or a Delimited file. The size of the fields, the delimiter and alot of pther things. Once you get the hang of that, then you can use the TransferText method. Another way you can do it is with a macro. I have found it helpful to set up a macro and get it working then translate it into VBA. When using Transfer Text and TransferDatabase some of the parameters can be a little confusing.

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You can do it using a RecordSet , opening a file Open "C:\Temp\Export\yourExport.txt" For Output As #3 for example.
    Then loop through the RecordSetwritting its value to the .txt file

  7. #7
    Join Date
    Feb 2004
    Posts
    16

    thanks

    I just used the export wizard and exported as a text file....


    Thanks for the help

Posting Permissions

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