If you don't want to use a DataEnvironment then you need to use a MSDataShape recordsource, this is one I use.
Code:
            ' Open a DATA Shape Connection For Reports
            With conReport
                .Mode = adModeShareDenyNone
                .Provider = "MSDataShape" 'Microsoft Provider for Data Shaping
                .ConnectionString = "Data Provider = Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source = " & conDb
                .Open
            End With
            ' open ADODB recordset for report
            cSql$ = "SHAPE {SELECT * FROM `tagsjournal`}  AS tagsjournal APPEND ({select * from tjdetail order by tagnumber,date}  AS Command1 RELATE 'userid' TO 'userid') AS Command1"
            If (conReport.State And adStateOpen) > 0 Then
                With rsData
                    .ActiveConnection = conReport
                    .LockType = adLockReadOnly
                    .CursorType = adOpenStatic
                    .Open cSql
                End With
            End If
            
      
            Set rptTagsjournal.DataSource = rsData
            rptTagsjournal.Show
            Set rsData = Nothing