troubleshooting Question

Using more than one join on an Informix Database

Avatar of ringroselaw
ringroselaw asked on
DatabasesMicrosoft Excel
4 Comments1 Solution1442 ViewsLast Modified:
Hi Experts

I am currently working on an Excel sheet which, with the aid of VBA populates a sheet with data from an Informix database.

My problem is that when I try to use more than one join statement, on execution Excel locks up.

The following SQL line returns any amount of results happily: -

SELECT * FROM actlup ORDER BY actlup.activity_code

But the following lines always struggle: -

SELECT * FROM actlup FULL OUTER JOIN doctyp ON actlup.activity_code = doctyp.type_code WHERE doctyp.wp_code = 'WORD' ORDER BY actlup.activity_code

SELECT * FROM actlup FULL OUTER JOIN doctyp ON actlup.activity_code = doctyp.type_code FULL OUTER JOIN casegrp ON actlup.activity_code = casegrp.id_code WHERE doctyp.wp_code = 'WORD' AND casegrp.main_group = 'MADV' ORDER BY actlup.activity_code


It could well be my syntax, I'm a rookie at this kind of thing, so any help would be much appreciated.






Dim AdoCon3 As ADODB.Connection
Dim Rs3 As ADODB.Recordset
Dim strSQL3 As String
Dim strSQL3p1 As String
Dim strSQL3p2 As String
Dim conditval As Integer
Dim conditval2 As Integer
 
conditval = 0
conditval2 = 0
 
Set AdoCon3 = New ADODB.Connection
    AdoCon3.CursorLocation = adUseClient
    AdoCon3.Open "DSN=arista32;Uid=informix;Pwd=Axxia;"
 
    
    strSQL3p1 = "SELECT first 100 * FROM actlup "
    
    
    If cmbDocType.Value <> "All Doc Types" Then
        
    strSQL3p1 = strSQL3p1 & "FULL OUTER JOIN doctyp ON actlup.activity_code = doctyp.type_code "
        
    End If
    
    If cmbGroup.Value <> "All Groups" Then
    
    strSQL3p1 = strSQL3p1 & "FULL OUTER JOIN casegrp ON actlup.activity_code = casegrp.id_code "
    
    End If
    
    
 
        If txtCode.Value <> "" Then
    
            If conditval = 0 Then
        
                strSQL3p2 = strSQL3p2 & "WHERE actlup.activity_code LIKE '%" & txtCode.Value & "%'"
                conditval = conditval + 1
        
            ElseIf conditval > 0 Then
                    
                strSQL3p2 = strSQL3p2 & " AND actlup.activity_code LIKE '%" & txtCode.Value & "%'"
                conditval = conditval + 1
                    
             End If
                 
            
        End If
    
    
        If txtDesc.Value <> "" Then
    
            If conditval = 0 Then
        
                strSQL3p2 = strSQL3p2 & "WHERE actlup.activity_desc LIKE '%" & txtDesc.Value & "%'"
                conditval = conditval + 1
        
            ElseIf conditval > 0 Then
                    
                strSQL3p2 = strSQL3p2 & " AND actlup.activity_desc LIKE '%" & txtDesc.Value & "%'"
                conditval = conditval + 1
                    
            End If
                 
        End If
 
    
        If cmbDocs.Value <> "All Activities" Then
    
            If cmbDocs.Value = "Documents Only" Then
       
                If conditval = 0 Then
        
                    strSQL3p2 = strSQL3p2 & "WHERE actlup.doc_flag = 'y'"
                    conditval = conditval + 1
            
                ElseIf conditval > 0 Then
                    
                    strSQL3p2 = strSQL3p2 & " AND actlup.doc_flag = 'y'"
                    conditval = conditval + 1
                        
                End If
       
            End If
       
    
            If cmbDocs.Value = "Activities Only" Then
       
                If conditval = 0 Then
        
                    strSQL3p2 = strSQL3p2 & "WHERE actlup.doc_flag = 'n'"
                    conditval = conditval + 1
            
                ElseIf conditval > 0 Then
                    
                    strSQL3p2 = strSQL3p2 & " AND actlup.doc_flag = 'n'"
                    conditval = conditval + 1
                        
                End If
       
            End If
        
        End If
    
        
        If cmbDocType.Value <> "All Doc Types" Then
        
            If conditval = 0 Then
                
                strSQL3p2 = strSQL3p2 & "WHERE doctyp.wp_code = '" & cmbDocType.Value & "'"
                conditval = conditval + 1
                
            ElseIf conditval > 0 Then
                    
                strSQL3p2 = strSQL3p2 & " AND doctyp.wp_code = '" & cmbDocType.Value & "'"
                conditval = conditval + 1
                        
            End If
    
        End If
            
        
        If cmbGroup.Value <> "All Groups" Then
        
           If conditval = 0 Then
        
                strSQL3p2 = strSQL3p2 & "WHERE casegrp.main_group = '" & cmbGroup.Value & "'"
                conditval = conditval + 1
                
            ElseIf conditval > 0 Then
                    
                strSQL3p2 = strSQL3p2 & " AND casegrp.main_group = '" & cmbGroup.Value & "'"
                conditval = conditval + 1
                        
            End If
    
        End If
        
    
    
    strSQL3 = strSQL3p1 & strSQL3p2 & " ORDER BY actlup.activity_code"
    
    
    Set Rs3 = New ADODB.Recordset
    Rs3.Open strSQL3, AdoCon3, adOpenDynamic, adLockPessimistic
        
    Dim loopval As Long
    loopval = 9
     
    Do Until Rs3.EOF
 
   Sheet2.Cells(loopval, "a").Value = Rs3!activity_code
   Sheet2.Cells(loopval, "c").Value = Rs3!activity_desc
    
    
    loopval = loopval + 1
    Rs3.MoveNext
    Loop
MsgBox strSQL3
AdoCon3.Close
Set Rs3 = Nothing
Set AdoCon3 = Nothing
 
Open in New Window Tags: 
Microsoft, Excel, 2003, VBA
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros