STUMPED with ADO Fields Collection

[SoftwareMaker]

HELP...

I am stumped at how to append a new field to a recordset and then after that sending the new recordset with the new added field back to Access Using ADO2.0 and JET

Could anybody give me any pointers ? I am stumped at especially which part I have to close the recordset and set the activeconnection to Nothing

[peet]

You want to add a new field to a table?

[SoftwareMaker]

Yes Peet,

I want to add a new field to an Access Table BUT not using native SQL alter Table statements. I want to do it using the recordset object and fields collection of ADO...For my MCSD case study.

Thanks

[peet]

oh.. I think u have to use ADOX, in order to do that.

Add a reference to the Microsoft ADO Extensions for DDL and Security (ADOX).

I'll digup a sample for u....

[peet]

This sample show u how to create a new db and adding a table to it.

Option Explicit
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
 
Private Sub Command1_Click()
    Set cat = New ADOX.Catalog
    Set tbl = New ADOX.Table
 
    ' create the table
    cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\newDB.mdb"
    
    With tbl
      .Name = "TestTable"
      ' Create fields and append them to the
      ' Columns collection of the new Table object.
      With .Columns
         .Append "ID COLUMN"
         .Append "SetName", adVarWChar, 255
         .Append "SetVal", adVarWChar, 255
         .Append "Description", adVarWChar, 255
      End With
   End With
   
   ' Add the new Table to the Tables collection of the database.
   cat.Tables.Append tbl
   
   Set cat = Nothing
   Set tbl = Nothing
    
End Sub

hope this can get u started

[SoftwareMaker]

I really appreciate the help you have given me peet

The thing is I need to append a new field using ADODB...Is that possible at all ?

[peet]

don't think so, but I'm not 100 % sure though... but I'v never seen it done.

anyone else have any input ?

[Cenobite]

This can't be done with ADO. You have to use ADOX or DAO


Cenobite

[SoftwareMaker]

THanks Cenobite,

I guess I have to stick with the commandText property of the ADO Command Object to append fields to a database using native SQL Alter Keyword statements for now.