I'm trying to update an access .mdb file. The file contains one table with 115 fields. I have estabished a connection to the database. I'm able to select the datarow I want to update. I can see it when I debug the code. I change two fields in the datarow by using thier index.

When I try to update the table adapter I get an error. The error message is "Query to complex". If I run the same code with another database with one table of fewer fields it runs OK. No errors.

Why am I getting this error and what can I do to work around it.

Thanks

check the below link

---

Narayanan Dayalan - Zeetaa Business Solutions ------- Please "Mark As Answer", if my answer works well with ur Query

Your problem occurs because you are passing some data that is not acceptable in the table column.

A quick search on the net give the following threads. Please go through it and check the solutions.

http://forums.techguy.org/business-applications/40875-access-query-too-complex-error.html

http://www.access-programmers.co.uk/forums/archive/index.php/t-85766.html

http://www.access-programmers.co.uk/forums/archive/index.php/t-80247.html

---

Thanks, A.m.a.L Dot Net Goodies
Don't hate the hacker, hate the code

Thanks for the response! I'm including the code below. All the fields seem to be right. I tried using CDate("12/12/11912") instead of the date formatting code. No change.

This should be simple, as it works with tbl_Logbookbut not with Table_HRD_Contacts_V01. I know I've got the "log" row as I can see it in the debugger. Maybe you will see something I don't.

Here are the column definitions in each table and the VB code.

QSLSentDate Date/Time 8
AllowZeroLengthFalse
AppendOnly: False
Attributes: Fixed Size
CollatingOrder:General
DataUpdatable: False
GUID: {guid {47FAFAE4-8169-443D-B2BC-02E1
OrdinalPosition19
Required: False
SourceField: QSLSentDate
SourceTable: TBL_LOGBOOK

QSLSent Text 10
AllowZeroLengthFalse
AppendOnly: False
Attributes: Variable Length
CollatingOrder:General
DataUpdatable: False
GUID: {guid {BC6C483F-E545-46B5-BF12-AF46
OrdinalPosition17

Required: False
SourceField: QSLSent
SourceTable: TBL_LOGBOOK

COL_QSLSDATE Date/Time 8
AllowZeroLengthFalse
AppendOnly: False
Attributes: Fixed Size
CollatingOrder:General
DataUpdatable: False
OrdinalPosition76
Required: False
SourceField: COL_QSLSDATE
SourceTable: TABLE_HRD_CONTACTS_V01

COL_QSL_SENT Text 2
AllowZeroLengthFalse
AppendOnly: False
Attributes: Variable Length
CollatingOrder:General
DataUpdatable: False
OrdinalPosition79
Required: False
SourceField: COL_QSL_SENT
SourceTable: TABLE_HRD_CONTACTS_V01

Private Sub Update_DataFile(ByVal intKey As Integer)

On Error GoTo Err_Update_DataFile

Dim intIndex As Integer = 0
Dim FilterExp As String = Nothing
Dim strMsg As String = Nothing
Dim Rows() As DataRow

FilterExp = Log.Tables(0).Columns(0).ColumnName & " = '" & CStr(intKey) & "'"
Rows = Log.Tables(strTable).Select(FilterExp, Nothing, _
DataViewRowState.CurrentRows)
If Rows.Count = 0 Then
MsgBox(Me.DataGridView1.Rows(intIndex).Cells(2).Value & _
" matching record not found.", _
MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, _
"Error!")
strMsg = Me.DataGridView1.Rows(intIndex).Cells(1).Value & " " & _
Me.DataGridView1.Rows(intIndex).Cells(2).Value & " " & _
Me.DataGridView1.Rows(intIndex).Cells(3).Value & " " & _
Me.DataGridView1.Rows(intIndex).Cells(4).Value
Call Write_Log(strMsg)
Else
If strTable = "tbl_Logbook" Then
Log.Tables(0).Rows(0).Item(17) = "Y"
Log.Tables(0).Rows(0).Item(19) = FormatDateTime(Now(), _
DateFormat.ShortDate).ToString
Else
Log.Tables(0).Rows(0).Item(76) = FormatDateTime(Now(), _
DateFormat.ShortDate).ToString
Log.Tables(0).Rows(0).Item(79) = "Y"
End If

Me.Validate()
myDALog.Update(Log.Tables(0).GetChanges(DataRowState.Modified))
Log.AcceptChanges()
End If
DataGridView1.Refresh()

Exit_Update_DataFile:

On Error GoTo 0
Exit Sub

Err_Update_DataFile:

Call PlaySoundFile(sChord)
MsgBox(Err.Number)
MsgBox(Err.Description & " Update DataFile.")
Resume Exit_Update_DataFile

End Sub

"Query to complex" Is this the query :

---

Don't judge me, just Upgrade me. Thanks!

Malange, let me explain how the code above flows. A connection to an external database is made usingTwo table adapters and two dataset have been created. Both from the same .mdb database. One "Grid" is used to populate a DataGridView control with specific columns from the database table. The other "Log" all of the columns from the database table. When a datarow is selected in the datagridview control its primary key is loaded into intKey.
Then the query is used to select the datarow from the dataset containing the full datarow. That datarow is then updated.

The selection query run without problem. I can see the datarow with the correct data in the debuger. The problem comes when I try to update the underlying database table.

Now the interesting thing is that when I use the datatable tbl_logbook the update takes place with no errors and works correctly. However if I use the datatable Table_HRD_Contacts_V01 the update fails and the error message is "Query to complex". Note the select query has already run at the time the update is called. It's function is to get the datarow for update. As an aside, I did not have success using a partial datarow (grid) for update only a full datarow (log) would work.

The main difference in the two table is the number of fields. tbl_logbook has 59 fields and Table_HRD_Contacts_V01 has 115.

The question is why can I update tbl_Logbook and cannot update Table_HRD_Contacts_V01 when both go through the same routine from the connection to the database on to the update statement.

Hope this explanation helps.