Trying to find best solution to convert custom estimating program.

Posted on 2006-03-28
Last Modified: 2010-04-23
Some background.
This is not a real "application" but rather a set of spreadsheets with macros that was originally created back in the stone-age of DOS with Symphony.
It was created by my father for our family business to do price quotes. When he retired in 96, I took over his main duties of "one-man-geek-department" and so it was my responsability to keep these spreadsheets updated. At the time I took them over, we were converting all the machines to Windows, so I figured why not convert these as well. I had more experience with Quattro Pro then with Excel, so I converted them all to Quattro format, macros and all. This is how its been for the last 9 years basically and over the years some of the formulas that work in the background have kinda gotten out of sync with current conditions such as prices of itmes and cost of labor. So it has come down to being told to fix it by my boss and instead of trying to through all the spreadsheets with a fine-tooth comb, I would like to completely revamp the whole thing.
I have inlisted my father to recreate the original spreadsheets, this time in Excel format.
I would like to create my own front-end to this, but am unsure what would be the easiest/end-user friendly way to do it.
I have thought about doing it in HTML/DHTML/CSS and Access/VB.
I am not up on these languages but learn quick. I did go to tech school to learn programming, but that was back just before PC programming was the norm, so I learned mainframe style progs like COBOL.
What do you guys think would be my best solution to this, and no, I do not want to farm this out, I want to get the experience for myself. (I've been telling myself I will learn these languages, but have been lazy about it :()

Question by:MtnDew2k
    LVL 5

    Expert Comment

    I would say go with .NET for sure, so you came to the right place.  As for web interface vs windows forms, it is just a user preference there, I don't see an advantage of one over the other, except with web, you will need some sort of server, but that comes with the development tools (however, you will need either a dedicated web server, or to install the dev tool on each computer you run the web interface on, or run it on IIS on XP Pro)  .NET allows you to open and manipulate Excell, which means once the spreadsheet is ported over to excell, you are basically making a user interface and that is it.  Pretty easy first project!  This will show you alot of the basics of .NET also.  And you can use access as a backend if you are wanting a db, as .NET makes it very easy to work with data via datasources.  You can get the express versions of either the web or windows development here and those are free tools that will let you get started and easily complete this project.  good luck, as I know this will be fun!
    LVL 10

    Expert Comment

    >>and no, I do not want to farm this out, I want to get the experience for myself

    be careful here or you will find that you are spending more time supporting these spreadsheets than doing your actual job.  Farming it out is the best solution - you get what you want with the best solution.  The developers out there know the best way and other tricks and tips for getting the result.

    Having said all that, the "best" solution depends on what you are trying to achieve and who your end users are.

    if the end users are internal and they will be accessing the data from within your company fireall, I would suggest a Winforms solution - you get a much richer and more controllable user interface;  in .NET 2.0 you have the "ClickOnce" deployment that makes upgrading the app a breeze.  
    If users are accessing the data from outside the firewall you probably will have to create a web application (ASP.NET).

    For the database, again it depends; how many concurrent users, how many transactions (writes/deletes/updates) per day/hour/minute.

    for full scaleabilty use SQL Server though access will do to start with.

    Get yourself some good .NET books and work through the examples in them before trying your own solution.

    I re-iterate, if you cost your time and loss of normal productivity trying to do this against the cost of getting in a contractor I think you will find that it is worth farming it out.  Once the code has been written you can then take over the support and learn from some good code already written.

    LVL 6

    Accepted Solution

    I am a .net programmer by trade and I still prototype in Microsoft Access.

    If you program it in .net you will still need to program a database backend, preferably with sql server although there are free databases, reporting will need to be done via crystal reports, in my honest opinion your using a hammer to crack a nut.

    Use Microsoft Access...

    You get all the advantages of multi user support and concurrency control (updating in different places)
    It has a built in report generator
    It has a built in input form generator
    It has VBA in the background in case you want to get deeper into programming.

    Its a quick efficient system for developing in, if you still want to get deeper into programming afterwards you will have a good grounding in database design, object properties, coding and will ahve a solid proven design to code to.


    Author Comment

    I had originaly intended to do it in Access BTW, but thought maybe .NET would make it "easier" to make it future-proof. (ha, yeah right)

    What would you suggest as far as reading material to get me off on the right foot?

    Thanks to all for answers.

    LVL 6

    Expert Comment

    Programming is such a massive subject area you won't be able to specifically read a book that will help much....

    Heres the steps you should take

    Read up on relational databases, a quick overview will do until you are familiar with joins, foreign keys and primary keys.  Stick with this until you have a database structure which will cope with what you need.  Do not over engineer at this stage.

    Milestone ... Design your tables.

    Read up on queries, so you can select records according to various criteria and also update various fields in a database according to those criteria.

    Use this knowledge to design a few simple queries and reports

    Milestone ... Design queries

    Programs usually follow very similar guidelines no matter what the applications scope is.... for each table in your database you will need a form to ....

    Enter the data
    Amend the data
    Delete the data
    Examine the data

    Access will allow you to build a form using a wizard which is automatically connected to the data, this handles entering and amending the data.  It will also give you some controls to delete the data but these are non intuitive so I would suggest putting a button on yourself to do this.

    So pick your top level table (customer?) and design a form that will allow you to enter the data, update it and delete a record.  You may need a top level form with a list of customers and a button next to each record to drill down into the customer detail.

    Usually these forms follow a hierarchy Customer >> Order >> Order Line etc, just keep following the hierarchy until youve stepped through the application.

    Refer to the Northwind samples that come with Microsoft Access but do it to solve a particular problem (how do I click a button to get such and such a form) the other useful reference for you is which is a massive knowledge base.

    If it becomes unmanageable break it down to its simplest parts and hit them one at a time.

    Sorry I cannot refer you to literature but like I say it is to huge an area.

    One suggestion though, when putting some code behind a button you will be given the option of using VBA or Macro, if you are interested in learning VB asp etc then always use the VBA option as the syntax is identical(ish) to VB rather than the Do-it-all for you macro option.

    Hope this helps and good luck.  Programming is a bug and once bitten it will take years to get it out of your system.


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Article by: Kraeven
    Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now