VBA ADO - "Query is too complex"

[ClimoC]

Hello all

Running this...

Public Sub DBQueryRunExecution()

     Dim DBFullName As String
     Dim Cnct As String, Src As String
     Dim Connection As ADODB.Connection
     Dim Recordset As ADODB.Recordset
     Dim Col As Integer
     Dim LR As Long
     Dim DB2U As Long
     Dim FilterStr As String
     
     UserForm1.Show
     Application.ScreenUpdating = False
     Application.EnableEvents = False
     Application.Calculation = xlCalculationManual
     
     Worksheets("Sheet1").Activate
     Cells.Clear
     FilterStr = CreateFilterString
'initiate loops through all DB's found within date range
For DB2U = FromWeek To ToWeek Step 1

     DBFullName = "U:\BIG DB THING\Week" & DB2U & "test.accdb"
     Set Connection = New ADODB.Connection
     Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
     Cnct = Cnct & "Data Source=" & DBFullName & ";"
     Connection.Open ConnectionString:=Cnct

     Set Recordset = New ADODB.Recordset
          With Recordset
                .LockType = adLockOptimistic
                .CursorLocation = adUseClient
                .CursorType = adOpenStatic
'    Define the appropriate Filter(s) and notify the user of the selection criteria.
               Src = "Select * from Wk" & DB2U & " where Title <> '' "
               .Open Source:=Src, ActiveConnection:=Connection

'    Write the field names.
          For Col = 0 To Recordset.Fields.Count - 1
               Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
          Next
 'Apply Filters in two steps - First, delete all records that are outside the dateranges or groups
End With
 'Apply Filters - Step 1

With Recordset
    .MoveFirst
    If Not .EOF = True Then
        Do Until .EOF = True

                .MoveFirst
                If Not "*" & .Fields("Channel").Value & "*" Like Chans Then
                    .Delete
                    .MoveNext
                ElseIf Not .Fields("Date").Value >= FromDate And Not .Fields("Date").Value <= ToDate Then
                        .Delete
                        .MoveNext
                End If
        .MoveNext
        Loop
    End If

'Apply Filters - Step 2

Recordset.Filter = FilterStr
'Dump Data to Sheet
               Range("A65000").End(xlUp).Offset(1, 0).CopyFromRecordset Recordset
          
          End With
     Set Recordset = Nothing
      '   Connection.Close
     Set Connection = Nothing
     
'Loop (goto next DB in range)
Next DB2U
'    Create and format the table from the Recordset.
     'run "formatasaccess"
     
     Application.EnableEvents = True
     Application.Calculation = xlCalculationAutomatic
     Application.ScreenUpdating = True

End Sub

Public Sub formatasaccess()

LR = Cells(Rows.Count, "A").End(xlUp).Row
     Range("A1").Select
          ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$M" & LR), _
                    , xlYes).Name = "INC2tbl"
          ActiveSheet.ListObjects("INC2tbl").ShowTotals = True
     ActiveSheet.Columns.AutoFit

     Sheets("Sheet1").Activate
          Range("A1").Select
End Sub

