vba issue with inner join

[joetinger]

Hello I'm trying to connect to my db using an inner join

str = "SELECT CCSMS.CUST_NO " _
    & "FROM CSPCL INNER JOIN CCSMS " _
    & "ON CCSMS.CUST_NO = CSPCL.CUST_NO "

I keep getting the error

thanks,
JO

[FunkyDexter]

I've never seen that error before so I googled it. I get loads of results where people are experiencing it in various prorprietary systems and databases. What database are you using? What data provider are you using? Are you getting this form a prorprietary app or your own VB app? And what's the code you're using to post that query to the database.

[joetinger]

Hey thanks for the reply. The if I change it to a where it works fine. I am just trying to speed it up a bit
1. NGS
2. MSGDASQL
3. Mine
4.

 Set cnt = New ADODB.Connection
 Set rst = New ADODB.Recordset

  str = "SELECT CCSMS.CUST_NO " _
    & "FROM CSPCL INNER JOIN CCSMS " _
    & "ON CCSMS.CUST_NO = CSPCL.CUST_NO "
    
    rst.Open str, cnt
    LstCnt = 1
    Do Until rst.EOF
        LstCnt = LstCnt + 1
        rst.MoveNext
    Loop
    rst.Close
    ReDim InfoArray(LstCnt, 6)
    rst.Open str, cnt
    X = 0
    
    Do Until rst.EOF
        InfoArray(X, 0) = rst!CUST_NO
        InfoArray(X, 1) = rst!CUST_SITE
        InfoArray(X, 2) = rst!SITE_NAME
        InfoArray(X, 3) = rst!CUS_SITE_ADDR_1
        InfoArray(X, 4) = rst!CUST_ADDR_1
        InfoArray(X, 5) = rst!CUST_ADDR_3
        X = X + 1
        rst.MoveNext
    Loop

[si_the_geek]

Try simply removing the word Inner, as most/all database systems treat a Join without any extra keywords as an Inner join.

[joetinger]

When I remove inner

[si_the_geek]

OK, well try the really simplified version:

  str = "SELECT CCSMS.CUST_NO " _
    & "FROM CSPCL, CCSMS " _
    & "WHERE CCSMS.CUST_NO = CSPCL.CUST_NO "