What it does is throttle based on concurrent batches.
It's not a connection limit or a user limit, but after eight concurrent batches not transactions are running it introduces a delay for extras.
We did a a test for a client server suite we recomend the full version for but support MSDE.
With 14 people trying to hit the most expensive functions there was no noticeable slow down, at about 19 hammering away it got worse, 25 and several people got seriously degraded performance.
So 40 is OK as long as they all don't request something at exactly the same time. If you are fortunate enough to have a sensible design, quick in and out stuff, not big aggregate multi table joins etc, you users might not even percieve the slow down, if it occurs at all.
Things like batch updates with cursors, big reports, slow queries are the things to look out for. You might get away with them except for locking issues on the full product, but they'll eat into those eight concurrent batches very quickly if you've got them.
HTHs