Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How can ADO recordsets be combined?

Posted on 2000-02-28
Medium Priority
Last Modified: 2013-11-26
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.
Question by:CrazyFox
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4

Accepted Solution

samopal earned 200 total points
ID: 2566175
Why you cannot use SQL query? But if you cannot, then....

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

do while not rs1.eof
 rs!MfgCode = rs1!MfgCode

do while not rs2.eof
  rs.find "MfgCode = " & rs2!MfgCode
  if rs.eof then
    rs!MfgCode = rs2!MfgCode
  end if

Author Comment

ID: 2566341
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.
LVL 70

Expert Comment

by:Éric Moreau
ID: 2566407
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
    End With

Once this part is done, you can use the AddNew method on this recordset.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 2566434
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.
LVL 70

Expert Comment

by:Éric Moreau
ID: 2566462
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

Author Comment

ID: 2566476
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.
LVL 70

Expert Comment

by:Éric Moreau
ID: 2566495
What is your database engine? You can use a left join.

Author Comment

ID: 2566509
Provider=Microsoft Jet 4.0 OLE DB Provider
LVL 70

Expert Comment

by:Éric Moreau
ID: 2566534
So you use Access. Your query may look like this:

Select Table1.*, Table2.*
From Table1 LEFT JOIN Table2
ON Table1.mfgcode = table2.mfgcode

Author Comment

ID: 2566596
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question