Changing table field types in VB

scubasteve · June 12th, 2006, 12:20 PM

I have a XML data file I receive that comes into Access 2003. All the fields are defined as Text, however, there are two fields that need to be Number/Double. There is no way I am going to be able to have the XML file changed. Is there some way through VB code to change the data type on this table on the fly through calling the code in a macro? I don't want to have to stop and change the data types manually.

Thank you.

Neil

Neil

echovue · June 12th, 2006, 12:24 PM

I have a similar thing that I need to do.

My work around is as follows.

I declare ADODB Connection called cn and then

Code:

strSQL = "ALTER TABLE " & strTableName & " ALTER COLUMN [Allocation Amount] CURRENCY"

cn.Execute strSQL

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.

Mike
EchoVue.com