Solved

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

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now