If you are into small games then it would be natural for you to go on and make small games. There is no problem with that. The problem arises when you're tempted to BYPASS your instincts because you're told they are wrong.
Yes, making big games is hard. If you do not work hard you will fail. But similar applies to making small games, but in a different kind of way. If you don't like small games but are nevertheless making them, they will suck, and so, you will fail. So, if someone wants to make a turn-based RPG and you come in and tell them they should make a small game instead, chances are you are doing it wrong because that might not be something they would love to play, and so, they will almost definitively fail. (notice the key assumption here - there is no way you can make a good game if it's not something you want to play)
Your second paragraph illustrates the weird over-obsession with completion some people have. Why is it so damn important to complete projects so quickly? After all, completion is an arbitrary point during creative process which means very little other than "ready for worldwide release". And what is the point of worldwide release but financial reward? Sure, you might get lots of praise and whatnot, but all that stuff is secondary and should never be the motivator for making games. In fact, making games (and making art in general, and any job where you are required to improve) requires you to ignore all praise and instead welcome criticism. Praise comes second, as a result of hard work.
So, where does the motivation for making games come from? Games themselves. And progress. If you're making progress then you should be going "Holy fuck, this is REALLY starting to look like what I always wanted to play" and that should be enough to keep you going.
Of course, you can now argue "but there are people out there who cannot do that!". Sure, I cannot deny that. How can I? But let's say you are right. Am I wrong now? Not for the most part. My main point would still stand.
Then you say:
The point of making a small game is to iterate often. Encountering the same problem and solving it multiple times will bring you much more experience than solving it once over a long period of time. This is extremely important for the learning process, to continually try again, doing better than before each time. In a large project you "can" get this too by continually rewriting your code, remaking your assets, reworking your designs, but then what's the point?
You need to elaborate on each little point you made here.
All I can say now is that you can do the same with big games.
You rewrite code or you write it from scratch. Same thing.
You remake assets or you make new assets. Same thing. EXCEPT that you make fewer assets in big games because you don't need each iteration to be accessible to the general public. In other words, you can leave art/sound/music for the last phase of development and only use placeholder for WIP iterations.
I used to think this way too. I took me 15 years to finish my first computer game
Feel free to test yourself if what I say is true
I used to think like you too. I spent 4 years on making small games and went nowhere.
I, too, have a long-term project I have yet to finish and I don't think that's a problem at all. It's big and it's been 7 years, but it keeps getting better and better with each new iteration.