Using VBA to change the datatype of a column

[LemonTwist]

I need to automatically change the datatype of one of the columns in an Access table from Binary to Number using VBA. I tried using the code below to do this but get the runtime error 3219: Invalid Operation.

Is there any way I can do this?

Public Sub ChangeColumnType()[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]        Dim db As DAO.Database[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]        Dim tbl As DAO.TableDef[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]        [/SIZE][/FONT]
[FONT=sans-serif][SIZE=2]        Set db = CurrentDb[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]        Set tbl = db.TableDefs("table1")[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]        tbl.Fields("column1").Type = dbBigInt[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]        [/SIZE][/FONT]
[FONT=sans-serif][SIZE=2]        Set tbl = Nothing[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]        Set db = Nothing[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]End Sub

Thank you in advance for any help.

 

[CyberLynx]

I suppose you could use the SQL ALTER TABLE statement but in my opinion, I don't think it's a good idea to be flipping your Data Types around all the time.

Set the field to a Data Type you will be happy with to accomodate the incoming data and when working with that data, merely use whichever MS-Access Type Conversion Functions (CStr(), CInt(), CLng(), CDate()....etc) needed to carry out the task.

.

 

[LemonTwist]

Thanks for your help. In the end, I decided to use ALTER TABLE statements. Previously, the user of the database was having to manually change the data types of the columns (by going into Design view) every time they wanted to run the report. Once I had created the ALTER TABLE statements, I was able to add them to a macro so that now the whole report process is automated, which is how we want it.

 

[dwhitman]

I recently needed a routine to change field type, and found a chunk of code here:

http://accessblog.net/2007/03/how-to-change-field-type-using-dao.html

that did the job. You can't do it directly in DAO, but the routine just makes a new field with the new type, then copies the data over and deletes the old field. Works like a charm.