After spending a lot of the morning researching it appears like extension tubes aren't really going to achieve what I am after. The problem is that you need SERIOUS extension before you start getting into the extreme macro level. They're good if you want a cheaper way to convert a non-macro lens into performing at macro scales. Beyond that however, it seems like it is better to just get a dedicated super macro lens.
Currently looking at the Canon 65mm 1-5x macro lens! Get one of those DSLR micro focuser thingys and have some serious patience. Won't be doing it for a little while yet but it is at least a direction to point.
One of the first targets on my list is one of those tiny black sugar ants