assign table values to recordset and update with another recordset using vb6.

Question

HI,

I want to assign database table columns to recordset in vb6. And i want update them with the values that i have in my another recordset. Is that possible? Any idea or example code will be appreciated.

Hoa assign the data from table to recordset?

Answer 1

An ADODB recordset is not a mirror of a database table. The recordset contains whatever you want it to based on the query you provide it. So to load the data from the database into the recordset you need to execute a query. This can be done in two ways.

1. Use the ADODB.Connection.Execute method and set your recordset to the result.

   Dim con as New ADODB.Connection
   Dim rs as ADODB.Recordset
   con.ConnectionString = "some connection string"
   con.Open
   Set rs = con.Execute("SELECT * FROM table")

2. Create an ADODB.Recordset object, specify the connection and then call the Open method passing it a query.

   Dim con as New ADODB.Connection
   Dim rs as New ADODB.Recordset
   con.ConnectionString = "some connection string"
   con.Open
   Set rs.ActiveConnection = con
   rs.Open "SELECT * FROM table"

The query can be as simple or complex as you want it be. The query could ask for fields from multiple tables and the recordset will still contain the results of the query, however you won't be able to tell which table the fields are from.

Answer 2

A fabricated ADODB Recordset object is a fine container object because it has some great methods built in: Filter, Sort, GetRows, GetString, Clone, etc plus support for paging, serializing as XML, etc. For details see "Adding Fields to a Recordset" in this MSDN article.

But if you are working with database data, why not just execute a query?