An interesting question.
I'd guess the key is diffraction.
Whether you look through the hole, point a camera at it, or allow an image to form on a screen behind it, doesn't seem to me to matter. With a small hole they'll all behave much as a pinhole camera.
With a large hole (relatively, say 1mm or so) the image is blurred due to the size of the spot produced by a single point. Reducing the hole size gives a sharper image as the spots get smaller - and dimmer, as Dave says, but perhaps we can use a photographic plate, or CCD array, as astronomers do (or did?)
The problem comes from diffraction causing spreading of light from the hole, rather than simple rectilinear spread from the source. Rayliegh showed a simple limit for the resolution of a telescope, when the diffraction patterns of adjacent dots overlap such that the peak of one coincides with the minimum of the other.
See
Rayliegh criterion
The diffraction spread of a point source becomes greater as the aperture of the hole decreases. A telescope has a large aperture and resolution is very good. When you get down to the sort of size you are probably thinking of, resolution would be very poor. Maybe see
Resolution calculator ( I am not familiar with this and haven't checked it out yet.
So there may not be a clear limit. Rather the image getting increasingly blurred and you decide when it is no longer useful. We can generally get over the dimness problem.
Edit:PS the shorter your wavelength, the better your resolution. So use blue light,or even UV for a suitable camera.