Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Ottawa, Canada

    Unanswered: Upgrading to Access: A costly mistake


    As a Consultant in Business Data Management I often have to develop applications in Excel to replace applications developed in Access.

    Are you planning to UPGRADE to Access?
    Are you working with Access and why?
    Do you know the SUMPRODUCT function in Excel to automate all your reports?
    Do you know about the VARIANT in VBA for Excel that allows you to work with millions of data and execute millions of calculations in seconds?
    Do you know about SQL in VBA that allows you to extract whatever data from whatever central database, ERP or finance and accounting applications?

    See my article on Access and Excel at:

    Looking forward to reading you thoughts
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick

  2. #2
    Join Date
    Oct 2003
    Howdy, Pierre. One caution about Variant in VBA is that it can easily become unmanageable, taking on the latest "value" of what precedes, or may acquire an unexpected value. So, sparing use of Variant can work well, overuse can lead to serious troubleshooting issues.
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Dec 2003
    Ottawa, Canada



    I use the VARIANT to avoid working on the worksheet itself.

    I bring the whole worksheet into a variable of the Variant type with:

    varMySheet = Sheets("SOansSO").CurrentRegion

    I then do what I have to do and bring the results back to the worksheet:

    Sheets("SOansSO").CurrentRegion = varMySheet

    And I never had a problem.

    I have just finished two complex applications for Bombardier (Financial Analysis) and KPMG (Reporting application generating 2,600 reports using 26,000 text files) using this approach.

    For example, going down 50,000 cells to enter a "1" in each with a For/Next on the worksheet takes 33 seconds in a Variant it takes less than one.
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick

  4. #4
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 10
    I always look @ what the Customer Want them decide what product to use

    I have just finish one

    I use MSaccess get the Data
    them MSaccess Creates Excel file
    msacess Send the Excel files out the user to there customer who Full them In
    send back Msaccess them read the excel file and put the Data back in the data base

    my rules
    excel does the Number
    MSaccess does the Databasing
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  5. #5
    Join Date
    Dec 2003
    Ottawa, Canada

    I am certified (MOUS) in both Excel and Access.

    My client has the final word and I have to make sure that he is well informed before making a decision.

    Developing in Access is more expensive then developing in Excel and the client becomes more dependant on the COnsultant if the development is made in Access.

    In most of my projects the data is in some central database.

    I have developed a sophisticated costing application for SCI (Houston) where millions of records are extracted from the central database using multiple criteria in VBA for Excel.

    The pricing workbooks are then sent to 500 directors who do the costing and who send back their workbooks to the head office.

    The data is then reformated to be uploaded in the central database (HMIS)

    There are so many ways to get the data directly in Excel from any central database, ERP and other central applications. In some of the applications I even work with stored procedures executed on the server through VBA.

    Basically when it can be done in Excel I avoid Access and this occurrence has never happened in the last 15 years.

    The great aspect of using Excel is that I can mentor my clients in Excel and they can develop reports themselves after a short while.

    They are better than me at developing great reports because they know their data better than I.

    They call me only if the applications are too complex for them and yet I am overloaded with complex and interesting projects
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts