Well, 40 minutes of raw video (no sound) at 320x240 and 8-bit color will use around 44 gig. After mpeg/divx compression, (i'm assuming the final video will be around 1/100th the size) you will use 442MB. So depending on which camera you get, and what kind of compression the thing does, a 1 GB card should be plenty for you.

I'm not up on all the cameras, but one I've been thinking about is that Tony Hawk helmet cam that is marketed to children. From what I hear, it has a decent video, and should be able to take some abuse. Plus, because it is marketed as a toy, it should be cheaper than a lot of other similar cameras.