Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove a Calculated Field from the Pivot Table field list | Excel Discussion (Misc queries) | |||
Creating a Calculated Field in a Pivot Table for same field | Excel Discussion (Misc queries) | |||
How to make a field created a part of the Pivot Table Field List? | Excel Discussion (Misc queries) | |||
Import from Database using field from excel. | Excel Discussion (Misc queries) | |||
Checking database field | Excel Programming |