Getting "Query is too complex" or "Expression is too complex” when using Access

[アーティクル]
02/06/2009

The scenario, you have an MS Access database and you want to run queries against it. A fairly common thing to want to do J

However, now you are getting the following exception, assuming you are running a .Net application:

System.Data.OleDb.OleDbException: Query is too complex.

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)

at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

…

It may be “Expression is too complex” instead of “Query is too complex”. What is going on?

Well, there is a limit in the Jet engine on how many ANDs you can have in a SQL statement.

Bring on the demo that shows this.

. Create a new .mdb file called QueryRepro.mdb containing the following fields.

ID AutoNumber (primary key)

ColOne Number

ColTwo Number

. Insert a row where ColOne and ColTwo has a value of 200, and save the table as QueryTable.

. Create a new C# console application and replace the main method with the following:

static void Main(string[] args)

{

try

{

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\<your location>\QueryRepro.mdb";

OleDbConnection con = new OleDbConnection(connectionString);

con.Open();

Console.WriteLine("Connection state is {0}", con.State.ToString());

// Create the update that have a too high number of AND's

StringBuilder updateCommandText = new StringBuilder("UPDATE QueryTable SET ColOne = 1 WHERE (ColTwo <> 100)");

int andCount = 100;

// Append the AND's

for (int i = 0; i < andCount; i++)

{

updateCommandText.Append(String.Format(" AND (ColTwo <> {0})", i));

}

OleDbCommand cmd = new OleDbCommand(updateCommandText.ToString(), con);

Console.WriteLine(cmd.CommandText);

// We can use this to count the number of AND's

MatchCollection andCollection = Regex.Matches(cmd.CommandText, "AND");

Console.WriteLine("\nNumber of ANDs: {0}\n", andCollection.Count.ToString());

// This will fail

cmd.ExecuteNonQuery();

con.Close();

Console.WriteLine("Connection state is {0}", con.State.ToString());

}

catch (Exception ex)

{

Console.WriteLine(ex);

}

. And run it, it should give you the exception above. Try to lower the andCount and the application should execute fine.

The application should be self-explanatory, all it does is to add a lot of ANDs to the SQL.

You probably will not handwrite a statement that is as long as the one created above, even though there may be occasions when the commands are built in a loop.

So when can the statement be complex without you implicitly creating it?

One situation could be when you are using DataAdapters. The DataAdapter uses Optimistic concurrency for its updates, this means that there will be a fairly long update statement.

This is perhaps easiest to show. In the example above, comment out everything between the connection Open() and Close() and insert this code and run it.

OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM QueryTable", con);

OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(oda);

oda.UpdateCommand = cmdBuilder.GetUpdateCommand();

Console.WriteLine(oda.UpdateCommand.CommandText);

MatchCollection andCollection = Regex.Matches(oda.UpdateCommand.CommandText, "AND");

Console.WriteLine("\nNumber of ANDs: {0}\n", andCollection.Count.ToString());

This will create an update command that looks as follows (4 ANDs):

UPDATE QueryTable SET ColOne = ?, ColTwo = ? WHERE ((ID = ?) AND ((? = 1 AND ColOne IS NULL) OR (ColOne = ?)) AND ((? =1 AND ColTwo IS NULL) OR (ColTwo = ?)))

Basically, the more columns you have in your database, more ANDs and more complexity.

In summary, you will get the described exception due to the limit of ANDs

KBs that discusses this and an article describing the command builder.

"Error message when you run a query in Access 2007: "System Resource Exceeded" or "Query is too complex" "

https://support.microsoft.com/default.aspx?scid=kb;EN-US;918814

"ACC2000: Limit on ANDs in SQL Select Statement"

https://support.microsoft.com/default.aspx?scid=kb;EN-US;209761

"ACC: Limit on ANDs in SQL Select Statement"

https://support.microsoft.com/default.aspx?scid=kb;EN-US;92690

"HOWTO: Update More Than 40 Fields in an Access (Jet) Database"

https://support.microsoft.com/default.aspx?scid=kb;EN-US;192716

"Generating Commands with CommandBuilders (ADO.NET)"