esps
asked on
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.OL EDB.4.0;Da ta 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").R ows
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
It must be early in the morning and I am missing something probably simple.
My code:
Dim conn As String = "Provider=Microsoft.Jet.OL
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(
builder1.QuotePrefix = "["
builder1.QuoteSuffix = "]"
For Each row In topics1.Tables("report").R
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
ASKER
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
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
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
Sorry ...
mx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
... Or has too many fields :-)
mx
mx
is 'Report' the query?
Can you post the SQL for that query?
mx