Exporting multiple excel files from a single table

I have seen this explained, my issue is the time it is taking to export.  I'm exporting around 200 files with probably 270 records in each.  It's taking about two minutes per file.  I need to find a fast export method.  Any help would be greatly appreciated.
gmerp2011Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Please post your code.
gmerp2011Author Commented:
Public Function NExportToExcel()

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim strSQLSelect As String
   
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("q5c multistore")
   
    strSQLSelect = "SELECT multistore.* FROM multistore"
   
    Set rst = dbs.OpenRecordset("SELECT STORE FROM multistore", dbOpenSnapshot)
    With rst
        Do Until .EOF
            qdf.SQL = strSQLSelect & " WHERE STORE = '" & rst!STORE & "'"
           
            DoCmd.OutputTo acOutputQuery, "q5c multistore", acSpreadsheetTypeExcel7, "P:\database\Store SKU\Export\" & Trim(!STORE) & "\" & Trim(!STORE) & "-NEWORDER-" & Year(Now()) & "-" & UCase(MonthName(Month(Now()), 3)) & "-" & Format(Now(), "dd") & ".xls"
            .MoveNext
        Loop
        .Close
    End With
    Set rst = Nothing
    Set qdf = Nothing
    Set dbs = Nothing
   
End Function
PatHartmanCommented:
Rather than modifying the SQL inside the loop, change it so that it gets the store value from a hidden form field and then populate the hidden form field in the loop.  What you are doing is causing the query to be saved and compiled for each execution and although that is very quick, inside a loop, the overhead adds up.

Select ... From ... Where STORE = Forms!myform!myhiddenstore;

In place of qdf.SQL = ...
Use Me.myhiddenstore = rst!STORE

I would also format the date part outside of the loop and save it to a variable.  Then inside the loop refer to the variable.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

gmerp2011Author Commented:
Thank you.   I'm pretty new to using code in Access  so I will give it a shot.

how would I switch the Store in the form?
PatHartmanCommented:
Just create a new control and set its visible property to false.  Name it txtSTORE.

Then inside the loop, instead of changing the SQL, populate txtSTORE.  The query will do the rest.

Me.txtSTORE= rst!STORE

So instead of the query having a hard-coded value which you have to update 270 times, the query uses a parameter.  Since using a parameter doesn't change the structure of the query, it doesn't need to be recompiled.  The parameter is simply a data value and whether you are looking for store 12 or store 876, the query does the same thing, it searches the index and brings up the requested records.  When you were changing the actual SQL, Access couldn't know that you hadn't actually changed anything except the ID of the record it was looking for and so it thought it needed to calculate a new execution plan because it had no way of knowing that what you changed wouldn't make a difference.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gmerp2011Author Commented:
So after making the modification to the code only shaved off about 30 mins from complete export.  The solution ( now takes about 97 seconds) was to create a table based on the store and then export the table and then loop and move on to the next table.
PatHartmanCommented:
I thought you already had a table based on the store???  What is the table named MultiStore?  If MultiStore had more than one row per store then you were creating each export MULTIPLE times and that is what was taking so long.  I'm not sure how we were supposed to figure that out.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.