none
VBA Recordset Question RRS feed

  • Question

  • I have a procedure where I need to select data using 3 different queries from 3 different SQL Servers. I've got it setup now so that the data will be pulled into 3 different recordsets. What I need to do is iterate the first and ensure that any occurrences of the 2nd recordset within the first get removed. Then I need to ensure that only those that exist in the 3rd recordset remain in the first.

    In other words...

    Recordset 1 (oRS1) = All records
    Recordset 2 (oRS2) = Remove records in Recordset 1 that also exist in this recordset.
    Recordset 3 (oRS3) = Remove records in Recordset 1 that don't exist in this recordset.

    I plan to iterate the recordsets so that I only end up with those records in oRS1 that don't exist in oRS2 and do exist in oRS3. So it'd look something like this.

    oRS1.MoveFirst
    Do Until oRS1.EOF
    
    	oRS2.MoveFirst
    	Do Until oRS2.EOF
    		
    		If (oRS1.Fields("ID").Value = oRS2.Fields("ID").Value) Then
    			oRS1.Delete
    		End If
    		
    		oRS2.MoveNext
    	Loop
    	
    	Dim recExists As Boolean
    	
    	Set recExists = False
    	
    	oRS3.MoveFirst
    	Do Until oRS3.EOF
    		If (oRS1.Fields("ID").Value = oRS3.Fields("ID").Value) Then
    			recExists = True
    		End If
    		
    		oRS3.MoveNext
    	Loop
    	
    	If (recExists = False) Then
    		oRS1.Delete
    	End If
    	
    	oRS1.MoveNext
    Loop

    I want to be sure that I'm doing that right and that these Delete function calls will ONLY update the recordset in memory and will NOT touch the database.


    Nathon Dalton
    Sr. Lead Developer
    Blog: http://www.nathondalton.com

    Monday, June 8, 2015 7:44 PM

Answers

  • Hi Nathon Dalton,

    As far as I know, to disable the modification not reflect to the database we can make the RecordSet disconnected via setting the RecordSet.ActiveConnection to nothing.

    You can get more detail about this property from link below:
    ActiveConnection Property (ADO)

    And here is an helpful article about it:
    How To  Create ADO Disconnected Recordsets in VBA/C++/Java

    However, to the original problem about filter data cross SQL server, I would suggest that create a view to provide the data for the solution.

    For example, we can use link server to access data from outside of SQL Server. And I suggest that you reopen a new thread in SQL server forum for the solution on the SQL server side.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 9, 2015 5:06 AM
    Moderator