Results 1 to 9 of 9
  1. #1
    jlgray0127 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Central Illinois
    Posts
    185

    Passthrough to Oracle - HELP!

    Thank you for looking at this post.
    I am building a database in which I need to pull data from an Oracle server. I have found, I need to change my SQL language to pull data from the Oracle server, which isn't too challenging for me, if I pull it wide open.
    Where I am struggling is trying to join 3 tables together. I do not have a strong understanding of SQL, so hence the struggle.



    Below is the code from Access and then below that is my attempt to pass it to Oracle.

    SQL before trying to convert to Oracle SQL

    SELECT AFTERTREATMENT_MAIN_CEM_BUILD.JOB_NO, AFTERTREATMENT_MAIN_CEM_BUILD.BUILD_NO, AFTERTREATMENT_MAIN_CEM_BUILD.PRODUCT_ID, AFTERTREATMENT_MAIN_CEM_BUILD.SERIAL_NO, AFTERTREATMENT_MAIN_CEM_BUILD.ROUTE_TO, AFTERTREATMENT_MAIN_CEM_BOM.PART_NO, AFTERTREATMENT_MAIN_CEM_BOM.REV, AFTERTREATMENT_MAIN_CEM_BOM.QUANTITY, AFTERTREATMENT_MAIN_CEM_BOM.TOTAL_QUANTITY, AFTERTREATMENT_MAIN_CEM_BOM.DESCRIPTION, AFTERTREATMENT_MAIN_CEM_BOM.BOM_LEVEL, AFTERTREATMENT_MAIN_CEM_BOM.SEQ, AFTERTREATMENT_MAIN_CEM_BOM.TRACER, AFTERTREATMENT_MAIN_CEM_BOM.CHILD_TRACER, AFTERTREATMENT_MAIN_CEM_BOM.IN_OPER_BOM, AFTERTREATMENT_MAIN_CEM_BOM.MAKE_PART, AFTERTREATMENT_MAIN_CEM_BOM.SHEET_STEEL, AFTERTREATMENT_MAIN_CEM_BOM.SEP_WORK_ORDER, AFTERTREATMENT_MAIN_CEM_BOM.PARENT_SEP_WORK_ORDER, AFTERTREATMENT_MAIN_CEM_BOM.KIT, AFTERTREATMENT_MAIN_CEM_BOM.DELIVER_TO, AFTERTREATMENT_MAIN_CEM_BOM.SERIAL_NO, AFTERTREATMENT_MAIN_CEM_BOM.CHILD_SHEET_STEEL, AFTERTREATMENT_MAIN_CEM_BOM.UNIT_OF_MEASURE, AFTERTREATMENT_MAIN_CEM_BOM.PART_TYPE
    FROM (AFTERTREATMENT_MAIN_CEM_BUILD INNER JOIN JobBuildNum_Conversion_Gray ON (AFTERTREATMENT_MAIN_CEM_BUILD.BUILD_NO = JobBuildNum_Conversion_Gray.BN_Txt) AND (AFTERTREATMENT_MAIN_CEM_BUILD.JOB_NO = JobBuildNum_Conversion_Gray.JN_Txt)) INNER JOIN AFTERTREATMENT_MAIN_CEM_BOM ON (JobBuildNum_Conversion_Gray.BN_No = AFTERTREATMENT_MAIN_CEM_BOM.BUILD_NO) AND (JobBuildNum_Conversion_Gray.JN_No = AFTERTREATMENT_MAIN_CEM_BOM.JOB_NO)
    WHERE (((AFTERTREATMENT_MAIN_CEM_BUILD.SERIAL_NO) Like "*" & [Forms]![Test_EnterPart]![Serial] & "*") AND ((AFTERTREATMENT_MAIN_CEM_BUILD.ROUTE_TO) Not Like "Complete"));

    Attempt to Passthrough to Oracle


    SELECT JOB_NO, BUILD_NO, PRODUCT_ID, SERIAL_NO, ROUTE_TO, PART_NO, REV, QUANTITY, TOTAL_QUANTITY, DESCRIPTION, BOM_LEVEL, SEQ, TRACER, CHILD_TRACER, IN_OPER_BOM, MAKE_PART, SHEET_STEEL, SEP_WORK_ORDER, PARENT_SEP_WORK_ORDER, KIT, DELIVER_TO, SERIAL_NO, CHILD_SHEET_STEEL, UNIT_OF_MEASURE, PART_TYPE
    FROM (AFTERTREATMENT_MAIN_CEM_BUILD INNER JOIN JobBuildNum_Conversion_Gray ON (BUILD_NO = JobBuildNum_Conversion_Gray.BN_Txt) AND (JOB_NO = JobBuildNum_Conversion_Gray.JN_Txt)) INNER JOIN AFTERTREATMENT_MAIN_CEM_BOM ON (JobBuildNum_Conversion_Gray.BN_No = BUILD_NO) AND (JobBuildNum_Conversion_Gray.JN_No = JOB_NO)
    WHERE (((AFTERTREATMENT_MAIN_CEM_BUILD.SERIAL_NO) Like "*" & [Forms]![Test_EnterPart]![Serial] & "*") AND ((AFTERTREATMENT_MAIN_CEM_BUILD.ROUTE_TO) Not Like "Complete"));

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Access loves a load of brackets in the Join clauses and I don't think Oracle will. Certainly in SQL server I never use them in the same way.

    Also Oracles wild cards are different to Access : https://docs.oracle.com/cd/E23095_01...queries01.html
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    jlgray0127 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Central Illinois
    Posts
    185
    I am finding results on how I can create a dynamic statement in a query to pass through to Oracle.
    Can someone direct me to an article or something which explains this a little more in depth? I'm struggling to find more info on this, where I can understand it.
    What I basically need is to be able to only pull the records with the same Job_No and Build_No from Oracle. I can create a query in access to determine the Job_No and Build_No. I need to pass this through to Oracle to only pull those records.
    I can not hard code it to Oracle, so need a way to make the SQL Statement in my passthrough dynamic.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You would need to modify the SQL property of a QueryDef object:
    https://learn.microsoft.com/en-us/of...l-property-dao
    So basically build the query in SQL Developer (for Oracle) to make sure it works (using some hard-coded Job_No and Build_No), copy that in your VBA procedure and dynamically replace the two values with the current ones.
    Please post back if you get stuck!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    jlgray0127 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Central Illinois
    Posts
    185
    Thank you, Gicu!
    I am still struggling a little bit. As I am not strong in SQL at all. To me, this seems like it's so basic, it's really frustrating me that I am not having a Eureka! moment. lol

    On a form in Access, I just want the operator to be able to enter the serial number and I will provide the Job_No and Build_No in Access.
    I then need to pass the Job_No and Build_No to the Oracle table to retrieve the records in Oracle containing those numbers.
    The Oracle table has almost 6Mil records on it, so Querying in Access takes some time, hence trying to use the pass through method. I created the pass through query and it runs fairly quickly, but I am struggling to add the criteria. Any help with this, is greatly appreciated.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Perhaps concatenate the variables?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Could you please show us what you got (the pass through query that runs fairly quickly) and the form in question (in design view with the names of the controls for the job and build numbers)?

    Also, have you had a chance to look at the code in the link I've sent you? Should take you almost all the way to the solution, you would need to manipulate the SQL statement to provide the two parameters (as the actual values from the form).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It's been a long time since I've done any interaction with Oracle from Access, but pretty sure you'll need to use the "%" character instead of "*" as your wildcard character.

  9. #9
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Oracle joins are mostly done in the WHERE clause and very simple to construct
    example:

    Code:
    SELECT A.Field1, A.Field2, B.Field1, B.Field2
       FROM TableA A, TableB B
       WHERE A.MyFieldID_id = B.MyField_id
       ORDER BY A.Field1;

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Passthrough query confirmation
    By grumpy_whale in forum Queries
    Replies: 1
    Last Post: 10-29-2019, 09:42 AM
  2. Replies: 6
    Last Post: 03-03-2015, 01:21 PM
  3. Passthrough queries and listboxes
    By Xipooo in forum Access
    Replies: 16
    Last Post: 03-27-2014, 02:54 PM
  4. Passthrough over a timestamp?
    By KrisDdb in forum Access
    Replies: 1
    Last Post: 01-10-2012, 06:42 PM
  5. Cannot Make Table with Passthrough Query
    By chasemhi in forum Import/Export Data
    Replies: 0
    Last Post: 12-05-2011, 01:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums