Solved

How can ADO recordsets be combined?

Posted on 2000-02-28
10
310 Views
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.
0
Comment
Question by:CrazyFox
  • 5
  • 4
10 Comments
 
LVL 9

Accepted Solution

by:
samopal earned 100 total points
ID: 2566175
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
 

Author Comment

by:CrazyFox
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.
0
 
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
        .Open
    End With

Once this part is done, you can use the AddNew method on this recordset.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:CrazyFox
ID: 2566434
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
 
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
0
 

Author Comment

by:CrazyFox
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.
0
 
LVL 70

Expert Comment

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

Author Comment

by:CrazyFox
ID: 2566509
Provider=Microsoft Jet 4.0 OLE DB Provider
0
 
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
0
 

Author Comment

by:CrazyFox
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
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA color chart bars 12 88
Sending a email via excel using vba 6 85
Search combo error "Data Type Mismatch in Criteria Expression" 2 63
Help me. 3 57
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

825 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