VBA : Need to retrieve data from database in Online Server

    Hi all,


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


    Task:
    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
    James

    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,


    Will

    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,
    Dennis

    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?


    Rdgs

    Warmest regards
    James

    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
    James


    What did you try & where did you fail ?


    The more information you can give the better :)


    Will

    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?


    Rdgs

    Warmest regards
    James

Participate now!

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

Register Yourself Login