Solved

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

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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