Best Database for interaction with Excel ????

C

chs245

Hi

can someone comment on the speed of data retrieval out of Excel from a database ?

I'm considering the following options to warehouse my intraday stock data, but not sure which database to chose from. Currently considering MSSQL, mySQL or simply MS Access. The most important criteria would be the import speed into Excel (using VBA code) from the database.

Any comments from experienced users appreciated!\

Oliver
 
Quote from chs245:

...
The most important criteria would be the import speed into Excel (using VBA code) from the database.
...
More...

There's the rub, using VBA...

For "best" (aka speed) performance, regardless of back-end, you will need to use an appropriate ODBC driver and the properties and methods exposed by the driver. Not ADO, which is the friendly built-in of VBA. The more data being processed, the more important the use of the ODBC driver.

I would also suggest using module level (or public level if that's your coding style) variables for the workbooks, sheets, ranges, cells, etc which will be populated. This will prevent creation (and destruction) of Excel-VBA objects each time a sub or function executes.

Osorico
 
Quote from osorico:

There's the rub, using VBA...

For "best" (aka speed) performance, regardless of back-end, you will need to use an appropriate ODBC driver and the properties and methods exposed by the driver. Not ADO, which is the friendly built-in of VBA. The more data being processed, the more important the use of the ODBC driver.

I would also suggest using module level (or public level if that's your coding style) variables for the workbooks, sheets, ranges, cells, etc which will be populated. This will prevent creation (and destruction) of Excel-VBA objects each time a sub or function executes.

Osorico
More...

Many thanks to this info which is new to me.
OLiver
 
You must log in or register to reply here.
Back
Top