Add New Field To Existing Database 
Author Message
 Add New Field To Existing Database

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



Fri, 07 Feb 2003 03:00:00 GMT  
 Add New Field To Existing Database
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

Quote:

>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



Sat, 08 Feb 2003 03:00:00 GMT  
 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



Sat, 08 Feb 2003 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. How to modified existing field and add new field in existing table for Access database

2. Adding a new field to an existing database

3. Adding new fields to an existing table

4. Add New Field in existing Table (VB6)

5. Adding New Field to Existing Repot

6. Adding a new field to an existing table via ADOX

7. adding a autonumber field to an existing database

8. Adding Tables and Fields to an existing database

9. Create new field in existing table exactly like field in second table

10. How to add new records in a new database

11. Add new listbox item to an existing array

12. Crystal, add new table to existing report?

 

 
Powered by phpBB® Forum Software