Is there a 'hardness limit' to which woods would be appropriate for this?
Within reason I don't believe there is. Many picture frames are made of pine of course so anything like a glazing point or finish nail can be pushed or lightly hammered into the surface without difficulty. But even with oak and beech frames points can usually be pushed in using only the pressure provided by a flathead screwdriver tip. Although a better tool for doing this is a firm-bladed putty knife.
If there is some difficulty they can be squeezed in. There are a variety of commercial tools made to do this but any wide-mouth pliers (e.g. Channellock pliers) can be used for the purpose, with some card or scrap wood on the outside surface to protect the frame from bruising or denting:
in particular I am unsure of whether or not the points will stay in place in the long term.
They appear to hold fine over time. There are reports of them shifting and even falling out but that is also the case with small brads/finish nails.