Change Ms-Access Field data type thru VB.Net

[balaki]

Hi All,

I am using VB.Net 2003 and Ms-Access 2003. I want to change the data type of fileds programmaticall y. I could not find any method in vb.net to do this.

i) I tried using the sql query "ALTER TABLE TableName ALTER COLUMN FieldName Number" It works for data type like 'Number', 'Memo' etc. But if I want to change to 'Long Integer' (sub type of Number), it does not work.

ii) I tried using ADOX as shown below. It shows the 'Operation is not allowed in this context' error on "adox.Tables(Ta bleName).Column s(FieldName).Ty pe = NewType"

Dim conn As ADODB.Connectio n
conn = CreateObject("A DODB.Connection ")
conn.Open(Conne ction.Connectio nString)
Dim adox As ADOX.Catalog
adox = CreateObject("A DOX.Catalog")
adox.ActiveConn ection = conn
adox.Tables(Tab leName).Columns (FieldName).Typ e = NewType)
adox.Tables.Ref resh()
conn.Close()

What am I doing wrong here? Is there any better way to do this? Please somebody help me as this is to be done urgently.

Thanks,
Bala

[tifoso]

Alter Table goes thru the SQL layer which is not fully supported. This may or may help you but is worth the shot. In access turn on the macro recording, then change the type and stop the recording. Now "edit" the macro and you'll see how access needs it or it will give you a hint on how to proceed It saved me a couple of times since sometimes , or should I say lots ;-) , they do funny stuff.

Ciaoi

[balaki]

Thanks for your reply.
But I am not sure, how can we record macros in MS-Access (2003). Can you please let me know how can we do this. The following url says, it is not possible.

http://www.ureader.com/message/1603951.aspx

Bala