Well, literally, to "drive someone crazy" means to cause them to become mentally unstable or insane. As an idiom, it still has pretty much the same meaning, except that the person doesn't become literally insane, they just feel like they are losing control of their mind. This could happen due to frustration or anger ("These instructions are so complicated, they're driving me crazy!"), or because of other extreme emotions, including sexual attraction ("The way she looks in that dress drives me crazy"). In either case, it means that the speaker feels they are losing control because of an emotion or intense feeling. We can also talk about being "crazy about" a person or "madly in love with" a person. If you're talking about love or attraction, words like "crazy" describe the intensity of the emotion, and usually don't mean anything negative.

In Steve Tyler's case, it means he can't stop thinking about a woman. Normally, it means something is frustrating. I felt like I had to answer this because I found an Aerosmith hat by the railroad tracks yesterday.

Drive me crazy .... as in making me mad or irritated She drives me crazy .... can mean makes me crazy about her, crazy in love and I have to be with her. He drives me crazy can have the same double meaning 

ничего странного "сводить с ума" можно не только дрелью в стену но и в позитивном (как в песне) смысле