I think if the main recordset is on the computer (front end) is faster if I
create recordset from the first one.
I give a simple example about the main recordset:
Dim cnn AsADODB.Connection
Dim rec AsADODB.recordset
Dim strSQL As String
Set cnn = CurrentProject.Connection
Set Rec = NewADODB.recordset
StrSql="SELECT dDate, Meter, Net, WatMeter FROM dbo.J1_OilProdYL WHERE
BlockID = N'block 2')"
Rec.Open strSQL, cnn
The recordset give the following data:
dDate Meter Net WatMeter
01-01-2007 Bagre 11117 5861
01-01-2007 Essungo 617 0
01-01-2007 Lombo 9281 5875
02-01-2007 Bagre 10985 5797
02-01-2007 Essungo 603 0
02-01-2007 Lombo 8813 5946
Sub recordset 1
Now I would like create a new recordset from main recordset (rec) to give
data only for the meter='Bagre'
dDate Meter Net WatMeter
01-01-2007 Bagre 11117 5861
02-01-2007 Bagre 10985 5797
Sub recordset 2
Also I would like to create a new sub recordset to give only the following
columns (reducing columns)
dDate Meter WatMeter
01-01-2007 Bagre 5861
01-01-2007 Essungo 0
01-01-2007 Lombo 5875
02-01-2007 Bagre 5797
02-01-2007 Essungo 0
02-01-2007 Lombo 5946
To fabricate a recordset with a reduced number of columns, something
like this:
Dim rs2 As ADODB.Recordset
Set rs2 = New ADODB.Recordset
rs2.Fields.Append _
rec.Fields("dDate").Name, _
rec.Fields("dDate").Type, _
rec.Fields("dDate").DefinedSize
rs2.Fields.Append _
rec.Fields("Meter").Name, _
rec.Fields("Meter").Type, _
rec.Fields("Meter").DefinedSize
rs2.Fields.Append _
rec.Fields("WatMeter").Name, _
rec.Fields("WatMeter").Type, _
rec.Fields("WatMeter").DefinedSize
rs2.Open
Dim counter As Long
rec.MoveFirst
For counter = 0 To rec.RecordCount - 1
rs2.AddNew Array( _
rec.Fields("dDate").Name, _
rec.Fields("Meter").Name, _
rec.Fields("WatMeter").Name), _
Array( _
rec.Fields("dDate").value, _
rec.Fields("Meter").value, _
rec.Fields("WatMeter").value)
rec.MoveNext
Next
rs2.MoveFirst
MsgBox rs2.GetString
For the reduced number of rows you could simply use
rec.Filter = "meter = 'Bagre'"
before transferring the data.
FWIW fabricating seems like a lot of work (i.e. processing time and
coding time) just to be able to use CopyFromRecordset. Take a look at
the Recordset object's GetRows property to return an array where you
can specify the fields to be returned. You need to be able to
'transpose' the array (columns to rows and vice versa) to be able to
read into an Excel Range; Excel has a worksheet function for this but
it has limits (check the Excel Help) e.g. something like:
Sheet1.Range("A1:C2").Resize(rec.RecordCount).Value = _
Excel.Application.WorksheetFunction.Transpose(rec.GetRows(, ,
Array("dDate", "Net", "WatMeter")))
PS. the main recordset has many recordsets
Then three database roundtrips to produce three different recordsets
may be faster than all this procedural processing in the middleware
and front end, especially so if you are fabricating recordsets.
Jamie.
--