Add field to jet database table in ADO

[Michael Malinsky]

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

 

[Rob Bovey]

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 *

 

[TroyW]

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... are:
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

 

[onedaywhen]

...

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-us/dnacc2k/html/acintsql.asp?frame=true#acintsql_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

--