JOIN expression not supported

[Wesley Hader]

I have been working on making my DB DSN-less and I have a bit of a problem. After racking my brain all day over the ODBC connection string to an Informix database and how to link to the tables as recordsets, I have finally got it to work!

But then I tried to QUERY the recordsets....

Here is the SQL String I am using.

        strSQL = "INSERT INTO Logsheet_Table ( Ticket_No, Date_Entered, Time_Entered, Ticket_Date, Cust_Code, Cust_Name, Cust_Address, Cust_AddressA, Cust_Address_2, " & _
                 "Cust_Address_2A, Cust_City, Cust_CityA, Cust_State, Cust_StateA, Cust_Zip_Code, Cust_Zip_CodeA, Ticket_Type, Status, Sort_Code ) " & _
                 "SELECT "" & RsHdr.ioqh_nbr & "", Date() AS Date_Entered, Time() AS Time_Entered, "" & RsHdr.ioqh_dt & "", "" & RsHdr.ioqh_cust_cd & "", "" & RsHdr.ioqh_cust_nm & "", " & _
                 """ & RsCustAddr.custa_frst_ln & "", "" & RsHdrAddr.ioqhe_frst_ln & "", "" & RsCustAddr.custa_scnd_ln & "", "" & RsHdrAddr.ioqhe_scnd_ln & "", "" & RsCustAddr.custa_city & "", " & _
                 "Trim(["" & RsHdr.ioqhe_city & ""]) AS Cust_City, "" & RsCustAddr.custa_state & "", "" & RsHdrAddr.ioqhe_state & "", "" & RsCustAddr.custa_zip_cd & "", "" & RsHdrAddr.ioqhe_zip_cd & "", " & _
                 """ & RsHdr.ioqh_type & "", ""OE"" AS Status, ""E"" AS Sort " & _
                 "FROM ((("" & RsHdr & "" INNER JOIN "" & RsCust & "" ON "" & RsHdr.ioqh_cust_cd & ""="" & RsCust.cust_cd & "") LEFT JOIN "" & RsHdrAddr & "" ON "" & RsHdr.ioqh_id & ""="" & RsHdrAddr.ioqh_id & "") INNER JOIN "" & RsCustAddr & "" ON "" & RsCust.cust_id & ""="" & RsCustAddr.cust_id & "")" & _
                 "WHERE ((("" & RsHdr.ioqh_nbr & "")=[Forms]![Frm_Logsheet_Today].[txtTicketNo]));"
 
 

And the ODBC connection string code

Dim Answer, strSQL, strConn As String
Dim Conn1 As New ADODB.Connection
Dim RsHdr, RsCust, RsCustAddr, RsHdrAddr, RsLookup As ADODB.Recordset
 
strConn = "ODBC;Dsn='';" & _
          "Driver={INFORMIX 3.81 32 BIT};" & _
          "Host=192.168.1.3;" & _
          "Server=dataline_725;" & _
          "Service=20000;" & _
          "Protocol=sesoctcp;" & _
          "Database=ecspro;" & _
          "UID=odbc;" & _
          "PWD=odbc"
 
Conn1.Open strConn
 
Set RsHdr = New ADODB.Recordset
RsHdr.CursorLocation = adUseClient
RsHdr.CursorType = adOpenKeyset
Set RsHdr = Conn1.Execute("SELECT * FROM informix.ioq_hdr")
 
Set RsCust = New ADODB.Recordset
RsCust.CursorLocation = adUseClient
RsCust.CursorType = adOpenKeyset
Set RsCust = Conn1.Execute("SELECT * FROM informix.cust")
 
Set RsHdrAddr = New ADODB.Recordset
RsHdrAddr.CursorLocation = adUseClient
RsHdrAddr.CursorType = adOpenKeyset
Set RsHdrAddr = Conn1.Execute("SELECT * FROM informix.ioq_hdr_addr")
 
Set RsCustAddr = New ADODB.Recordset
RsCustAddr.CursorLocation = adUseClient
RsCustAddr.CursorType = adOpenKeyset
Set RsCustAddr = Conn1.Execute("SELECT * FROM informix.cust_addr")
 
Set RsLookup = New ADODB.Recordset
RsLookup.CursorLocation = adUseClient
RsLookup.CursorType = adOpenKeyset
Set RsLookup = Conn1.Execute("SELECT * FROM informix.ioq_hdr WHERE ioqh_nbr='" & TicketNo & "';")

My problem is on line #7 of the SQL statement. I get "Run-time error '3296'"
"JOIN expression not supported".

