I have a similar thing that I need to do.
My work around is as follows.
I declare ADODB Connection called cn and then
strSQL = "ALTER TABLE " & strTableName & " ALTER COLUMN [Allocation Amount] CURRENCY"
That changes the datatype from a string to currency, and I would assumed that you could change it to a Number or Double just as easily.