Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Unanswered: Use Access for utility company?

    Hi - I have a client that is starting a utility company. Long story short is they have an appliance that they deploy at client locations. This device monitors/captures 100 data points (information about the appliance - heat, rpm, flow, etc) every minute and is stored until manually pulled over the web. This information then serves as the basis for a couple things:

    1) Determining usage so they can accurately invoice the client
    2) Monitoring the system - if a data point is out of whack, it can raise a red flag

    The client has written an access DB with some VB functions that, on the surface, appear to handle 90% of the requirements. They have been using this db for the past year and a half to successfully run the business, and the app has largely been untouched for about a year.

    There are a few issues present that my client wanted me to focus on:
    1) The DB exists on a laptop! (yes, they back it up from time to time but yea, it lives on a laptop!)
    2) The process of retrieving the numbers and engaging the appliance is entirely manual, save a couple of critical alerts that have been set up on the device

    My initial thought was "A production system in Access sitting on a laptop?! Let's port it to a cloud based mysql db!"

    After thinking about it, I realized that Access may be the perfect home for it, especially if it's already been serving the purpose for the past year and a half - if it ain't broke, don't fix it too much!

    The client is at a point where they are willing to consider migrating their home brew system to another platform. That was my initial thought, but I'm writing this post because I can't justify the migration yet in my mind!

    My questions:
    Is Access a safe home for a growing utility company to run their metrics off of?
    Where would you house the DB aside from a laptop? A cloud based VM? Win7 or server 2008 or... or...? Any recommendations for a good partner to house this for us?
    Any other thoughts?

    THANKS - I have not been active in this community in many years, but I always value the thoughtful feedback that I have received, indeed DBforums helped me grow both personally and in pay scale for a few years!

    Mitch

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access should be fine for that task
    subject to
    ..the db should be split into a front end (the user interface)and a back end (the data). this makes making changes and backups easier
    ..the data should be backed as as frequently as the business requirements needs and then some.

    I'd also suggest writing an audit log as part of the application so that the system developers / owners can see what the users are actually doing, especially if your company invoices a third party based on the third parties data

    the fact the app runs on a laptop doesn't matter. all that does amtter is that its treated as 'serious' data (ie it means the lap top contains sensitive data and there fore you must take into account prudent application support issues and cater for the fact that at soem stage the laptop will get broken or stolen.

    bear in mind Access is actually a frnt end designer that copmes with a bundled data storage mechanism. you odn't have to use the bundled stroage mechanism if youi dont' want to.

    there is nothing stopping the data beign stored in a server product, and it probably will have to be eventually. bear in mind Access (like any piece of software / hardware) has internal limits. I think in Access its 2 Gb of data.

    you may hit limitations if there is a huge amount of analysis of data going on eg periodicitiy, correlation and so on

    as to whether a laptop is 'good enough' for a companies fudnametal operations data, thats your clients call. If it were me I'd stay with Access for now, split it into a fritn end and back end.
    decide if the laptop is because thats all they have or needed out in the field. it may be smarter to capture the data locally and then send to a central server
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Ha! I think you helped me a bunch "back in the day", Healdem! Thank you so much for your reply.

    I agree, split up the DB for separate front/back end... Also, the audit log - i had not thought of that.

    Any problem you can see with moving the data to a cloud VM of a server image and running the front end from a local client, provided the client has a cable internet connection? I would set up a VPN between the two locations for access to the DB. Something software based like Hamachi.

    Thoughts?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    VPN & Access is a bad idea
    Access over anything other than a local machine or LAN bad idea

    you'd have the same issues with any file server based product
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Thanks a lot, Healdem - Based on a conversation with the client, it seems we are going to look at migrating it to a SQL based web app. As always, I appreciate your help.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    erm Access is an SQL based app, you may not be using SQL but under the bonnet its is an SQL app.

    the problem is shifting data up and down a network when using the default storage mechanism used in Access which is a product called JET.
    Access talking to a properly designed server db back end:- no problem
    A properley designed Access talking to a file server back end: cabn get away with a lot of things but not over a VPN
    Access talking to a file server back end, no matter how well designed: -big problems
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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