Data type change in VBA?

[key]

Hi,

just two quick questions:

- is it possible to change columns' data type?
- is ist possible to change columns' name?
in VBA?

Thank you,

Key

 

[DanR]

Hi Key

I just had a quick try, and changed the field names in a table using the following code. Thus uses DAO, the older way to handle data in Access. I haven't tried using ADO. Trying to change the data type in the same way resulted in an error.

--------------------------------

Sub changefield()

Dim tdf As TableDef
Dim fld As DAO.Field
Dim dbs As Database

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("TestTable")
MsgBox tdf.Name
For Each fld In tdf.Fields
MsgBox fld.Name
fld.Name = "Renamed" & fld.Name
MsgBox fld.Name
Next fld

End Sub

-------------------------------------------

Note, that you can create a new table, or add a new field, and set the name and type using VBA code.

cheers
Dan

 

[key]

Quick question

How I can change just one fields' name and type
(I have to change field: 'nummer' in number and type: 'text' in 'number'

Thank you,

Key

 

[Pat Hartman]

Inside the For Each loop, compare the field name to the field that you want to change. When you get to the correct field, make the change and exit the loop.

Why not just open the table in design view and change the name and data type?

 

[key]

code check

Hi,

I got the run-time error '3219' but I cannot figure out what is wrong with this code... I think it's because the property 'type' is read-only. Is there any way to change the field type via vba?

Dim tdf As TableDef
Dim fld As DAO.Field
Dim dbs As Database

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("tblTemp")

For Each fld In tdf.Fields
If fld.Name = "AdjAccount" Then
MsgBox fld.Name
fld.Type = dbLong
fld.Size = 50
MsgBox fld.Name
End If
Next fld

Thanks,

Key