There are a few elements to this. The Musket may be inaccurate, but Napoleon had the advantage in numbers, and the closer Cavalry gets, the easier it is to hit. Furthermore, there's two dimensions the the usefulness of the Musket. On the one hand, it's a gun, but with a bayonet fixed it's also a functional spear. Infantry in Divisional Square formation can effectively ward off melee cavalry with Bayonets, since horses are usually unwilling to charge into solid groups of men, and even if they did they'd be quickly speared.

So, there were many mitigating factors to the inaccuracy of the Musket: volume of Fire, use of Formations and Bayonets.