Public Function CreateFilterString() As String
Dim TempStr As String

                    Select Case True
                            Case F2OoA = "AND" And F3OoA = "AND" And F4OoA = "AND"   '1,1,1
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' AND " & _
                                                                        F2F & " " & F2O & " '" & F2C & "' AND " & _
                                                                        F3F & " " & F3O & " '" & F3C & "' AND " & _
                                                                        F4F & " " & F4O & " '" & F4C & "'"
                            Case F2OoA = "OR" And F3OoA = "AND" And F4OoA = "AND"   '2,1,1
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' OR (" & _
                                                                        F2F & " " & F2O & " '" & F2C & "' AND " & _
                                                                        F3F & " " & F3O & " '" & F3C & "' AND " & _
                                                                        F4F & " " & F4O & " '" & F4C & "')"
                            Case F2OoA = "AND" And F3OoA = "OR" And F4OoA = "AND"   '1,2,1
                                    TempStr = "(" & F1F & " " & F1O & " '" & F1C & "' AND " & _
                                                                        F2F & " " & F2O & " '" & F2C & "') OR (" & _
                                                                        F3F & " " & F3O & " '" & F3C & "' AND " & _
                                                                        F4F & " " & F4O & " '" & F4C & "')"
                            Case F2OoA = "AND" And F3OoA = "AND" And F4OoA = "OR"   '1,1,2
                                    TempStr = "(" & F1F & " " & F1O & " '" & F1C & "' AND " & _
                                                                        F2F & " " & F2O & " '" & F2C & "' AND " & _
                                                                        F3F & " " & F3O & " '" & F3C & "') OR " & _
                                                                        F4F & " " & F4O & " '" & F4C & "'"
                            Case F2OoA = "OR" And F3OoA = "OR" And F4OoA = "AND"  '2,2,1
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' OR " & _
                                                                        F2F & " " & F2O & " '" & F2C & "' OR (" & _
                                                                        F3F & " " & F3O & " '" & F3C & "' AND " & _
                                                                        F4F & " " & F4O & " '" & F4C & "')"
                            Case F2OoA = "OR" And F3OoA = "AND" And F4OoA = "OR"  '2,1,2
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' OR (" & _
                                                                        F2F & " " & F2O & " '" & F2C & "' AND " & _
                                                                        F3F & " " & F3O & " '" & F3C & "') OR " & _
                                                                        F4F & " " & F4O & " '" & F4C & "'"
                            Case F2OoA = "AND" And F3OoA = "OR" And F4OoA = "OR"  '1,2,2
                                    TempStr = "(" & F1F & " " & F1O & " '" & F1C & "' AND " & _
                                                                        F2F & " " & F2O & " '" & F2C & "') OR " & _
                                                                        F3F & " " & F3O & " '" & F3C & "' OR " & _
                                                                        F4F & " " & F4O & " '" & F4C & "'"
                            Case F2OoA = "OR" And F3OoA = "OR" And F4OoA = "OR"  '2,2,2
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' OR " & _
                                                                        F2F & " " & F2O & " '" & F2C & "' OR " & _
                                                                        F3F & " " & F3O & " '" & F3C & "' OR " & _
                                                                        F4F & " " & F4O & " '" & F4C & "'"
                                                                        
                                                                        
                            Case F2OoA = "AND" And F3OoA = "AND" And F4OoA = ""  '1,1,_
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' AND " & _
                                                                        F2F & " " & F2O & " '" & F2C & "' AND " & _
                                                                        F3F & " " & F3O & " '" & F3C & "'"
                            Case F2OoA = "AND" And F3OoA = "OR" And F4OoA = ""  '1,2,_
                                    TempStr = "(" & F1F & " " & F1O & " '" & F1C & "' AND " & _
                                                                        F2F & " " & F2O & " '" & F2C & "') OR " & _
                                                                        F3F & " " & F3O & " '" & F3C & "'"
                            Case F2OoA = "OR" And F3OoA = "AND" And F4OoA = ""  '2,1,_
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' OR (" & _
                                                                        F2F & " " & F2O & " '" & F2C & "' AND " & _
                                                                        F3F & " " & F3O & " '" & F3C & "')"
                            Case F2OoA = "OR" And F3OoA = "OR" And F4OoA = ""  '2,2,_
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' OR " & _
                                                                        F2F & " " & F2O & " '" & F2C & "' OR " & _
                                                                        F3F & " " & F3O & " '" & F3C & "'"

                            Case F2OoA = "AND" And F3OoA = "" And F4OoA = ""  '1,_,_
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' AND " & _
                                                                        F2F & " " & F2O & " '" & F2C & "'"
                            Case F2OoA = "OR" And F3OoA = "" And F4OoA = ""  '2,_,_
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' OR " & _
                                                                        F2F & " " & F2O & " '" & F2C & "'"
                                                                        
                            Case F2OoA = "" And F3OoA = "" And F4OoA = ""  '_,_,_
                                    TempStr = F1F & " " & F1O & " '" & F1C & "'"
                                    
                    End Select

 CreateFilterString = TempStr
    
End Function

And get "Query is too complex" when trying to do any kind of cursor movement or action (delete, movenext etc)

I read somewhere that the table width can be a problem - this one is something like 200 columns wide (in excel goes from A:HH when dumped)

Could this be my problem? Or have I made a fundamental error somewhere that's causing this problem?

The filters work fine. It's just the delete/movenext loop that crashes

Using ADO 2.8, Access and Excel 14.0

Thanks
C

 

[boblarson]

First off - do NOT use Reserved Words for your variable names. Do NOT use RECORDSET as a variable name. Do NOT use CONNECTION as a variable name.

Next, the SQL String should be:

Src = "Select * from Wk" & DB2U & " where Title <> ' '"

 

[ClimoC]

Thanks. Will amend the variant names...

I got it working just by reworking the If statement in the loop. It seems to now be handling everything I throw at it

Option Explicit

