• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 870
  • Last Modified:

error Query is too complex when updating record

Hi Experts,
It must be early in the morning and I am missing something probably simple.

My code:
Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & "'" & TextBox1.Text & "'" & ";Persist Security Info=False"
        Dim cmd1 As String = "Select * from Report"
        Dim adapter1 As New OleDbDataAdapter(cmd1, conn)
        Dim topics1 As New DataSet()
        adapter1.Fill(topics1, "Report")
        Dim builder1 As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(adapter1)
        builder1.QuotePrefix = "["
        builder1.QuoteSuffix = "]"

For Each row In topics1.Tables("report").Rows

                If aa1 = 102 Then  ' variable value from elsewhere
                    row("Question102") = drCurrent("comment")   ' drCurrent("comment") value from other table
                Else
                    If aa1 < 98 Then
                        row("Question" & aa1) = drCurrent("result")    ' result from other table

                    End If
                End If

                builder1.GetUpdateCommand()
                adapter1.Update(topics1, "report")

Last line returns the error - Query is too Complex

All I basically want to do is to update an existing record in Access with VB 2005

regards
0
esps
Asked:
esps
  • 3
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Always tricky to troubleshoot.  

is 'Report'  the query?

Can you post the SQL for that query?

mx
0
 
espsAuthor Commented:
No, Report is the table, which I  build up from data in other tables.
The table 'report' contain X no of reports with only one column populated that I use as lookup.
I then 'page' through the table 'report' and when the value is 'true' I assign the value to the field and then want to update and this is where the problem starts.
ZS
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well ... I'm not that familiar with VB2005  so, not sure what I can offer here.  Usually in Access, 'too complex' can be the result of many different conditions, such as mis-spelled field name, function class on fields that may be null (ed CDate(someFieldThatIsNull) ... or ... a query that has multiple joins to other tables and or queries.  But ... doesn't seem like that is the case here.

Sorry ...

mx
0
 
espsAuthor Commented:
I found the problem!
I had more than 100 columns in the table.
When I tested with less than 100 it works fine.
Now I can do a workaround
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
... Or has too many fields :-)

mx
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now