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.

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

    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

    Monday, June 8, 2015 7:44 PM


  • 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