error Query is too complex when updating record

Posted on 2007-07-24
Last Modified: 2008-01-09
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
                    If aa1 < 98 Then
                        row("Question" & aa1) = drCurrent("result")    ' result from other table

                    End If
                End If

                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

Question by:esps
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Always tricky to troubleshoot.  

    is 'Report'  the query?

    Can you post the SQL for that query?


    Author Comment

    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.
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    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 ...


    Accepted Solution

    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
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    ... Or has too many fields :-)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now