Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Unanswered: Unmatched SQL across databases ... A97

    Hey gang,

    Trying to bash together an unmatched query on 2 tables in separate databases ... This might be a Rudy question ... Here's what I've got SQL wise right now:

    SELECT * FROM HISRPM LEFT JOIN Data IN 'C:\Mike Misc\flateishist.mdb' ON HISRPM.[Test Record Number - Report Number] = Data.[Test Record Number - Report Number]
    WHERE (((Data.[Test Record Number - Report Number]) Is Null));

    Any thoughts on my IN blowing on me? Perhaps in the wrong order?

    Thanks!

    [EDIT] - Or perhaps it's just cruddy SQL cobbled on a unmatched query ... In which case, (HELP!!! ) on straightening this pig out ...
    Last edited by M Owen; 08-29-06 at 14:21.
    Back to Access ... ADO is not the way to go for speed ...

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Why not Just Link the tables
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Haven't really needed it, but I'm not sure the IN clause can be used with tables in separate db's. Try this:

    SELECT * FROM HISRPM LEFT JOIN [C:\Mike Misc\flateishist.mdb].Data ON HISRPM.[Test Record Number - Report Number] = Data.[Test Record Number - Report Number]
    WHERE (((Data.[Test Record Number - Report Number]) Is Null));

    Please tell me you aren't responsible for all those spaces and symbols!
    Paul

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pbaldy
    Haven't really needed it, but I'm not sure the IN clause can be used with tables in separate db's. Try this:

    SELECT * FROM HISRPM LEFT JOIN [C:\Mike Misc\flateishist.mdb].Data ON HISRPM.[Test Record Number - Report Number] = Data.[Test Record Number - Report Number]
    WHERE (((Data.[Test Record Number - Report Number]) Is Null));

    Please tell me you aren't responsible for all those spaces and symbols!
    Nope. Inherited ... BTW, doesn't work ... A97 thinks that "C:\Mike Misc\flateishist.mdb" is a local table ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Perhaps something new to A2k? I tested this in A2k and it worked fine:

    SELECT *
    FROM TableName LEFT JOIN [C:\db1.mdb].TableName1 ON TableName.ID = TableName1.ID
    WHERE TableName1.ID Is Null;
    Paul

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    DUH!!!!! Damn I'm stupid. Typo in the file name ... What a day ... Thanks Paul.
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    UPDATE:

    GOT IT!!!! This works:

    Code:
    DELETE *
    FROM HISRPM
    WHERE (HISRPM.[Test Record Number - Report Number] IN (SELECT HISRPM.[Test Record Number - Report Number]
    FROM HISRPM LEFT JOIN [C:\MikeMisc\flatEishst.mdb].Data ON HISRPM.[Test Record Number - Report Number] = Data.[Test Record Number - Report Number]
    WHERE (((Data.[Test Record Number - Report Number]) Is Null))));
    I had to do it this way cause the tables in question have no PKs or even indexed columns (I know, I know - and they're probably not going to either ... Not my choice) and A97 can't do the DELETE as originally constituted.

    It's amazing what you can figure out in the middle of the night while sleeping ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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