Would a 1974 Winchester 94 be collectable?

For Winchesters, 1964 or earlier are the guns that command a premium price.

Believe anything Winchester will have a value. All have taken a
turn upward rapidly. Do expect a downward trend If that Williams
sight required drill and tap of the receiver pretty well knocks the value
to a shooter that the addition improved it usefullness, but decrease
collector value.

In 1974 M94's came from the factory drilled and tapped for a receiver sight. For the time being, consider it a fine shooter that will appreciate slowly if cared for.

It's a Good Shooter

Yeah, what MMCSRET said. It'll appreciate slowly and isn't all that uncommon, so in our lifetimes it really won't be much of an investment gun.
I'd shoot it but not abuse it - your kids may someday find it has value.

I have a Model 94 produced around that time. When I had a Williams
Foolproof sight installed, I found that the receiver was already drilled and tapped for such a sight from the factory. The receiver sight makes the rifle much easier to shoot accurately than the buckhorn open sights that came on it. I bought mine used at a gunshow almost 20 years ago for $150.00. It was/is in almost new condition with a perfect bore and only a nick or two on the wood. I imagine that I could probably get a little more for it now than I paid for it. So I guess you can expect your 94 to slowly appreciate over time. Personally, I would use it however I wanted to and enjoy it without consideration of it's collectibilty status.