Global FromWeek As Long, ToWeek As Long, Chans As String, FromDate As Date, ToDate As Date, _
                    DRorWEEK As String, FromYear As Long, ToYear As Long, _
                    F1F As String, F1O As String, F1C As Variant, _
                    F2F As String, F2O As String, F2C As Variant, F2OoA As String, _
                    F3F As String, F3O As String, F3C As Variant, F3OoA As String, _
                    F4F As String, F4O As String, F4C As Variant, F4OoA As String
                    
Enum ADOConstants
    adOpenStatic = 3
    adUseClient = 3
    adVarChar = 200
End Enum

Public Sub DBQueryRunExecution()

     Dim DBFullName As String
     Dim Cnct As String, Src As String
     Dim Conn As ADODB.Connection
     Dim Recset As ADODB.Recordset
     Dim Col As Integer
     Dim LR As Long
     Dim DB2U As Long
     Dim FilterStr As String
     Dim i As Integer
     UserForm1.Show


     Application.ScreenUpdating = False
     Application.EnableEvents = False
     Application.Calculation = xlCalculationManual
    i = 0
     Worksheets("Sheet1").Activate
     Cells.Clear
     FilterStr = CreateFilterString
'initiate loops through all DB's found within date range
For DB2U = FromWeek To ToWeek Step 1
    i = i + 1
    StatusBar.Label11 = "Connecting to Database " & i & " of " & (ToWeek - FromWeek) + 1

     DBFullName = "U:\BIG DB THING\Week" & DB2U & "test.accdb"
     Set Conn = New ADODB.Connection
     Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
     Cnct = Cnct & "Data Source=" & DBFullName & ";"
     Conn.Open ConnectionString:=Cnct

     Set Recset = New ADODB.Recordset
          With Recset
                .LockType = adLockBatchOptimistic
                .CursorLocation = adUseClient
                .CursorType = adOpenStatic
'    Define the appropriate Filter(s) and notify the user of the selection criteria.
               Src = "Select * from Wk" & DB2U & " where Title <> ' ' "
               .Open Source:=Src, ActiveConnection:=Conn
                StatusBar.Label11 = "Searching Database " & i & " of " & (ToWeek - FromWeek) + 1 & " (" & .RecordCount & " Records found)"
                StatusBar.Repaint
'    Write the field names.
          For Col = 0 To Recordset.Fields.Count - 1
               Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
          Next
 'Apply Filters in two steps - First, delete all records from memory that are outside the dateranges or groups
End With
 'Apply Filters - Step 1
With Recset
    .MoveFirst
    If Not .EOF = True Then
        Do Until .EOF = True
                Debug.Print .CursorLocation & "/" & .RecordCount
                If Not VBA.InStr(1, Chans, Recset.Fields("Channel").Value, vbTextCompare) > 1 Then
                    .Delete

                ElseIf Not .Fields("Date").Value >= FromDate And Not .Fields("Date").Value <= ToDate Then
                        .Delete

                Else

                End If
        .MoveNext
        Loop
    End If

'Apply Filters - Step 2

Recset.Filter = FilterStr
'Dump Data to Sheet
               Range("A65000").End(xlUp).Offset(1, 0).CopyFromRecordset Recset
          
          End With
     Set Recset = Nothing
      '   Connection.Close
     Set Conn = Nothing
     
'Loop (goto next DB in range)
Next DB2U
'    Create and format the table from the Recordset.
     'run "formatasaccess"
StatusBar.Label11 = "Complete. Close Window when you're ready"

StatusBar.Repaint
Sheets("Sheet1").Columns("E:G").NumberFormat = "[hh]:mm:ss"
     Application.EnableEvents = True
     Application.Calculation = xlCalculationAutomatic
     Application.ScreenUpdating = True

End Sub

Public Sub formatasaccess()

LR = Cells(Rows.Count, "A").End(xlUp).Row
     Range("A1").Select
          ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$M" & LR), _
                    , xlYes).Name = "INC2tbl"
          ActiveSheet.ListObjects("INC2tbl").ShowTotals = True
     ActiveSheet.Columns.AutoFit

     Sheets("Sheet1").Activate
          Range("A1").Select
End Sub

