Add field to table programatically at runtime

[briancoats]

I would like to check to see if a field already exists in a table and the add it to the table programatically if it does not. I am connecting to an access database through the data objecy (I know, don't kill me) but I can change that if I have to. I would appreciate any help with this as I can't seem to get it myself. Thanks.

Brian

 

[bjd4jc]

Here is some code that will help you out. You will need to change it to match whatever database type your using. You will need a reference to Microsoft ActiveX Data Object 2.x and Microsoft ADO Ext. 2.7 for DDL and Security for this to work. This example is with an Access Database.

    Dim con As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    Dim blnFound As Boolean
    
    Set con = New ADODB.Connection
    con.Provider = "Microsoft.Jet.OLEDB.4.0"
    
    con.Open "C:\db.mdb"
    
    Set cat = New ADOX.Catalog
    
    cat.ActiveConnection = con
    
    Set tbl = cat.Tables("Table1")
    
    blnFound = False
    For Each col In tbl.Columns
        If col.Name = "Column2" Then
            blnFound = True
            Exit For
        End If
    Next
    
    If Not blnFound Then
        tbl.Columns.Append "Column2", adVarWChar, 50
    End If
    Set tbl = Nothing
    Set cat = Nothing
    con.Close
    Set con = Nothing

 

[fredericofonseca]

Note that ADOX will not work with all Databases. see

http://support.microsoft.com/?id=271483

So if you with to have "portable" code use OpenSchema to get the table definition, and then use standard sql to do an "alter table" and add the column.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com