VBA : Need to retrieve data from database in Online Server

  • Hi all,

    I have an online database, in Access 2000 format, Stock. mdb.

    i need to download the data in the database into MS EXCEL, MAKING use of MS EXCEL as an interface and also analyze the data in pivot table. (everytime i click on update button from the Excel Interface, the data would be downloaded from the online server into the Excel offline)

    First of all, how do i create that link from Online server to my MS Excel interface? Secondly, do i need to create a so called "cube" to contain all the data in MS Excel?

    PLease help me..

    Warmest regards

  • You should be able to accomplish everything you are wanting using MSQuery.

    Goto Data>Get External Data and set up the connection to your database following the wizard steps.. you can either output the data to excel or directly to a pivot table - once the link is established, you will be able to update the data just by clicking on a "Refresh External Data" menu button on the "external data" menu (which will be greyed out until you actually have established an external data connection)...

    This is probably the most straightforward way of accomplishing your task without some hefty VBA coding, although if you want to explore that option, Dennis has a couple of excellent data retrieval posts using VBA driven SQL to improt data in the Advanced Excel Integration forum further down the page...

    Hope this helps,


  • I agree with Will and we can achieve the same result using ADO/VBA but give the suggested approach a chance.

    No, there is no need for a cube unless the amound of data is large and that You face an unacceptable time for retrieving the wanted data.

    As for using ADO it require a basic understanding for the concept.

    Kind regards,

  • Hi Dennis,

    I would be downloading the sales data, which would be 1000 + records.

    Is these amount of data considered huge?
    Need a cube for this?


    Warmest regards

  • Hi Dennis

    I tried, but I dun seem to know how to do it.

    I wan to connect to the web server's MS Access database..

    pls help

    Warmest regards

  • What did you try & where did you fail ?

    The more information you can give the better :)


  • HI WillR,

    The steps u provided me was to connect to offline database.

    I have a database that's residing on a web server. How to make my connection to it?


    Warmest regards

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!