The thing that initially made the
Borg frightening, at least for me, was the fact that they were so
adaptable. You couldn't (easily) defeat them with
brute force, so you needed to come up with tricks, find
loopholes, and try to somehow
outsmart them. The problem is that the same trick would never work twice.
Unfortunately, the writers of the various series (The Next Generation and Voyager in particular) decided they didn't need to invest their creative energy into conceiving new tricks, and instead used the same one over and over, one the Borg incomprehensibly weren't able to grasp.
Just about every seemingly hopeless confrontation was overcome by a small team of courageous Federation officers beaming aboard a Borg ship and doing something nasty to its insides. This was possible because the Borg never attacked or even tried to detain intruders on their ships unless they posed a direct threat. WHY they indulged in this uncharacteristically pacifistic behaviour is a mystery, especially considering how often it foiled their evil plots. Perhaps they held onto the hope that THIS TIME, the visitors were bringing them some fantastic technological gift which they, in spite of their massive consciousness and collected knowledge from just about every species around, hadn't thought of yet.
A more minor element in the series that ruined the Borg's scare factor was how comically clumsy they all were. The point of melding humanoids and robots, at least as far as I can see, was to produce a superior being to either one of these. You'd think, then, that they could make soldiers that didn't walk as though they had a large steel rod up their colon, and had SOME kind of ranged weapon. Alas.