Add field to jet database table in ADO
 

I've been working through some code on the MS KB on how to use ADO to create
and manipulate a jet database. I understand how to create a new table, but
what I can't seem to find is a bit of code to add a new field to an existing
table. Any help is appreciated.

TIA
--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winne the Pooh

Add field to jet database table in ADO
 

Hi Michael,

Check out the help topic on the ALTER TABLE statement in the Access help
file. The syntax for adding a new column to a table is the following:

ALTER TABLE MyTable ADD COLUMN MyColumn Number

Run this SQL statement against your database using ADO and it will add a
column to the table.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Michael Malinsky" wrote in message
...
I've been working through some code on the MS KB on how to use ADO to
create
and manipulate a jet database. I understand how to create a new table,
but
what I can't seem to find is a bit of code to add a new field to an
existing
table. Any help is appreciated.

TIA
--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winne the Pooh

Add field to jet database table in ADO
 

Michael,

Below is some code that uses the ADODB and ADOX libraries. The code will add
a field named "myFldNew1" to a table "Table1" in the file "TestDB1.mdb".
The connection string assumes you have a Jet 4.0 engine. Does this do what
you want?

The two libraries that need to be defined under Tools | References... a
1) Microsoft ActiveX Data Objects 2.7 Library (ADODB)
2) Microsoft ADO Ext. 2.7 for DDL and Security (ADOX)

Troy

Sub ADOX_AddTableField()

Dim oCat As ADOX.Catalog
Dim oConn As ADODB.Connection
Dim oTbl As ADOX.Table
Dim sConn As String

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\My Documents\TestDB1.mdb;" & _
"User ID=admin;Password="""";"

Set oConn = New ADODB.Connection
oConn.Open sConn

Set oCat = New ADOX.Catalog
oCat.ActiveConnection = oConn

Set oTbl = oCat.Tables("Table1")

'''Display the properties of the first field of Table1.
MsgBox "Name = " & oTbl.Columns(1).Name & vbCr & _
"Type = " & oTbl.Columns(1).Type

'''Add the new field.
oTbl.Columns.Append "myFldNew1", adVarWChar, 50

MsgBox "Table1 Field Count = " & oTbl.Columns.Count

'''Kill the objects.
Set oTbl = Nothing
Set oCat.ActiveConnection = Nothing
oConn.Close
Set oConn = Nothing

End Sub


"Michael Malinsky" wrote in message
...
I've been working through some code on the MS KB on how to use ADO to
create
and manipulate a jet database. I understand how to create a new table,
but
what I can't seem to find is a bit of code to add a new field to an
existing
table. Any help is appreciated.

TIA
--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winne the Pooh

Add field to jet database table in ADO
 

"Rob Bovey" wrote ...

ALTER TABLE MyTable ADD COLUMN MyColumn Number

Run this SQL statement against your database using ADO and it will add a
column to the table

.... of type 'Float'. For details on DDL mappings for Jet data types,
see:

Intermediate Microsoft Jet SQL
http://msdn.microsoft.com/library/en...tsql_datatypes

I agree with Rob Bovey: DDL is preferable to ADOX.

FWIW the exception is 'Hyperlink'. This is because it is not a native
Jet data type; rather, it is used in the MS Access UI only. Hence, to
create a column of type 'Hyperlink' you *must* use the ADOX approach
e.g.

Sub Test()
Dim Cat As Object
Dim Col As Object

Set Cat = CreateObject("ADOX.Catalog")
Cat.ActiveConnection = CONN_STRING

Set Col = CreateObject("ADOX.Column")
With Col
.ParentCatalog = Cat
.Name = "NewHyper"
.Type = 203 ' adWChar i.e. Memo
.Properties("Jet OLEDB:Hyperlink").Value = True
End With

Cat.Tables("MyTable ").Columns.Append Col

End Sub

--