I think that an inventive director, with the access to the present-day computer animation that looks like actual film, should be able to surmount this problem, with something like cut shots, i.e. from people on the worm to the whole worm, or with a flying-in zoom shot (i.e. you see this massive worm speeding across the desert, and as the camera pans the length of the beastie, it begins an accelerating zoom to show little tiny people, who get larger and larger, then presto, Paul Maud'Dib riding with his Fremen warriors on the back of a massive worm...The only adaptation I've seen is David Lynch's.
Aside from the usual "what's in/what's left out" issues and "who should play which character" arguments, I thought the biggest disappointments were the worms. Now perhaps this was solved in the TC mini-series, but I doubt it. I wonder if it is inevitable. Put simply, the worms are too big for translation to the screen. The difference in scale between a sand worm and the people around it is too much to encompass. Focus too much on the people and the worm becomes a huge wall; focus too much on the worm, and the people become next-to-invisible dots. Even in the cinema, it didn't work.
One cannot really blame the film-makers for this: they are only following the books. One cannot blame Herbert: the worms in the book are fine (if only because, without the obvious scaling problems on the screen, the mind can conceive of something small - a human - interacting with something so monumentally large as a sandworm).
There are those who absolutely worship the films of David Lynch. My older son is one of them. I'm not. I thought the effects (such as they were) did nothing to enhance the telling of the story. It seemed more of an attempt to turn Herbert's themes into Steampunk.The Fremen didn't look like Fremen (no robes over the stillsuits), the acting was stilted, and the story did not make sense unless you read the book (my friends who came with me who had not read the book were totally lost). And that's just for starters! That movie committed all of the major crimes that Hollywood commits when putting a big imaginative sf/fantasy on the screen.