I don't see why not. But you left out an utterly crucial piece of information. Where is this forwarding to take place? Apache, php script, That Other Server, within the html itself, etc. And, equally important, why isn't your host doing this already as part of the wild-card subdomain package? It isn't enough to have software to create the accounts; you also need the DNS to point people to the right place.
If I treat this as an Apache question-- which it may not be, since you didn't say-- the most obvious option is
RewriteCond %{HTTP_HOST} !^www\.
RewriteCond %{HTTP_HOST} ^(\w+)\.example\.com
RewriteRule (.*) http://example.com/%1/$1 [R=301,L]
Do not cut and paste. That was off the top of my head. You would also need to exclude names that are already used by regular top-level directories-- /images/ and that kind of thing.
As written, this will result in a 301-to-404 sequence if someone requests a nonexistent subdomain-- or a bogus page within a real subdomain. So option B is something more like
RewriteCond %{HTTP_HOST} !^www\.
RewriteCond %{HTTP_HOST} ^(\w+)\.example\.com
RewriteRule (.*) http://example.com/fixup.php?subd=%1&req=$1 [L]
letting fixup.php do the work, ending up with either a 301 or a 404.
And then there's a whole nother set of options if you don't want people redirected at all, you're just talking about where the subdomain.example.com content lives. In that case you leave out the [R=301] flag but make a preceding rule that redirects people who asked for example.com/subdomain/ by that name. (People can type anything they like. But they all have to end up at the same URL.)
All of which takes us back to:
I tried adding the wildcard "*" and then forwarding it to mydomain.com/* but that only takes them to the index of my site.
Where, exactly, did you try this?