After a quick Google search, seems the JOIN statement may not be parenthesized correctly, but I cannot see where. Any help would be greatly appreciated!

[Rabbit]

It would help to see the string that's actually submitted.

[Wesley Hader]

Thank you for the quick response.

The SQL string is executed by an After Update event using

DoCmd.RunSQL strSQL

[Rabbit]

I didn't mean how is it executed. I wanted to know what is in the variable right before that line of code.

[Wesley Hader]

Here is the entire procedure. I apologize if I have not been clear.

Private Sub txtTicketNo_AfterUpdate()
On Error GoTo ERR_txtTicketNo_AfterUpdate
 
Dim Answer, strSQL, strConn As String
Dim TicketNo As Long
Dim Conn1 As New ADODB.Connection
Dim RsHdr, RsCust, RsCustAddr, RsHdrAddr, RsLookup As ADODB.Recordset
 
strConn = "ODBC;Dsn='';" & _
          "Driver={INFORMIX 3.81 32 BIT};" & _
          "Host=192.168.1.3;" & _
          "Server=dataline_725;" & _
          "Service=20000;" & _
          "Protocol=sesoctcp;" & _
          "Database=ecspro;" & _
          "UID=odbc;" & _
          "PWD=odbc"
 
Conn1.Open strConn
 
Set RsHdr = New ADODB.Recordset
RsHdr.CursorLocation = adUseClient
RsHdr.CursorType = adOpenKeyset
Set RsHdr = Conn1.Execute("SELECT * FROM informix.ioq_hdr")
 
Set RsCust = New ADODB.Recordset
RsCust.CursorLocation = adUseClient
RsCust.CursorType = adOpenKeyset
Set RsCust = Conn1.Execute("SELECT * FROM informix.cust")
 
Set RsHdrAddr = New ADODB.Recordset
RsHdrAddr.CursorLocation = adUseClient
RsHdrAddr.CursorType = adOpenKeyset
Set RsHdrAddr = Conn1.Execute("SELECT * FROM informix.ioq_hdr_addr")
 
Set RsCustAddr = New ADODB.Recordset
RsCustAddr.CursorLocation = adUseClient
RsCustAddr.CursorType = adOpenKeyset
Set RsCustAddr = Conn1.Execute("SELECT * FROM informix.cust_addr")
 
TicketNo = Nz(Me.txtTicketNo)
 
Set RsLookup = New ADODB.Recordset
RsLookup.CursorLocation = adUseClient
RsLookup.CursorType = adOpenKeyset
Set RsLookup = Conn1.Execute("SELECT * FROM informix.ioq_hdr WHERE ioqh_nbr='" & TicketNo & "';")
 
If RsLookup.BOF = True And RsLookup.EOF = True Then
 
    ' Record does not exist
 
    MsgBox "This is not a valid transaction number!", vbExclamation, "Invalid Transaction Number"
 
    Me.txtTicketNo = ""
 
    Me.txtTicketNo.SetFocus
 
    Exit Sub
 
End If
 
If IsNull(DLookup("Log_ID", "Logsheet_Table", "[Ticket_No]= " & TicketNo & "")) Then
 
   ' Record does not exist
 
        strSQL = "INSERT INTO Logsheet_Table ( Ticket_No, Date_Entered, Time_Entered, Ticket_Date, Cust_Code, Cust_Name, Cust_Address, Cust_AddressA, Cust_Address_2, " & _
                 "Cust_Address_2A, Cust_City, Cust_CityA, Cust_State, Cust_StateA, Cust_Zip_Code, Cust_Zip_CodeA, Ticket_Type, Status, Sort_Code ) " & _
                 "SELECT "" & RsHdr.ioqh_nbr & "", Date() AS Date_Entered, Time() AS Time_Entered, "" & RsHdr.ioqh_dt & "", "" & RsHdr.ioqh_cust_cd & "", "" & RsHdr.ioqh_cust_nm & "", " & _
                 """ & RsCustAddr.custa_frst_ln & "", "" & RsHdrAddr.ioqhe_frst_ln & "", "" & RsCustAddr.custa_scnd_ln & "", "" & RsHdrAddr.ioqhe_scnd_ln & "", "" & RsCustAddr.custa_city & "", " & _
                 "Trim(["" & RsHdr.ioqhe_city & ""]) AS Cust_City, "" & RsCustAddr.custa_state & "", "" & RsHdrAddr.ioqhe_state & "", "" & RsCustAddr.custa_zip_cd & "", "" & RsHdrAddr.ioqhe_zip_cd & "", " & _
                 """ & RsHdr.ioqh_type & "", ""OE"" AS Status, ""E"" AS Sort " & _
                 "FROM ((("" & RsHdr & "" INNER JOIN "" & RsCust & "" ON "" & RsHdr.ioqh_cust_cd & ""="" & RsCust.cust_cd & "") LEFT JOIN "" & RsHdrAddr & "" ON "" & RsHdr.ioqh_id & ""="" & RsHdrAddr.ioqh_id & "") INNER JOIN "" & RsCustAddr & "" ON "" & RsCust.cust_id & ""="" & RsCustAddr.cust_id & "")" & _
                 "WHERE ((("" & RsHdr.ioqh_nbr & "")=[Forms]![Frm_Logsheet_Today].[txtTicketNo]));"
 
    DoCmd.RunSQL strSQL
 
    Me.txtTicketNo = ""
 
    Form.Refresh
 
    Me.txtTicketNo.SetFocus
 
    Exit Sub
 
