Become a TFP Supporter Pool Math Forum Rules Pool School
Results 1 to 17 of 17

Thread: Access database for tracking pool data

  1. Back To Top    #1

    Access database for tracking pool data

    (I'm not sure if this is the right forum for this, but I guess the moderators will move it if needed!)

    I've started working on an access database that allows me to track maintenance I do on the pool, results of water tests and expenses. It's still pretty raw, but if anyone is somewhat technically adventurous and has MS Office (with Access) I'd be happy to send a copy to anyone who is interested.

    I started off doing it in OpenOffice, but I needed to put together some macros and the available references on OpenOffice Macros is just to sketchy so I decided to stick with Access for now.
    ----------------------
    20,500 gal gunite pool w/ PebbleTec and infloor cleaning system.
    Sta-Rite System 3 cartridge filter.
    Houston, TX

  2. Back To Top    #2

    In the Industry
    X-PertPool's Avatar
    Join Date
    Jun 2009
    Location
    Exeter, PA
    Posts
    1,385

    Re: Access database for tracking pool data

    I use access for keeping track of customers, I would like to see what you have come up with.
    -Kevin
    X-Pert Pool Service
    x-pertpool.com
    X-Pert Pool on Facebook and @XPertPoolServic on Twitter
    22,000 gal / 16' x 32' / Vinyl / Hayward s244t /Pentair SuperFlo 1 hp

  3. Back To Top    #3
    kenmar's Avatar
    Join Date
    Apr 2009
    Location
    Schnecksville, PA
    Posts
    288

    Re: Access database for tracking pool data

    90% of my time at my job is spent developing applications in MS Access.
    I'd like a peek.
    20'x40' / 30,000 Gal / In Ground / Vinyl
    Pentair FNS 48 DE Filter / Pentair Challenger 1.5 HP Pump
    Jandy Lite2 400k BTU Propane Heater / Dolphin Dynamic cleaning robot
    TF-100 Test Kit / BBB
    Creator of SimplePool (BBB Friendly Android App) http://www.simplepool.net

  4. Back To Top    #4

    Re: Access database for tracking pool data

    Send me a copy or just post it here.
    22,000 Gunite, Cart Filter, Aquarite SWG

  5. Back To Top    #5

    Re: Access database for tracking pool data

    I'm not seeing a way to post attachments here or to send them through PMs. If you send me a PM with your e-mail address I'll be happy to send it along.
    ----------------------
    20,500 gal gunite pool w/ PebbleTec and infloor cleaning system.
    Sta-Rite System 3 cartridge filter.
    Houston, TX

  6. Back To Top    #6
    reebok's Avatar
    Join Date
    Apr 2009
    Location
    Lakeland, FL
    Posts
    1,268

    Re: Access database for tracking pool data

    below the place you type a reply is a place to attach images and files.
    16x32 21,000 gallon in-ground exposed aggregate, 1.5hp pump, 120 sqft catridge filter, birdcage, solar panels, aquavac tigershark qc robot.

  7. Back To Top    #7

    Re: Access database for tracking pool data

    OK.. I found it! I've attached the file to the thread. The zip file contains an Access 2007 database. If you have an earlier version, there may be a converter on the Microsoft web site or I can save a copy in an earlier version if someone wants it.

    With the standard settings, you will see a message bar at the top when you open the database informing you that macros are disabled. I have one macro right now that calculates the CSI when a button is pushed so if you want to use that, you'll have to enable macros.

    I don't have a salt water pool, so I don't have fields for tracking any parameters unique to those, but it is easy enough to add those if someone wants to. There's no documentation, but hopefully everything is relatively self-explanatory. (so far this has just been a "Weekend" project, but I'll probably build on it some as I have a need.
    Attached Files Attached Files
    ----------------------
    20,500 gal gunite pool w/ PebbleTec and infloor cleaning system.
    Sta-Rite System 3 cartridge filter.
    Houston, TX

  8. Back To Top    #8

    Re: Access database for tracking pool data

    I have an updated version of the database available with some improvements. Thanks to a little help from kenmar on the board, I've discovered a few things...

    1) I've split the database into two files.. one with the data and the other with the "front end" stuff, so if I add reports or modify forms in the future, you can just replace the front end without affecting your data.
    2) I've discovered that you can download an Access runtime executable from Microsoft that allows you to use this without owning Access.. the link for this is in a text file in the zip file.
    3) I've saved the file in Access 2003 format so more people can use it.

    I wrote this mostly for my use, so I haven't written any help for the tool, so if you have questions, feel free to ask.

    The file is too big to include here, so you can download at the following link:
    http://hyperfileshare.com/d/32113961 (new upload link)

    If you have trouble with the download, let me know and I'll send an e-mail.

    A screen shot of one of the screens is attached.

    [attachment=0:3buvkav4]Screensho2t.jpg[/attachment:3buvkav4]

    [attachment=1:3buvkav4]Screenshot.jpg[/attachment:3buvkav4]
    Attached Images Attached Images
    ----------------------
    20,500 gal gunite pool w/ PebbleTec and infloor cleaning system.
    Sta-Rite System 3 cartridge filter.
    Houston, TX

  9. Back To Top    #9

    Re: Access database for tracking pool data

    Installed it and got 'c:\users\curtis\pooltracker_be.mdb' is not a valid path.
    14,500 inground gunite
    7' connected spa with overflow waterfall
    Goldline salt water chlorine generator
    Hayward 4 cartridge filter
    Hayward 400K heater
    Completed January 07

  10. Back To Top    #10

    Re: Access database for tracking pool data

    Thanks.. let me see if I can fix that.
    ----------------------
    20,500 gal gunite pool w/ PebbleTec and infloor cleaning system.
    Sta-Rite System 3 cartridge filter.
    Houston, TX

  11. Back To Top    #11

    Re: Access database for tracking pool data

    I think I've got a solution to the path problem. I've updated the download URL above and it points to the new version.

    You'll need to do the following after downloading the new zip file:
    1) Extract the files in the ZIP file to the same directory somewhere (Access has gotten a little weird.. it may need to be under 'My Documents' to work right)
    2) Open the PoolTracker.mdb file
    3) If you get a message at the top about content being disabled, click on that and enable macros (another real nuisance thing about MS Access)
    4) Select the "Enter Setup Data" button from the main menu.
    5) Click the "Relink tables" at the bottom of the switchboard
    6) After you get a message box telling us that is is done, close and re-open the database.

    After that first time, you shouldn't have to do it again (although Access will make you enable macros every time).

    If you're still having problems, send me a PM and we'll hook up offline and work out any remaining problems and I'll update the solution here as needed.
    ----------------------
    20,500 gal gunite pool w/ PebbleTec and infloor cleaning system.
    Sta-Rite System 3 cartridge filter.
    Houston, TX

  12. Back To Top    #12

    Re: Access database for tracking pool data

    Curtis,
    I clicked on the updated link...and I was unable to locate the file on the download page. It had a a note about the file being removed. ...am I looking in the wrong place? Thanks!
    Travis & Kathy
    16x32 AGP (extra return outlet installed), Pentair Dynamo Pump (1.5HP, 60+ GPM), Sta-Rite Crystal Flow Sand Filter (300 lb), Clear Diamond Solar Blanket & Reel, Pool-Skim
    Aqua Trol SWG (also controlling the pump), TF Test Kit. (Everything self installed, up and running on 08/02/09)
    Enjoying the TFP site and appreciating the infinite knowledge of it's members... and I'm slowly learning how to maintain our new pool the BBB way!
    Four Winds Spa (love the tub...hate the filtration system). Best water quality in four years using the BBB method

  13. Back To Top    #13

    Re: Access database for tracking pool data

    We'll try one more time. I may have to find a different file sharing service! As of right now, the link above is working now. We'll see how long it keeps working.
    ----------------------
    20,500 gal gunite pool w/ PebbleTec and infloor cleaning system.
    Sta-Rite System 3 cartridge filter.
    Houston, TX

  14. Back To Top    #14

    Join Date
    May 2008
    Location
    central Florida
    Posts
    27

    Re: Access database for tracking pool data

    I was able to download and extract the zip file from your download site. I was only able to accomplish steps 1 and 2 in your above instructions -- I never got the macros message.

    however, I'm having the same problem as vegas_dp with the invalid path. it appears you have somehow hard coded the path to Windows User 'curtis' as the message reads:

    c:\users\curtis\documents\pooltracker2\pooltracker_be.mdb

    it appears everything after '\documents' in the file path is correct for my case, as I placed the dB file in a folder called 'pooltracker2' on my desktop.

    also, when I click the 'Enter Setup Data' button I get a pop up for 'Enter Parameter Value' with a text field for variable 'TempVars!SwitchboardId' -- not sure what I'm supposed to type in that field. if I leave the field blank I get the Switchboard -- but it's blank -- nothing but 'Pool Tracker' text at the top. if I type in a '1', the switchboard shows up with 4 checkbox items displayed: Test Log, Maintenance Log, Expenses, and Maintain Setup Data.

    I'm using Access 2003 SP3 on a Win XP pro machine.
    12K gal PebbleTec, 1.5 HP 1-spd P2RA series Sta-Rite pump, Hayward cart filter, Pool Pilot SWG, spillway, no heater, sunny, Taylor K-2006 test kit.

  15. Back To Top    #15

    Re: Access database for tracking pool data

    Yes. It appears that Access forces a hard coded path when you have links between two databases. The button to re-set links is supposed to re-calculate those paths based on where you have the files stored on your own local machine. Since it sounds like you're running the full version of access, you can try to view the macros and run the macro by hand to re-link the tables. Also, you should be able to right click on a table name and bring up the linked table manager and re-set the paths by hand. It is possible though that even though I have the database save in Access 2003, that something about the macro only works in Access 2007... I'll have to try to get hold of a 2003 runtime and try things out there. Try these and send me a private message to see how it works.. maybe between us, we can get it worked out and then just post the solutions for everyone else to see rather than boring everyone with all of the details. Also, I'm not sure why it's prompting for SwitchboardID, but you might try a 2 there (the one I'm using was the 2nd one I created)... nothing is ever easy with Microsoft! Thanks for your patience.


    Update: I loaded Access 2003 on another laptop and tried it there and noticed the following:
    1) The "Enter Setup Data" button will display the switchboard listing the forms if you enter '2' when prompted, but none of the buttons work. Apparently something is incompatible between 2003 and 2007. You can view the forms from the 'Database' window and open them manually. I can fix this by eliminating the switchboard which is something I wanted to do anyway.
    2) You can select 'Macros' from the Database window and run the RelinkTables macro by double-clicking it. If you extracted both databases to the same directory, this will fix all of the paths on the tables and things should start working after you close and re-start the database. This only has to be done once.
    3) Lots of fonts and colors are messed up and things look ugly... I'll have to go back through and choose more standard fonts that are available on all systems.
    4) The buttons at the top of the TestLog window don't work.. again you can run those forms and reports manually.

    I can fix all of these things, but I would suggest that if you're still interested, try these things and at least poke around a little bit and see if you think you're interested in using the database. If there's interest, I'll get these taken care of... like I say, I built this mostly for myself but if others are interested in using it, I'll work with you to get it working.
    ----------------------
    20,500 gal gunite pool w/ PebbleTec and infloor cleaning system.
    Sta-Rite System 3 cartridge filter.
    Houston, TX

  16. Back To Top    #16

    Join Date
    May 2008
    Location
    central Florida
    Posts
    27

    Re: Access database for tracking pool data

    No. this is fine curtis. I performed your steps above and everything relinked fine to the other dB tables. I was planning on modifying your tables slightly to allow for my SWG data. I may even put this on my home server and build some front end ASP web pages so I can access/modify the dB from any of the computers on the home LAN.

    I've been meaning to do something like this for a while. I want to take all the data I have in an excel SS and put it in a dB. I'll use your work as a baseline and modify it to fit my specific needs. thanks much!
    12K gal PebbleTec, 1.5 HP 1-spd P2RA series Sta-Rite pump, Hayward cart filter, Pool Pilot SWG, spillway, no heater, sunny, Taylor K-2006 test kit.

  17. Back To Top    #17

    Join Date
    Jun 2014
    Location
    Michigan
    Posts
    65

    Re: Access database for tracking pool data

    This is great. TKS Curtis. Was going to start my own in Access but YET again this board provided a push in the right direction. Does anyone have an update to this/new version they would like to share ?

    TKS Again !

Posting Permissions

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