Access database for tracking pool data

Jump to latest Follow Reply
C

CurtisT

0
Jul 2, 2009
59
(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.
 
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.
 

Attachments

  • PoolTracker.zip
    74.5 KB · Views: 55
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]
 

Attachments

  • Screenshot.jpg
    Screenshot.jpg
    83 KB · Views: 321
  • Screensho2t.jpg
    Screensho2t.jpg
    56.3 KB · Views: 322

Enjoying this content?

Support TFP with a donation.

Give Support
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.
 
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.
 
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.
 
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!
 
You must log in or register to reply here.
Thread Status
Hello , This thread has been inactive for over 60 days. New postings here are unlikely to be seen or responded to by other members. For better visibility, consider Starting A New Thread.
Top Bottom
Back
Jump to latest Follow Reply