Best Database for interaction with Excel ????

Discussion in 'Data Sets and Feeds' started by chs245, Jun 2, 2006.

  1. chs245



    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!\

  2. novais


  3. 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.

  4. chs245


    Many thanks to this info which is new to me.