Else
 
    Answer = MsgBox("This ticket is already on today's logsheet!" & vbNewLine & "Would you like to add it anyway?", vbYesNo, "Duplicate Ticket No found")
 
    If Answer = vbYes Then
 
        stDocName = "Qry_Get_Ticket"
        DoCmd.OpenQuery stDocName
 
        Me.txtTicketNo = ""
 
        Form.Refresh
 
        Me.txtTicketNo.SetFocus
 
        Exit Sub
 
    Else
 
        Me.txtTicketNo = ""
 
        Form.Refresh
 
        Me.txtTicketNo.SetFocus
 
        Exit Sub
 
    End If
 
End If
 
Exit_txtTicketNo_AfterUpdate:
 
RsHdr.Close
RsCust.Close
RsHdrAddr.Close
RsCustAddr.Close
RsLookup.Close
 
Conn1.Close
 
Set RsHdr = Nothing
Set RsCust = Nothing
Set RsHdrAddr = Nothing
Set custhdr = Nothing
Set RsLookup = Nothing
 
Set Conn1 = Nothing
 
    Exit Sub
 
ERR_txtTicketNo_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_txtTicketNo_AfterUpdate
 
End Sub
 

The Run-Time Error highlights line #75 on debug and states JOIN expression not supported.

[dsatino]

SQL syntax is not the same across all database types. You need to check on the syntax that is used for the specific type of database that you are passing the SQL string to. In your case it tells you that the 'JOIN expression is not supported'. So start with that.

[Rabbit]

@Wesley, that's not what I meant either. You already posted your code. I only need to know the actual value of the variable that is being submitted to the DBMS.

Take this code for example:

intValue = 5
sqlString = "SELECT *" & vbCrLf & "FROM someTable" & vbCrLf & "WHERE ID = " & intValue

When I say I want to see what's in the variable. I want to see this:

SELECT *
FROM someTable
WHERE ID = 5

And I don't mean what you think is in the variable or what you expect to be in the variable. I want to know the actual variable value as it exists in your computer's memory.

[dsatino]

Actually, I think the first thing you should do is examine your use of quotes in building your strSQL variable.

[Rabbit]

@dsatino, that's why I want to see what's in his variable. It will reveal any misquotations along with other SQL syntax errors.

[dsatino]

Well, that's easy. He gets this:

