Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Pass-Through Query & CreateQueryDef (Access 97 SR-2)

    Help!!

    I created a SQL Pass-Through Query to DB2 with a left outer join statement. A portion of the code is listed below.

    from gpkbnitm a, gpkbnusg b
    left outer join gpkbnlpf c on b.plnt = c.plnt and b.itm = c.itm
    where a.kbnitm_plant = '015' and a.kbnitm_plant = b.plnt and a.kbnitm_item = b.itm

    This works great! Now the problem - when the entire sql is put in a string (MySql) and I use the CreateQueryDef function to create the query on the fly; I receive a message 'Join Expression not supported'. No error codes or other information is received. The CreateQueryDef function is below.

    Set DB2qry = db.CreateQueryDef("Q_ek_db2_rpt_11", MySql)

    If I remove the join, the CreateQueryDef works fine. I've tried different formats and sequences of the SQL join but still get the same problem.

    Any Suggestions?? Thanks...........

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL Pass-Through Query & CreateQueryDef (Access 97 SR-2)

    It sounds like your CreateQueryDef doesn't know that you are creating a Pass-Through query - I believe you have to supply a parameter to the CreateQueryDef to tell DAO that you are creating a pass-through as opposed to a regular query. Unfortunately, my DAO help seems to be MIA at the moment - but check you 97 Help and see if it doesn't show a parameter for query type.
    Wendell

  3. #3
    New Lounger
    Join Date
    Mar 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Pass-Through Query & CreateQueryDef (Access 97 SR-2)

    I think you are on the correct track, but the Access Help & a DAO reference book I checked, don't list any additional parameters for the CreateQueryDef to specify a SQL Pass-Through Query.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL Pass-Through Query & CreateQueryDef (Access 97 SR-2)

    After some digging and interuptions, I think what you need to do is specify an ODBC Connection String property for the query. Note that pass-through queries only work with .MDB databases, not ADP projects, and that they do need to use an ODBC data source. Technet Article ACC: How to Create an SQL Pass-Through Query in Code outlines the basic steps that are required.
    Wendell

  5. #5
    New Lounger
    Join Date
    Mar 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Pass-Through Query & CreateQueryDef (Access 97 SR-2)

    That worked!!!

    Thanks for all your help..........

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •