SQLExecQuery link database tables
SQLExecQuery link database tables
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: SQLExecQuery link database tables

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Vancouver, Canada
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I've trying to retrieve data from our database using SQLExecQuery.
    I'm having trouble with the formula to link tables together.
    So far I've tried as follows, but it doesn't work:
    SQLExecQuery DB, "SELECT CLIENTS.NAME, CLIENTS.TEL_1, LEASES.START_DATE FROM ONSETC_MERGED_CLIENTS INNER JOIN ONSETC_MERGED_LEASES ON ONSETC_MERGED_CLIENTS.CLIENT_ID = ONSETC_MERGED_LEASES.CLIENT_ID WHERE LEASES.LEASE_ID = '1234'"

    I'm not sure on the INNER JOIN part.
    Is this the correct formula?

    Any help would be much appricated.

  2. #2
    New Member
    Join Date
    Apr 2002
    Location
    Vancouver, Canada
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've just tried a different formula, but still no success!
    SQLExecQuery DB, "SELECT CLIENTS.NAME, CLIENTS.TEL_1, LEASES.START_DATE FROM ONSETC_MERGED_CLIENTS CLIENTS, ONSETC_MERGED_LEASES LEASES WHERE LEASES.CLIENT_ID = CLIENTS.CLIENT_ID AND LEASES.LEASE_ID = '1234'"

    Please help!

  3. #3
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    DatabaseName = "SELECT CLIENTS.NAME, CLIENTS.TEL_1, LEASES.START_DATE FROM ONSETC_MERGED_CLIENTS INNER JOIN ONSETC_MERGED_LEASES ON ONSETC_MERGED_CLIENTS.CLIENT_ID = ONSETC_MERGED_LEASES.CLIENT_ID WHERE LEASES.LEASE_ID = '1234'"
    queryString = _
    "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)"
    myJoin = SQLOpen("DSN=" & DatabaseName)
    SQLExecQuery myJoin, queryString
    Set output = Worksheets("Sheet1").Range("A1")
    SQLRetrieve myJoin, output, , , True
    SQLClose myJoin

    I cannot test this, but "DatabaseName" may need to be the result of your join within the database product. So, DatabaseName is not a join statement but the table name of the resulting join. You cannot use SQLExec without SQLOpen! Hope this helps? JSW

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    Vancouver, Canada
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I've sorted it.
    Apparently there's a limit of 127 characters for the string.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com