Results 1 to 5 of 5
  1. #1
    kestefon is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    108

    Export Query Into Multiple Text Files (Due to Size Limit)


    Every month I have to update my database, do some queries, and export the result to a massive delimited text file. As of now, everything is able to fit into one text file, but the size jumps significantly each month. Last month it was about 2 gigs, this month it's 3 gigs, and I suspect we'll reach some size limits next month. (If I'm not mistaken, text files have a 4 gig limit.) What would be the most efficient way to solve this problem? Is there a way to "split" a single query so that it exports into multiple files with headers, or would I have to create multiple queries?

  2. #2
    BluffMeAllIn is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Dec 2013
    Location
    Canada
    Posts
    21
    Hi Kestefon,

    Just curious as to the reason behind needing to export so much data OUT of a database. I can't imagine anything being done with such a large file other than maybe being imported into another database.

    I would also constitue that if the data is growing that much from month to month that maybe there are better options than ms access, I am not familiar with limits or the newer versions of access as just getting back into using it however would think if the data is possibly groing by 1GB/month that it will eventually be causing problems.

    On the note of doing this if you are running basic access queries, and not through VBA code then I think its unlikely that it could be done from a single query, but certainly is likely you could produce multiple queries with diff where criteria to break it down into smaller files. From vba code essentially there would probably be multiple ways to go about it.

    If through vba code let me know as I could certainly be of more assistance.

    Thanks,
    Dave

  3. #3
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Bluff is right. If your database is growing that significantly, there's likely a design error.

    I remember a time when there was a report being delivered to a manager's office on a daily basis. The job started having issues at some point, and the manager finally screamed when the daily greenbar was taller than the manager. The job had been blowing up each day, displaying for documentation purposes all the data that had not been processed and would be recycled the next day, and no one noticed the problem despite the output report growing by a foot a day.

    Investigate what the query is reporting, and what actually happens to the output file. Find out what the business rules are. Find out what process this supports, and what's going to happen at the far end when the data becomes unmanageable.

    There have been times when a user asked me for an extremely complicated report, that would take hundreds of hours of coding and produce piles of paper, and when I drilled down to what the user really wanted, it was a couple of calculated numbers that would (incidentally) be put out in the totals. The user wanted those numbers to manually fix a different report that was always off by that amount. The real business need, incidentally simpler and cheaper, was for the other report to be fixed. Piece of cake.

  4. #4
    kestefon is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    108
    The increasing data size is expected, because each update contains a month's worth of data and millions of records.

    I'm not completely opposed to using multiple queries...I'm just concerned about it becoming cumbersome and tedious. Would you recommend VBA or multiple queries for this type of thing?

  5. #5
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Then I assume the 2G last month 3G this month was a theoretical example rather than real numbers. The 50% increase in size was what set off loud alarm bells.

    Probably the most efficient method is to use VBA to drive a single query/export, parameterized into chunks.

    Although... I wonder why you are re-exporting historical data. Is the prior month's data being updated in the current month?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import / Export Multiple Files & Tabs
    By maggiemago3 in forum Programming
    Replies: 6
    Last Post: 08-21-2013, 11:20 AM
  2. Replies: 11
    Last Post: 12-20-2012, 12:30 PM
  3. Export table to several text files
    By ShadeTree in forum Programming
    Replies: 3
    Last Post: 04-24-2012, 08:02 AM
  4. Import Text File Size Limit
    By wfbp in forum Import/Export Data
    Replies: 1
    Last Post: 11-04-2010, 09:05 AM
  5. Replies: 6
    Last Post: 10-14-2010, 08:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums