Add a field (programmatically) using ADO 1

[nwb1]

Hi My Friends,
I need your help!
Can you tell me how to add a field (Boolean field) to an Access table (programmatically) please?
Many Thanks
NWB1

 

[johnwm]

Hi and welcome to Tek-Tips.

To get an introduction to getting better answers please read faq222-2244 fully.

For this question we may need rather a lot more info, as we don't know what tools you are using to manipulate your DB!

If you're using JetSQL then look up the ALTER TABLE command, using the ADD method to the COLUMN collection.

If you're using ADOX then look at the Append method for the Columns collection

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[nwb1]

I need to do this throught VB and ADO.
Thanks

 

[Treepox]

Do you need this line?

ADODC1.Recordset.AddNew

Fill in the record and leave the record. After leave the record e.g. with

ADODC1.recordset.movefirst

the record is updated.

 

[nwb1]

Thanks but I need to add a Field to the database. Recordset.AddNew only add a record to the database.
I think its something like

recordset.fields.append("FieldName",FldType)

can any one give me the correct syntax please?

Thanks

 

[Dimandja]

It's here:

http://www.codeguru.com/vb/gen/vb_database/microsoftaccess/article.php/c5149

 

[nwb1]

thank you very much.

 

[nwb1]

Tried above and it only works when creating a new table. Any one can show how to append a field to an existing access table please?
Thanks in advance.

 

[johnwm]

Did you try ADOX (as suggested in my first reply) ?

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[nwb1]

Yes I did. Following ADOX code (from Dimandja) appends a field to a new table. Is there a way to change this to add a field to an existing table?
Thanks

Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, _
objTable As ADOX.Table

Set Cn = New ADODB.Connection
Set Cat = New ADOX.Catalog
Set objTable = New ADOX.Table

'Open the connection
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"

'Open the Catalog
Set Cat.ActiveConnection = Cn

'Create the table
objTable.Name = "Test_Table"

'Create and Append a new field to the "Test_Table"
'Columns Collection
objTable.Columns.Append "PrimaryKey_Field", adInteger

'Create and Append a new key. Note that we are merely passing
'the "PimaryKey_Field" column as the source of the primary key.
'Thi snew Key will be Appended to the Keys Collection of
'"Test_Table"
objTable.Keys.Append "PrimaryKey", adKeyPrimary, _
"PrimaryKey_Field"

'Append the newly created table to the Tables Collection
Cat.Tables.Append objTable

' clean up objects
Set objKey = Nothing
Set objTable = Nothing
Set Cat = Nothing
Cn.Close
Set Cn = Nothing
End Sub

 

[nwb1]

Any one there to help me?

 

[vidru]

As johnwm stated, you need to use the Append method for the Columns collection. Leave the Tables collection alone (well, at least leave the Append method alone)...

cat.Tables("TableName").Columns.Append "YourField", adBoolean

-dave

 

[nwb1]

Thanks but I tried that too.
Although there were no errors when running the program,for some reason it did not and the field.
Do you know why?