We read Kohelet either on the Shabbos Chol Hamoed of Sukkos or Shemini Atzeres.
Why do we specifically read this on Sukkos? What is the connection between Sukkos and Koheles?
Sukkot is called "The Holiday of Gathering", since it is the time of the in-gathering of the crops. At this time one, feeling flush with his successful agricultural endeavors, may forget to attribute the success to G-d (as promised in Sefer Devarim).
In order to remember G-d's role in our work, we move into a temporary dwelling (sukkah) where we can see the heavens and remember G-d.
For this reason we read Kohelet, which "is full of earnest thoughts and reflections on the "vanity of vanities" of this world. It fittingly concludes with the words, "The end of the matter after all is heard, is: Fear G-d and keep His commandments, for this is the whole purpose of man."
I heard a really nice dvar Torah about this over Sukkot.
At one point in time, Kohelet was debated as to whether it should be part of the Tanach or not. One argument against Kohelet was that it is confusing and contradictory. In one verse it says that Simcha is Good, in another verse it says that Simcha leads to sin and is therefore Bad. (There are many such examples) However, one of the reason this argument was refuted was because Kohelet also makes us think. Yes it is full of contradictions, but in the end it makes us think and brings us closer to Hashem.
The holiday of Sukkot, is also full of these 'contradictions'. On the one hand, The etrog is alone and considered to be the Talmud Chacham. On the other hand, we have the Lulav which is made up of 3 types of non-Talmud Chachams all with their pitfalls. But we bring them together, and the Lulav is considered greater than the Etrog. On Sukkot, we go outside but build for ourselves booths so we go out to go in. During the rest of the year we seperate ourselves from Non-Jews, but on Sukkoth the non Jews are invited to the Temple as well, and we bring sacrifices for them.
So in Short. Just as in Kohelet, the good and the bad are brought together, so too in Sukkot the good and the bad are brought together all towards one purpose... serving Hashem. (And it makes us think)
The follow up question to this though, is why do only Ashanazim read Kohelet on Sukkot?
I heard that since all the other Megillot were read on a holiday, a minhag (custom) started to read the remaining megillah, ie. Kohelet, on the remaining holiday, ie. Sukkot. Later rabbis came up with justifications for this minhag, such as those mentioned above.
(I don't have a hard source for this. Sorry.)
Sukkos in prayers is refered to as zman simchateinu, time of our happiness. It is happier than and both Pesach and Shavuos, because happier than the day one is introduced to their beloved, and happier even than the day one is married, is the day one reconciles with their beloved after a fight.
Conflicts and their resolutions make relationships stronger. We spent this past year in varying degrees of unfaithfulness to Gd. Admitting our faults, and commiting to forsake them for His service, makes our relationship with Him stronger every time we do it. That should bring about more happiness than a memory of first meeting Him, or making an eternal covenant with Him, because it represents both that meeting's and that covenant's enduring presence in our lives, how year after year it is renewed, stronger and more full of love than ever before.
Koheles captures that, in essence, by considering all of the pleasures of the world, as well as its mechanics, concluding from experience that its all worthless, and arriving at the service of Gd as the means and the end. Shlomo struggled to find real meaning in places other than torah and mitzvos, and finally admitted that there was none, reconciling with himself and his existence as a servant of Gd, the only source of meaning.
Just my thoughts.