Can a report recordsource sql be to an external mdb table?

I have the below code for creating a new DB, creating tables in that new DB and then creating links to the tables in that DB.  The problem I am running into is setting the recordsource of the reports on the fly.  I get errors in everything that I have tried.  I have tried to link during the Report_Open event as well as trying to set them as in the bottom of the code below.  The main report that opens also opens 5 sub-reports.
I created a test report by copying the main report and deleting the sub-reports out of it.  In the Report_Open event I can set the recordsource and it works.
Any ideas what could be going on?

Function fnMakeTables()
On Error GoTo Error_Handler
    
    Dim barWidth As Integer
    Dim maxbarWidth As Integer
    Dim db As Database
    
    strNewDB = "C:\Temp\tempdb.mdb"
    Set db = DBEngine.CreateDatabase(strNewDB, dbLangGeneral)
    
    maxbarWidth = 6
    
    barWidth = Forms!frm_Main.barProgressBar.Width
    Forms!frm_Main.boxProgressBar.Visible = True
    Forms!frm_Main.barContProgressBar.Visible = True
    Forms!frm_Main.barProgressBar.Visible = True
    Forms!frm_Main.lblProgressBar.Visible = True
    Forms!frm_Main.barProgressBar.Width = (barWidth / maxbarWidth) * 0
    Forms!frm_Main.Repaint
    
    DoCmd.SetWarnings False
    
    HeadSQL = "SELECT A1.CR_ID, A1.ORIGINATOR_NAME, A1.ORIGINATOR_GROUP, A1.ORIGINATOR_PHONE, A1.DISCOVERED_DATETIME, A1.ORIGINATOR_SUPERVISOR_NAME, A1.ENTERED_DATETIME, A1.CR_NUM, A1.CONDITION_DESC, A1.IMMEDIATE_ACTION_DESC, A1.SUGGESTED_ACTION_DESC, (CASE WHEN A1.OPER_MODE_IND = 'Y' THEN 'Yes' ELSE 'No' END) AS OpInd, (CASE WHEN A1.REPORT_MODE_IND = 'Y' THEN 'Yes' ELSE 'No' END) AS RepInd FROM CRSDBA.V_CR_CURRENT A1 WHERE ((A1.PLANT_CODE='PLP') AND (A1.ENTERED_DATETIME BETWEEN to_date('" & BeginDate & "', 'mm/dd/yy:hh:mi:ssam') AND to_date('" & EndDate & "', 'mm/dd/yy:hh:mi:ssam')) " & NullText & ")"
    
    CurrentDb.QueryDefs("qry_PT_Connection").SQL = HeadSQL
    
    CurrentDb.Execute "SELECT * INTO " & strNewDB & ".tbl_Main FROM qry_PT_Connection", dbFailOnError
    
    DoCmd.TransferDatabase acLink, "Microsoft Access", strNewDB, acTable, "tbl_Main", "tbl_Main_" & strUserName
    
    Forms!frm_Main.barProgressBar.Width = (barWidth / maxbarWidth) * 1
    Forms!frm_Main.Repaint
    
    OperSQL = "SELECT A2.CR_ID, A2.OPERERABILITY_VERSION, A2.OPER_CODE, A2.PERFORMEDBY_NAME, A2.APPROVEDBY_NAME, A2.APPROVED_DATETIME, A2.OPERABILITY_DESC, A2.APPROVED_DESC,  A2.PERFORMED_DATETIME, (CASE WHEN A2.APPROVED_DATETIME IS NULL THEN 'Not Approved' ELSE 'Approved' END) AS OP_STATUS FROM CRSDBA.V_CR_CURRENT A1, CRSDBA.V_OPERABILITY A2 WHERE ((A1.CR_ID = A2.CR_ID) AND (A1.PLANT_CODE='PLP') AND (A1.ENTERED_DATETIME BETWEEN to_date('" & BeginDate & "', 'mm/dd/yy:hh:mi:ssam') AND to_date('" & EndDate & "', 'mm/dd/yy:hh:mi:ssam')) " & NullText & ")"
        
    CurrentDb.QueryDefs("qry_PT_Connection").SQL = OperSQL
    
    CurrentDb.Execute "SELECT * INTO " & strNewDB & ".tbl_Oper FROM qry_PT_Connection", dbFailOnError
    
    DoCmd.TransferDatabase acLink, "Microsoft Access", strNewDB, acTable, "tbl_Oper", "tbl_Oper_" & strUserName
    
    Forms!frm_Main.barProgressBar.Width = (barWidth / maxbarWidth) * 2
    Forms!frm_Main.Repaint
    
    EquipSQL = "SELECT A2.CR_ID, A2.TAG_NAME, A2.TAG_SUFFIX_NAME, A2.COMPONENT_CODE, A2.PROCESS_SYSTEM_CODE FROM CRSDBA.V_CR_CURRENT A1, CRSDBA.T_CR_EQUIPMENT A2 WHERE ((A1.CR_ID = A2.CR_ID) AND (A1.PLANT_CODE='PLP') AND (A1.ENTERED_DATETIME BETWEEN to_date('" & BeginDate & "', 'mm/dd/yy:hh:mi:ssam') AND to_date('" & EndDate & "', 'mm/dd/yy:hh:mi:ssam')) " & NullText & ")"
        
    CurrentDb.QueryDefs("qry_PT_Connection").SQL = EquipSQL
    
    CurrentDb.Execute "SELECT * INTO " & strNewDB & ".tbl_Equip FROM qry_PT_Connection", dbFailOnError
    
    DoCmd.TransferDatabase acLink, "Microsoft Access", strNewDB, acTable, "tbl_Equip", "tbl_Equip_" & strUserName
    
    Forms!frm_Main.barProgressBar.Width = (barWidth / maxbarWidth) * 3
    Forms!frm_Main.Repaint
    
    ReportSQL = "SELECT A2.CR_ID, A2.REPORTABILITY_VERSION, A2.REP_CODE, A2.BOILERPLATE_CODE, A2.REPORT_NUMBER, A2.REPORTABILITY_DESC, A2.PERFORMEDBY_NAME, A2.PERFORMED_DATETIME FROM CRSDBA.V_CR_CURRENT A1, CRSDBA.V_REPORTABILITY A2 WHERE ((A1.CR_ID = A2.CR_ID) AND (A1.PLANT_CODE='PLP') AND (A1.ENTERED_DATETIME BETWEEN to_date('" & BeginDate & "', 'mm/dd/yy:hh:mi:ssam') AND to_date('" & EndDate & "', 'mm/dd/yy:hh:mi:ssam')) " & NullText & ")"
    
    CurrentDb.QueryDefs("qry_PT_Connection").SQL = ReportSQL
    
    CurrentDb.Execute "SELECT * INTO " & strNewDB & ".tbl_Report FROM qry_PT_Connection", dbFailOnError
    
    DoCmd.TransferDatabase acLink, "Microsoft Access", strNewDB, acTable, "tbl_Report", "tbl_Report_" & strUserName
    
    Forms!frm_Main.barProgressBar.Width = (barWidth / maxbarWidth) * 4
    Forms!frm_Main.Repaint
    
    RefSQL = "SELECT A2.CR_ID, A2.TYPE_CODE, A2.ITEM_DESC FROM CRSDBA.V_CR_CURRENT A1, CRSDBA.T_REFERENCEITEM A2 WHERE ((A1.CR_ID = A2.CR_ID) AND (A1.PLANT_CODE='PLP') AND (A1.ENTERED_DATETIME BETWEEN to_date('" & BeginDate & "', 'mm/dd/yy:hh:mi:ssam') AND to_date('" & EndDate & "', 'mm/dd/yy:hh:mi:ssam')) " & NullText & ")"
        
    CurrentDb.QueryDefs("qry_PT_Connection").SQL = RefSQL
    
    CurrentDb.Execute "SELECT * INTO " & strNewDB & ".tbl_Ref FROM qry_PT_Connection", dbFailOnError
    
    DoCmd.TransferDatabase acLink, "Microsoft Access", strNewDB, acTable, "tbl_Ref", "tbl_Ref_" & strUserName
    
    Forms!frm_Main.barProgressBar.Width = (barWidth / maxbarWidth) * 5
    Forms!frm_Main.Repaint
    
    TrendSQL = "SELECT A2.CR_ID, A2.TREND_TYPE, A2.TREND_CODE FROM CRSDBA.V_CR_CURRENT A1, CRSDBA.T_CRTREND A2 WHERE ((A2.CR_ID = A1.CR_ID) AND (A1.PLANT_CODE='PLP') AND (A1.ENTERED_DATETIME BETWEEN to_date('" & BeginDate & "', 'mm/dd/yy:hh:mi:ssam') AND to_date('" & EndDate & "', 'mm/dd/yy:hh:mi:ssam')) " & NullText & ")"
 
    CurrentDb.QueryDefs("qry_PT_Connection").SQL = TrendSQL
    
    CurrentDb.Execute "SELECT * INTO " & strNewDB & ".tbl_Trend FROM qry_PT_Connection", dbFailOnError
    
    DoCmd.TransferDatabase acLink, "Microsoft Access", strNewDB, acTable, "tbl_Trend", "tbl_Trend_" & strUserName
    
    Forms!frm_Main.barProgressBar.Width = (barWidth / maxbarWidth) * 6
    Forms!frm_Main.Repaint
    
    Reports("rpt_Main").RecordSource = "SELECT * FROM tbl_Main_KLANGST"
    Reports("subrpt_Equipment").RecordSource = "SELECT * FROM tbl_Equip_KLANGST"
    Reports("subrpt_Oper").RecordSource = "SELECT * FROM tbl_Oper_KLANGST"
    Reports("subrpt_Ref").RecordSource = "SELECT * FROM tbl_Ref_KLANGST"
    Reports("subrpt_Report").RecordSource = "SELECT * FROM tbl_Report_KLANGST"
    Reports("subrpt_Trend").RecordSource = "SELECT * FROM tbl_Trend_KLANGST"
    
    DoCmd.OpenReport "rpt_Main", acViewPreview
    
    DoCmd.SetWarnings True
    
    DoCmd.ShowToolbar "CR Report App", acToolbarYes
 
Exit_Procedure:
Exit Function
 
Error_Handler:
    MsgBox "An error has occurred in this application. " _
    & "Please call IT and give " _
    & "the following information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
    & Err.Description, _
    Buttons:=vbCritical
    
    Resume Exit_Procedure
    
End Function

Select allOpen in new window