Hi all,

I have a large access database from which I need to extract specific fields to use in claculations within an Excel VBA program. I have set up the ODBC connection but am unclear about how to express the SQL query within the vba to get the required field. Also would it be quicker to pull out all the information first and then store this in an array or to pull out each piece of data as required in the calculation (assuming no loops)?

I know this could be a biggy but any help is greatly appreciated.

Cheers

Chris

Firstly, how much data are you thinking of pulling out of Access? It might be that the simplest option is to use the 'External Data' (MS Query) tool to pull the data onto a sheet and manipulate it from there. Using MS Query will also generate the required SQL code, which you can then either use 'as is' in VBA, or as a template for creating queries on the fly. I'm assuming you are going to be using ADO as the actual access method within VBA.
As far as performance is concerned, the rule of thumb is to pull as much data as you can in one go. This minimises network traffic, and uses the bandwidth more efficiently. Even if you're not on a network, the reduced number of disk reads improves performance.

HTH

Chris

There are two secrets to success in this world:
1. Never tell everything you know

Check out http://www.zmey.1977.ru/Access_To_Excel.htm and http://www.greggriffiths.org/webdev/both/excel/ which should provide you with plenty of information.

Sorry been away thaks both for the help