How to add a new field in an existing Access 97 table?

[Giampiero]

How can I add a new field in an existing Access 97 table? Where can I find
an example?

Bye

Giampiero

[Paul Clement]

On 5 Mar 2001 09:23:15 -0800, "Giampiero" <giampiero@powersoft.it> wrote:

¤
¤ How can I add a new field in an existing Access 97 table? Where can I find
¤ an example?
¤

You can use ADOX (Microsoft ADO Ext for DDL and Security) to do this:

Sub AddFields()

Dim col As New ADOX.Column
Dim cat As New ADOX.Catalog

'Engine Type=4 is Access 97 and a value of 5 is Access 2000
cat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;"

col.NAME = "Column1"
col.Type = adLongVarWChar
cat.Tables("Table1").Columns.Append col
Set col = New ADOX.Column
col.NAME = "Column2"
col.Type = adBoolean
cat.Tables("Table1").Columns.Append col
Set col = New ADOX.Column
col.NAME = "Column3"
col.Type = adVarChar
cat.Tables("Table1").Columns.Append col

cat.Tables("Table1").Columns("Column1").Properties("Jet OLEDB:Allow Zero Length").Value = True
cat.Tables("Table1").Columns("Column2").Properties("Jet OLEDB:Allow Zero Length").Value = True
cat.Tables("Table1").Columns("Column3").Properties("Jet OLEDB:Allow Zero Length").Value = True

Set cat = Nothing

End Sub


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[Giampiero]

Thank you very much. IT WORKS!!!!

Giampiero

Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
>On 5 Mar 2001 09:23:15 -0800, "Giampiero" <giampiero@powersoft.it> wrote:
>
>¤
>¤ How can I add a new field in an existing Access 97 table? Where can I
find
>¤ an example?
>¤
>
>You can use ADOX (Microsoft ADO Ext for DDL and Security) to do this:
>
>Sub AddFields()
>
>Dim col As New ADOX.Column
>Dim cat As New ADOX.Catalog
>
>'Engine Type=4 is Access 97 and a value of 5 is Access 2000
>cat.ActiveConnection = _
> "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=c:\My Documents\db1.mdb;" & _
> "Jet OLEDB:Engine Type=4;"
>
>col.NAME = "Column1"
>col.Type = adLongVarWChar
>cat.Tables("Table1").Columns.Append col
>Set col = New ADOX.Column
>col.NAME = "Column2"
>col.Type = adBoolean
>cat.Tables("Table1").Columns.Append col
>Set col = New ADOX.Column
>col.NAME = "Column3"
>col.Type = adVarChar
>cat.Tables("Table1").Columns.Append col
>
>cat.Tables("Table1").Columns("Column1").Properties("Jet OLEDB:Allow Zero
Length").Value
>= True
>cat.Tables("Table1").Columns("Column2").Properties("Jet OLEDB:Allow Zero
Length").Value
>= True
>cat.Tables("Table1").Columns("Column3").Properties("Jet OLEDB:Allow Zero
Length").Value
>= True
>
>Set cat = Nothing
>
>End Sub
>
>
>Paul ~~~ pclement@ameritech.net
>Microsoft MVP (Visual Basic)