Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-04-01
2
Medium Priority
?
468 Views
Last Modified: 2012-05-06
Can you make the sql of a reports recordsource pull from an mdb table external to the mdb the report is located in without having to link the tables?
0
Comment
Question by:SDKCompany
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 

Author Comment

by:SDKCompany
ID: 24041342
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

Open in new window

0
 

Accepted Solution

by:
SDKCompany earned 0 total points
ID: 24043296
I solved my issue by in the properties of each sub-report under recordsource, I put the following:

SELECT * FROM C:\Temp\tempdb.mdb.tbl_Main

Changing tbl_Main to whatever table corelates with each report.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question