ADODB recordsets and new fields...

[UKFireMan]

Does anyone know how to add a new field to an existing/ open recordset?

My current code can bind controls to an open recordset whereby the field name is the control name. When I want to add a new control to a form, and therefore add a new field to an existing recordset - this is not allowed...

for example 'rs.Fields.Append CtlRef, 8, 255' doesn’t work after the recordset has been initialised and loaded with data.

Thanks for your help...

 

[Snowcrash]

It is possible to to update recordsets although there are the following restrictions. The recordset cannot be based on (from MS Help):

A query based on a many-to-many join.

A query based on data marked as read-only in the database.

A query that violates constraints on the base tables.

A query that includes the DISTINCT keyword to exclude duplicate rows.

A query based on a subquery that contains totals or aggregate functions.

A form, datasheet, or page where the underlying SQL Statement of the record source contains a GROUP BY or COMPUTE clause.
A linked table based on Transact SQL functions, because they create an ad hoc read-only connection to the external data source.
In addition, you might not be able to update specific columns in the query results. The following list summarizes specific types of columns that you cannot update in the result set:

Columns based on expressions (such as price * quantity AS extended_price).

Rows or columns deleted by another user.

Rows or columns locked by another user (locked rows can usually be updated as soon as they are unlocked).

Timestamp or image columns.

 

[BobRodes]

You could define a new recordset, add fields to it manually, including the new field, populate it with the old recordset, bind your controls to the new recordset, and then drop the old recordset.

HTH

Bob

 

[pweegar]

If you know the column name, type and size prior to runetime, why not use the alter table method of the connection property???
EX: Connection.Execute "ALTER TABLE myTable ADD COLUMN textField20 CHAR(255) NULL;"


Beaware that your table design has a limit of about 2k in size. I did a google search to find the above code, so there is a lot of it out there.