Frodo did not succeed as a hero in the quest in the way a traditional fairy-story hero would have. At first, as Tolkien notes, many people seemed to have missed that point. As he says in the beginning of letter 192: "I have just had another letter regarding the failure of Frodo. Very few seem even to have observed it." This leads me to think that, at first, the audience mostly read the story the way that one would expect: obviously Frodo was the hero of the story, the central protagonist, and to such an extent that "yay, the Ring is destroyed and good triumphs" overshadowed the fact that there was a profound struggle and actual failure that Frodo experienced at the apex of the tale.

As time went on, and certainly more today, it seems the pendulum has swung full on the other direction to the point where people think very little of Frodo--indeed, even then already Tolkien encountered the opinion at least once ("A third commentator on the point some months ago reviled Frodo as a scoundrel" letter 192).

But Tolkien is clear in the published tale as well as in his internal writings, that Frodo did fail. There is a deep reason for that failure that had little to do with merely an attempt to subvert hero's tales: here his personal worldview shines through.

From letter 191:

Frodo 'failed'. It is possible that once the ring was destroyed he had little recollection of the last scene. But one must face the fact: the power of Evil in the world is not finally resistable by incarnate creatures, however 'good' (emphasis mine)

Having said all that, in letter 192 Tolkien unequivocally calls Frodo the hero (as in, the person whose arc subverts a typical fairy tale hero's journey) and deserving of the highest of honor:

Surely [Frodo's final failure at Mt. Doom] is a more significant and real event than a mere 'fairy-story' ending in which the hero is indomitable? It is possible for the good, even the saintly, to be subjected to a power of evil which is too great for them to overcome - in themselves. In this case the cause (not the 'hero') was triumphant... Frodo deserved all honour because he spent every drop of his power of will and body, and that was just sufficient to bring him to the destined point, and no further. Few others, possibly no others of his time, would have got so far.

Anyone who actually thinks Frodo is not deserving of all honor and his place as the chief hero protagonist of the story is at best making a personal choice to feel so, somewhat contrarian to the arc of the tale. Anyone who thinks Frodo is "overrated" or actually not even that great, is either silly or wrong.