Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009

    Unanswered: Reporting into Excel EXtremely slow

    I'm an end-user of a 3rd party Access created database. Each month I pull up the monthly reports through a basic report/query selection menu. Processing was taking upto 15 minutes. No big deal. The problem is the report generation. It would start a new Excel worksheet and dump the report data (and formatting) basically line by line. For simply queries (ie specific customers, small data ranges or limited products) no big deal but the regular monthly report, about 7000 lines long in Excel 2002 would take about 4 hours on my machine (Dell Core 2 Duo laptop 2 gb ram).
    We were fortunate to be able to move the database to a new SQL server now it takes about 4 minutes for the processing of the query but still the same slow pace to dump the data into Excel.

    As stated - 3rd party creation. I can't 'break in' to get the code. Is there anything I (and other parties) can do to try to speed this process up - some SQL/ODBC connection, some tweaking anywhere? Or are we 'philips'ed?' until someone is hired to trim/recode the actual macros?
    Suggestions - hints - voodoo dolls - pistols at 10 paces? Basically: Options?

  2. #2
    Join Date
    May 2005
    I'd suggest a few things (that I myself usually check) that can cause slow performance in general, maybe one might apply to you:

    There's an interesting Microsoft Article on subdatasheets that might help.
    Granite Counseling has a nice checklist I found once that's a good reference. In particular I would suggest looking into their Persistent Connection article.
    Otherwise, I think pkstormy has an article in the Code Bank regarding slow performance.

    Me.Geek = True

Posting Permissions

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