Public Function CreateFilterString() As String
Dim TempStr As String

                    Select Case True
                            Case F2OoA = "AND" And F3OoA = "AND" And F4OoA = "AND"   '1,1,1
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' AND " & _
                                                                        F2F & " " & F2O & " '" & F2C & "' AND " & _
                                                                        F3F & " " & F3O & " '" & F3C & "' AND " & _
                                                                        F4F & " " & F4O & " '" & F4C & "'"
                            Case F2OoA = "OR" And F3OoA = "AND" And F4OoA = "AND"   '2,1,1
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' OR (" & _
                                                                        F2F & " " & F2O & " '" & F2C & "' AND " & _
                                                                        F3F & " " & F3O & " '" & F3C & "' AND " & _
                                                                        F4F & " " & F4O & " '" & F4C & "')"
                            Case F2OoA = "AND" And F3OoA = "OR" And F4OoA = "AND"   '1,2,1
                                    TempStr = "(" & F1F & " " & F1O & " '" & F1C & "' AND " & _
                                                                        F2F & " " & F2O & " '" & F2C & "') OR (" & _
                                                                        F3F & " " & F3O & " '" & F3C & "' AND " & _
                                                                        F4F & " " & F4O & " '" & F4C & "')"
                            Case F2OoA = "AND" And F3OoA = "AND" And F4OoA = "OR"   '1,1,2
                                    TempStr = "(" & F1F & " " & F1O & " '" & F1C & "' AND " & _
                                                                        F2F & " " & F2O & " '" & F2C & "' AND " & _
                                                                        F3F & " " & F3O & " '" & F3C & "') OR " & _
                                                                        F4F & " " & F4O & " '" & F4C & "'"
                            Case F2OoA = "OR" And F3OoA = "OR" And F4OoA = "AND"  '2,2,1
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' OR " & _
                                                                        F2F & " " & F2O & " '" & F2C & "' OR (" & _
                                                                        F3F & " " & F3O & " '" & F3C & "' AND " & _
                                                                        F4F & " " & F4O & " '" & F4C & "')"
                            Case F2OoA = "OR" And F3OoA = "AND" And F4OoA = "OR"  '2,1,2
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' OR (" & _
                                                                        F2F & " " & F2O & " '" & F2C & "' AND " & _
                                                                        F3F & " " & F3O & " '" & F3C & "') OR " & _
                                                                        F4F & " " & F4O & " '" & F4C & "'"
                            Case F2OoA = "AND" And F3OoA = "OR" And F4OoA = "OR"  '1,2,2
                                    TempStr = "(" & F1F & " " & F1O & " '" & F1C & "' AND " & _
                                                                        F2F & " " & F2O & " '" & F2C & "') OR " & _
                                                                        F3F & " " & F3O & " '" & F3C & "' OR " & _
                                                                        F4F & " " & F4O & " '" & F4C & "'"
                            Case F2OoA = "OR" And F3OoA = "OR" And F4OoA = "OR"  '2,2,2
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' OR " & _
                                                                        F2F & " " & F2O & " '" & F2C & "' OR " & _
                                                                        F3F & " " & F3O & " '" & F3C & "' OR " & _
                                                                        F4F & " " & F4O & " '" & F4C & "'"
                                                                        
                                                                        
                            Case F2OoA = "AND" And F3OoA = "AND" And F4OoA = ""  '1,1,_
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' AND " & _
                                                                        F2F & " " & F2O & " '" & F2C & "' AND " & _
                                                                        F3F & " " & F3O & " '" & F3C & "'"
                            Case F2OoA = "AND" And F3OoA = "OR" And F4OoA = ""  '1,2,_
                                    TempStr = "(" & F1F & " " & F1O & " '" & F1C & "' AND " & _
                                                                        F2F & " " & F2O & " '" & F2C & "') OR " & _
                                                                        F3F & " " & F3O & " '" & F3C & "'"
                            Case F2OoA = "OR" And F3OoA = "AND" And F4OoA = ""  '2,1,_
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' OR (" & _
                                                                        F2F & " " & F2O & " '" & F2C & "' AND " & _
                                                                        F3F & " " & F3O & " '" & F3C & "')"
                            Case F2OoA = "OR" And F3OoA = "OR" And F4OoA = ""  '2,2,_
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' OR " & _
                                                                        F2F & " " & F2O & " '" & F2C & "' OR " & _
                                                                        F3F & " " & F3O & " '" & F3C & "'"

                            Case F2OoA = "AND" And F3OoA = "" And F4OoA = ""  '1,_,_
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' AND " & _
                                                                        F2F & " " & F2O & " '" & F2C & "'"
                            Case F2OoA = "OR" And F3OoA = "" And F4OoA = ""  '2,_,_
                                    TempStr = F1F & " " & F1O & " '" & F1C & "' OR " & _
                                                                        F2F & " " & F2O & " '" & F2C & "'"
                                                                        
                            Case F2OoA = "" And F3OoA = "" And F4OoA = ""  '_,_,_
                                    TempStr = F1F & " " & F1O & " '" & F1C & "'"
                                    
                    End Select

 CreateFilterString = TempStr
    
End Function