Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

How can ADO recordsets be combined?

I'm using VB6 with ADO and an Access 2000 database.  I have two ADODB recordsets with only one field (MfgCode) in common that I need to combine into one recordset.  Does anyone have an example of how to do this?  I cannot use a SQL statement to write another query to generate the desired recordset.  I need to combine the existing recordsets into a new recordset where the mfgcode fields of the two recordsets match.
0
CrazyFox
Asked:
CrazyFox
  • 5
  • 4
1 Solution
 
samopalCommented:
Why you cannot use SQL query? But if you cannot, then....

Rs1 - 1st Recordset
Rs2 - 2nd Recordset
Rs - result Recordset

rs1.movefirst
do while not rs1.eof
 rs.addnew
 rs!MfgCode = rs1!MfgCode
 rs.Update
 rs1.MoveNext
Loop

rs2.MoveFirst
do while not rs2.eof
  rs.find "MfgCode = " & rs2!MfgCode
  if rs.eof then
    rs.addnew
    rs!MfgCode = rs2!MfgCode
    rs.Update
  end if
 rs2.MoveNext
loop
0
 
CrazyFoxAuthor Commented:
I tried your example and got the following error on the line rs.addnew:

Run-Time Error '3704'
The operation requested by the application is not allowed if the object is closed.
0
 
Éric MoreauSenior .Net ConsultantCommented:
Your new recordset must be declared this way:

Dim rstMemory As ADODB.Recordset

    Set rstMemory = New ADODB.Recordset
    With rstMemory
        .CursorLocation = adUseClient
        .LockType = adLockPessimistic
        .Fields.Append "MfgCode", adVarChar, 50
        .Open
    End With

Once this part is done, you can use the AddNew method on this recordset.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
CrazyFoxAuthor Commented:
emoreau,
That worked, but how do I get more than just the mfgcode field in the resulting recordset?  What I really need is all the fields from both recordsets in the result recordset where the mfgcodes are the same.
0
 
Éric MoreauSenior .Net ConsultantCommented:
You can use «.Fields.Append» to add other fields.

But why can't you do it from your database in a query like this:
Select Table1.*, Table2.*
From Table1, Table2
Where Table1.mfgcode = table2.mfgcode
0
 
CrazyFoxAuthor Commented:
I can't use the query because sometimes table2 will be empty.  It is filled in with optional user comments during runtime and these comments are then used elsewhere in the app by user generated reports.

If it is empty, then the query returns no results.
0
 
Éric MoreauSenior .Net ConsultantCommented:
What is your database engine? You can use a left join.
0
 
CrazyFoxAuthor Commented:
Provider=Microsoft Jet 4.0 OLE DB Provider
0
 
Éric MoreauSenior .Net ConsultantCommented:
So you use Access. Your query may look like this:

Select Table1.*, Table2.*
From Table1 LEFT JOIN Table2
ON Table1.mfgcode = table2.mfgcode
0
 
CrazyFoxAuthor Commented:
That still gives me an empty recordset.  I checked the data and there should be two matching records from the tables.  I'll try the fields.append
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now