Union SQL Statement as ADO Recordset Source

[mbentley]

I'm having trouble opening an ADO recordset using a union SQL statement as the source. I'm wondering if it will even let you do that. I'm trying to avoid storing the union query in the database and calling it as the source if possible. Of note, I am attempting to open two recordsets with different connections and base tables. The one giving me problems is the second.

   Dim cnnLayout As ADODB.Connection, rstLayout As ADODB.Recordset, _
    cnnPatients As ADODB.Connection, rstPatients As ADODB.Recordset, _
    strSQL As String, booCombinationFound As Boolean, i As Integer

    Set cnnLayout = New ADODB.Connection
    Set cnnPatients = New ADODB.Connection
    
    With cnnLayout
        .Provider = CurrentProject.Connection.Provider
        .ConnectionString = GetBackendPath("tbl_Admin_AssessmentReportLayout")
        .Open
    End With
    
    With cnnPatients
        .Provider = CurrentProject.Connection.Provider
        .ConnectionString = GetBackendPath("tblDiagnostic")
        .Open
    End With
    
    Set rstLayout = New ADODB.Recordset
    Set rstPatients = New ADODB.Recordset
    
    strSQL = "SELECT DiagnosticType, ReportSection, SectionPosition " & _
    "FROM tbl_Admin_AssessmentReportLayout " & _
    "WHERE PatientID=" & Me.txtPatientID & _
    " ORDER BY SectionPosition"
    
    rstLayout.Open strSQL, cnnLayout, adOpenDynamic, adLockOptimistic
    
    strSQL = "SELECT tblDiagnostic.DiagType as Diagnostic, " & _
    "tblDiagnostic.ReportSection as Section " & _
    "FROM tblDiagnostic " & _
    "WHERE PatientID=" & Me.txtPatientID & _
    " UNION SELECT " & Chr(34) & "Pulmonary Function Tests" & Chr(34) & ", " & _
    Chr(34) & "A. Pulmonary" & Chr(34) & _
    " FROM tblPulmFuncTest " & _
    "WHERE PatientID=" & Me.txtPatientID & _
    " UNION SELECT " & Chr(34) & "Cardiac Catheterization" & Chr(34) & ", " & _
    Chr(34) & "B. Cardiac" & Chr(34) & _
    " FROM tblCardiacCath" & _
    " WHERE PatientID=" & Me.txtPatientID

    rstPatients.Open strSQL, cnnPatients, adOpenStatic, adLockReadOnly

 

[FoFa]

What is it telling you?
I have done it in the past.

 

[mbentley]

Error -2147467259: Method 'Open' of object '_Recordset' failed.

 

[mbentley]

Solution

After much debugging, I've discovered that it did not like the "Section" alias I had given one of my fields in the SQL statement. Go figure.

 

[Pat Hartman]

Are you sure that the query is correct? Will it run if you paste it into the SQL window?

You don't say what type of trouble that you're having. Union queries are not updatable if that's your problem.

 

[mbentley]

Yes, I did test the SQL. It worked. The problem was that it wouldn't open the recordset at all. It wasn't an update problem.

Thanks