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

 

[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