INSERT INTO Logsheet_Table ( Ticket_No, Date_Entered, Time_Entered, Ticket_Date, Cust_Code, Cust_Name, Cust_Address, Cust_AddressA, Cust_Address_2, Cust_Address_2A, Cust_City, Cust_CityA, Cust_State, Cust_StateA, Cust_Zip_Code, Cust_Zip_CodeA, Ticket_Type, Status, Sort_Code ) SELECT " & RsHdr.ioqh_nbr & ", Date() AS Date_Entered, Time() AS Time_Entered, " & RsHdr.ioqh_dt & ", " & RsHdr.ioqh_cust_cd & ", " & RsHdr.ioqh_cust_nm & ", " & RsCustAddr.custa_frst_ln & ", " & RsHdrAddr.ioqhe_frst_ln & ", " & RsCustAddr.custa_scnd_ln & ", " & RsHdrAddr.ioqhe_scnd_ln & ", " & RsCustAddr.custa_city & ", Trim([" & RsHdr.ioqhe_city & "]) AS Cust_City, " & RsCustAddr.custa_state & ", " & RsHdrAddr.ioqhe_state & ", " & RsCustAddr.custa_zip_cd & ", " & RsHdrAddr.ioqhe_zip_cd & ", " & RsHdr.ioqh_type & ", "OE" AS Status, "E" AS Sort FROM (((" & RsHdr & " INNER JOIN " & RsCust & " ON " & RsHdr.ioqh_cust_cd & "=" & RsCust.cust_cd & ") LEFT JOIN " & RsHdrAddr & " ON " & RsHdr.ioqh_id & "=" & RsHdrAddr.ioqh_id & ") INNER JOIN " 
& RsCustAddr & " ON " & RsCust.cust_id & "=" & RsCustAddr.cust_id & ")WHERE (((" & RsHdr.ioqh_nbr & ")=[Forms]![Frm_Logsheet_Today].[txtTicketNo]));

Which is not a valid sql string in anyway of course, but I think it's better to make them work that out themselves by pointing them in the right direction.

[Wesley Hader]

@Rabbit

Are you wanting to see what EACH variable is equal to or just the one in the WHERE statement?

WHERE (((" & RsHdr.ioqh_nbr & ")=726148));

@dsatino

I have always assumed that this was a syntax error probably related to quotations. This is the first time I have tried using a variable for a recordset in a SQL string so I thought there may be a chance that this is actually not supported, which is why I posed the question in the way that I did.

Either way, thank you both for your responses!

[NeoPa]

Wesley Hader:
Here is the SQL String I am using.

Actually, that's not a SQL string at all Wesley. It's some VBA code that creates a SQL string using some literal values, but also some other variable values that are not available to us (as you haven't shared this information). I can see that other experts have also stumbled into this problem in the thread.

Please read Before Posting (VBA or SQL) Code. This will help everyone to help you in a more timely manner.

[dsatino]

Ah, I see...I think.

I Wesley is trying to reference his recordset variables with his SQL statement. If that's the case, then no Wesley you can't do that. You can, however, use your recordsets to build a proper SQL string that you can run.

[Wesley Hader]

Thank you dsatino! It seemed like a long shot when I first attempted it, but I thought I would give it a shot.

Can you point me in the right direction on using ADO recordsets in VBA, creating the VBA TEXT string, and using that to create the SQL string? My problem, I assume, will arise in trying to create the JOINs or what I am thinking of as the relationships between the recordsets.

I know my wording has not been up to par and that I did not provide enough information to make this an easy question to answer, but I appreciate the effort anyways!

[dsatino]

Before you go down that road...

Do you have your DB linked to these remote tables?

[Wesley Hader]

That's how I got here....

I can connect to these tables using an ODBC System DSN, but this problem arose when trying to create the connections with VBA Connection strings and manually creating the ADO recordsets. My goal is to have everything coded and go away from using DSN's.

[dsatino]

Ok, well then there a couple of ways to go about this. Here's one:

Private Sub txtTicketNo_AfterUpdate() 
    On Error GoTo ERR_txtTicketNo_AfterUpdate 
 
    Dim Answer, strSQL, strConn As String 
    Dim TicketNo As Long 
    Dim Conn1 As New ADODB.Connection 
    Dim RsHdr, RsCust, RsCustAddr, RsHdrAddr, rsRemote, RsLookup As ADODB.Recordset 
    dim db as database
    dim rsLocal as recordset
 
    strConn = "ODBC;Dsn='';" & _ 
              "Driver={INFORMIX 3.81 32 BIT};" & _ 
              "Host=192.168.1.3;" & _ 
              "Server=dataline_725;" & _ 
              "Service=20000;" & _ 
              "Protocol=sesoctcp;" & _ 
              "Database=ecspro;" & _ 
              "UID=odbc;" & _ 
              "PWD=odbc" 
 
    Conn1.Open strConn 
 
    'TicketNo = Nz(Me.txtTicketNo) 
    TicketNo = Nz(Me.txtTicketNo,-1)
    'you should now test for TicketNo = -1 and exit the sub
 
    Set RsLookup = New ADODB.Recordset 
    RsLookup.CursorLocation = adUseClient 
    RsLookup.CursorType = adOpenKeyset 
    Set RsLookup = Conn1.Execute("SELECT * FROM informix.ioq_hdr WHERE ioqh_nbr='" & TicketNo & "';")  'ticketno is in a text field here
 
    If RsLookup.BOF = True And RsLookup.EOF = True Then       
        ' Record does not exist 
        MsgBox "This is not a valid transaction number!", vbExclamation, "Invalid Transaction Number"
        Me.txtTicketNo = "" 
        Me.txtTicketNo.SetFocus 
        Exit Sub 'You should explicitly close your connections and recordsets
    End If 
 
    If IsNull(DLookup("Log_ID", "Logsheet_Table", "[Ticket_No]= " & TicketNo & "")) Then 'ticketno is in a numeric field here
       ' Record does not exist 
        set db=currentdb
        set rsLocal=db.openrecordset("Logsheet_Table",dbopendynamic)
        set rsRemote= Conn1.Execute("SELECT hdr.ioqhdt,hdr.ioqhcustcd,hdr.ioqhcustnm, " & _  
             "cadd.custafrstln,hadd.ioqhefrstln,cadd.custascndln,hadd.ioqhescndln,cadd.custacity,hdr.ioqhecity AS CustCity,cadd.custastate, " & _
             "hadd.ioqhestate, cadd.custazipcd,hadd.ioqhezipcd,hdr.ioqhtype,OE AS Status,E AS Sort " & _
             "FROM (((informix.ioq_hdr hdr   INNER JOIN   informix.cust cust   ON   hdr.ioqhcustcd  =  cust.custcd) " & _
             "LEFT JOIN informix.ioq_hdr_addr hadd ON hdr.ioqhid  =  hadd.ioqhid) " & _
             "INNER JOIN   informix.cust_addr cadd   ON   cust.custid  =  cadd.custid) " & _
             "WHERE hdr.ioqhnbr ='" & TicketNo & "'"
         rsRemote.movefirst
 
 
        with rsLocal
            .addnew
                !Ticket_No=TicketNo
                !Date_Entered= Date()
                !Time_Entered= Time()
                !Ticket_Date= rsRemote!ioqhdt
                !Cust_Code= rsRemote!ioqhcustcd
                !Cust_Name= rsRemote!ioqhcustnm
                !Cust_Address= rsRemote!
                !Cust_AddressA= rsRemote!
                !Cust_Address_2= rsRemote!
                !Cust_Address_2A= rsRemote!
                !Cust_City= rsRemote!
                !Cust_CityA= rsRemote!
                !Cust_State= rsRemote!
                !Cust_StateA= rsRemote!
                !Cust_Zip_Code= rsRemote!
                !Cust_Zip_CodeA= rsRemote!
                !Ticket_Type= rsRemote!
                !Status= "OE"
                !Sort_Code = "E"
            .update
        End with
 
        set db=nothing
        set conn1=nothing
 
        Me.txtTicketNo = "" 
        Form.Refresh 
        Me.txtTicketNo.SetFocus 
 
        Exit Sub 
 
    Else 
 
        Answer = MsgBox("This ticket is already on today's logsheet!" & vbNewLine & "Would you like to add it anyway?", vbYesNo, "Duplicate Ticket No found") 
 
        If Answer = vbYes Then 
 
            stDocName = "Qry_Get_Ticket" 
            DoCmd.OpenQuery stDocName 
 
            Me.txtTicketNo = "" 
 
            Form.Refresh 
 
            Me.txtTicketNo.SetFocus 
 
            Exit Sub 
 
        Else 
 
            Me.txtTicketNo = "" 
 
            Form.Refresh 
 
            Me.txtTicketNo.SetFocus 
 
            Exit Sub 
 
        End If 
 
    End If 
 
    Exit_txtTicketNo_AfterUpdate: 
 
    RsHdr.Close 
    RsCust.Close 
    RsHdrAddr.Close 
    RsCustAddr.Close 
    RsLookup.Close 
 
    Conn1.Close 
 
    Set RsHdr = Nothing 
    Set RsCust = Nothing 
    Set RsHdrAddr = Nothing 
    Set custhdr = Nothing 
    Set RsLookup = Nothing 
 
    Set Conn1 = Nothing 
 
        Exit Sub 
 
    ERR_txtTicketNo_AfterUpdate: 
        MsgBox Err.Description 
        Resume Exit_txtTicketNo_AfterUpdate 
 
End Sub 
 
 

All I'm doing here is showing you a method. Don't assume this will work as written. The SQL syntax for the informix database type may be different than you have written here so you'll need to check on that. I also didn't complete the field references for the local updates so you'll have to do that yourself.

Again, I'm just giving you one concept on how to do this. There are various other methods as well.

[NeoPa]

Wesley,

I see you've taken that on board (which is good). Now, though, you're heading into territory that is not the original question. That's not a problem except in as much as it may not be done in this thread (otherwise threads just become messy and impossible to follow sensibly).

At this stage you need to break down your problem into more manageable chunks and ask specific questions for each that you need help with. The accent here is on the specific. We're happy to give help in any area we can, but threads need to be specific to be much use to anyone. The process you have to go through to get to that stage is also very beneficial to you, the requestor, as you have to get much of the logic sorted out in your head before you even ask the question. This will certainly help you with the process, even before anyone jumps in to provide help.

Good luck :-)