mbentley
Registered User.
- Local time
- Today, 07:53
- Joined
- Feb 1, 2002
- Messages
- 138
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.
Code:
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
Last edited: