How to make fetching and displaying of data from dtabase to workbook faster??

[divya]

I have a small querry:-

I need to get data from the database and display in the excel sheet .
The user might ask for 2 yrs data or some times even more ..therz a
form he fills based on which I querry the database.. I want the
fetching and displaying to take minimal time...

I wanted to know of this two approaches which would be faster...

1. Make a temporary array and while moving through the Recordset data
in the do while loop copy these values into the into this temporary
array . And then later copy the values from this array to the
workbook.. Advantage( I need to only change the display procedure if
client wants te data to be displayed in other format ... need not
meddle with the procedure which fetches the data.. But found this takes
a lot of time...) and array would be a 2 dimensional matrix..

2. Instead of making a temporary array , while looping through the
recordset copy the recordset data into the workbook at the same instant
instaed of copying into temp array and then cal a procedure to copy
this array to the workbook ... Will this approach be faster than the
one mentioned above .. I want it to take less time for dispalying and
fetching.... Plz letme know which is a better approach ..

Kindly let me know any functions or properties like turning the display
alerts to false that can be used for faster execution...


Cheers !!!
Divya..

 

[Jay]

Hi Divya -

Suggestion: Before setting out on VBA code to do the job, check the Data
menu in Excel. There is an option there that may help -- 'Import External
Data'. You can point directly to a table or existing query in your external
source (like Access) and the data imports at lightning speed.

If that doesn't apply to your application, the second programmatic option
that you describe is generally much faster in execution than the first. I'd
pursue that approach if necessary.

 

[divya]

Thanx a Lot Jay for giving the idea of using the "Import External Data"
from the data menu.. It actually imports at lightning speed ... I hav
recorded a macro and recorded the
VB code which does this .. Now based on the the user's input in the
form I will make a query and replace it with the one I used while
recording ... I hope its tht easy ... But if tht doesn't work I will
manually copy records using the second method as u said its faster...
Once again thanx a ton Jay...

Cheers !!!
Divya...