I haven't run into any limitations using DNN that I wouldn't have run into doing a from scratch site. There may be some that I haven't encountered, but I haven't seen them.
I recommend using a framework because it speeds things up, for you and your client.
I have not encountered anything in DNN that limited what I could do. IWeb (http://iweb.adefwebserver.com/) helps with that.
There is a slight learning curve to developing with DNN, though, if you haven't developed a site from scratch before, there will be a learning curve there as well. :)
Module creation is fairly easy, IMO. You are essentially just creating user controls (ascx) instead of pages (aspx). There are templates out there for module creation which help get all the initial settings and structure in place so you can jump right into making your module.
As for CodeSmith and netTiers; First codesmith - I wouldn't be NEARLY as productive as I am without this tool. I've created templates in codeSmith that generate a 'blank' module for me with a bunch of settings. It allows me to enter some information, click go, open the project and start implementing my design with no other steps. Occasionally I find I need a new usercontrol, so I have it set up that I can regenerate the project and have the new usercontrol I entered added and load up when I switch back to VS.
I wouldn't enjoy programming if I had to do all the steps to get my modules set up that I have codesmith do for me. I'm kinda lazy, so I automate as much as I possibly can... CodeSmith does that wonderfully. Codesmith is a template machine. You can, essentially, run code when you generate your template to do anything you could inside a normal application. I have it launch a couple BATCH files for me to generate my Source Control repositories. (So lazy)
For netTiers - I wrote my own DAL layer. It was good... not many bugs. But it didn't do a whole lot and well... was really hack jobbie. :) I spent a while searching for better tools and found netTiers and codesmith. I have only recently been able to use them on some major modules due to not having time to refactor all my code.
I find netTiers fairly simple to use, there is a bit of a learning curve, but not that big. The netTiers website has pretty good information, it's what I used to figure it out when I was having trouble on things. I am trying to phase in netTiers as my DAL in all future projects. I find it incredibly useful, and I'm probably only using 1/2 or less of the features it has. :)
I would recommend netTiers for someone who doesn't already have a DAL, with out a doubt. There are a few database access layer generation tools and formats out there. I'd suggest doing some research into the options and finding one that looks like the best fit for you and your projects. I use netTiers, and find it to be perfect for me; if that every changes, I'll write up the code to make it perfect, and submit a patch, hehe.
Have a look at http://www.codesmithtools.com/features/frameworks.aspx for other frameworks using codesmith as the generator for database access stuff