Solved

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

Posted on 2009-04-01
2
466 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

617 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