
Add New Field To Existing Database
Although Thomas' suggestion is quick and easy, it may not be sufficient in
some cases. The real issue is that if you are using Jet, you cannot issue a
complete DDL statement to alter the table structure (cannot define
contraints for one). If you are using SQL server, then you can send the DDL
statement directly to SQL server and that will be handled fine.
Therefore, if you are using Jet, the correct answer is: you cannot add a
field (column) with ADO. You use ADOX for that. you need to create a column
object, define it's properties in full and then append it to the table.
The following code is copied from MSDN. It does not cover extended column
properties but should provide a jumpstart. For a full description go to MSDN
library at :
PlatformSDK - Data Services - Microsoft Data Acess Components - Microsoft
ActiveX Data Objects (ADO) - Microsoft ADOX Programmer's Reference. Don't
forget to bookmark this, or you will have trouble finding it again, as it is
burried too deep!!!
Sub CreateTable()
Dim tbl As New Table
Dim cat As New ADOX.Catalog
'Open the catalog.
' Open the Catalog.
cat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb;"
tbl.Name = "MyTable"
tbl.Columns.Append "Column1", adInteger
tbl.Columns.Append "Column2", adInteger
tbl.Columns.Append "Column3", adVarWChar, 50
cat.Tables.Append tbl
End Sub
SaSi
Quote:
> Use the SQL statement:
> ALTER TABLE [TableName]
> ADD COLUMN [ColumnName] [Type]
> and the Command object
> I suggest you save the following URLs. A very nice succinct SQL tutorial:
> http://msdn.microsoft.com/library/techart/acfundsql.htm
> http://msdn.microsoft.com/library/techart/acintsql.htm
> http://msdn.microsoft.com/library/techart/acadvsql.htm
> Regards,
> -Toby
> >Howdy,
> >This is probably really easy, but I'm just missing the obvious: How do
you
> >add a new field to an existing database using ADO? Any quick little
examples
> >or links to articles explaining how to do this in a nice concise manner
> >would be appreciated.
> >This seems like such an easy thing, but I can't find any examples on
> >Microsoft's site or through my searches of the Internet. Can anyone help?
> >CF