How to open a recordset from a recordset?

Question

I would like to open a recordset with VBA, from another recordset.

This works if I write the SQL and save it into a query named new_entry and valid_unit

' valid_unit
SELECT *
FROM import_raw_data
WHERE unit_name IN (SELECT unit_name FROM unit);

' new_entry
SELECT *
FROM valid_unit
WHERE id NOT IN (SELECT id FROM serviceman);
WHERE

However, I would like to do this in VBA exclusively instead of saving the queries.

Sub testing()
    valid_unit_sql = _
        "SELECT * " _
      & "FROM import_raw_data"
      & "WHERE unit_name IN (SELECT unit_name FROM unit);

    new_entry_sql = _
        "SELECT * "
      & "FROM ( " & valid_unit_sql & ") " _
      & "WHERE id NOT IN (SELECT id FROM serviceman);"

    With CurrentDb
        Set valid_unit = .OpenRecordset(valid_unit_sql)
        ' valid_unit works great
        Set new_entry = .OpenRecordset(new_entry_sql)
        ' this doesnt, it errors out
    End With
End Sub

May I know how do I do this? Should I be saving my valid_unit query and FROM valid_unit instead?

Your code is invalid, please correct it and show working code. Then read and apply: [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) -- if it doesn't help, add the output of `Debug.Print new_entry_sql` to your question. — Andre, Sep 20 '17 at 09:29
Also, *"errors out"* isn't very helpful, please add the full error message. — Andre, Sep 20 '17 at 09:30
Thanks for pointing that out, and apologies for the incorrect code provided and the not-descriptive error message. I'll take note of that for future questions! — Shawn Koh, Oct 02 '17 at 01:09

score 0 · Accepted Answer · answered Sep 20 '17 at 12:47

Your quotation marks " and new line continuations & _ are a bit messed up. I don't know if that's a copy-paste error or if this your actual code (doesn't compile).

Anyways try this:

Sub testing()       
    valid_unit_sql = "SELECT * " & _
                     "FROM import_raw_data " & _
                     "WHERE unit_name IN (SELECT unit_name FROM unit)"

    new_entry_sql = "SELECT * " & _
                    "FROM (" & valid_unit_sql & ") AS T " & _
                    "WHERE T.id NOT IN (SELECT id FROM serviceman);"

    Debug.Print new_entry_sql

    With CurrentDb
        Set valid_unit = .OpenRecordset(valid_unit_sql)
        Set new_entry = .OpenRecordset(new_entry_sql)
    End With
End Sub

Output SQL:

SELECT * 
FROM 
    (
        SELECT * 
        FROM import_raw_data 
        WHERE unit_name IN (SELECT unit_name FROM unit)
    ) AS T 
WHERE T.id NOT IN (SELECT id FROM serviceman);

Thank you so much! I was not aware that I had to specify FROM AS — Shawn Koh, Oct 02 '17 at 01:08

How to open a recordset from a recordset?

1 Answers1