Append query expression complexity limits

[Guest]

On 06 July I had posted a question to the forms coding DG asking about an
"Unspecified Error" message that I was receiving in processing a NotInList
event for a combo box. The action query involved the use of a nested select
to obtain one of the values from another table. I found that by doing the
lookup for that value in a separate query and just using the retrieved value
in the "insert" that the problem went away. I tried this simply ouut of
desperation, not because I was able to figure out what was going on. The text
of the original SQL was:

insert into Personnel (firstName, middleName, lastName, roleID)
values ("A", "B", "C', (select roleid from Roles where roleName = 'PM'));

I reconstructed the original in the QEB, by using SQL view to write the
query and then switching back to Design View. Access did not complain during
any of that, but when I tried to run the run the query to do the actual
append, I got the error message: Reserved Error (-3025); there is no message
for this error.

The question here is: is there a limit on the complexity of the expression
that can be used in an insert or update? I have not seen anything that
discusses limitations on complexity. In SQL Server or other client-server
DBMS's that I've used this would have been no problem.

I'm using Access 2000 with Window 2000 SP4.

TIA.

 

[Duane Hookom]

Try this:
Insert into Personnel (firstName, middleName, lastName, roleID)
SELECT "A", "B", "C', roleid
From Roles
Where roleName = 'PM';

 

[Guest]

Duane,

Thanks for the response. I tried that option also and it works just fine. My
real question for here though is: Why doesn't the original work? Is there
some documentation- posts, white papers, etc.- that anyone is aware of that
discusses the limitations?

Once again, thanks for the response.

 

[Duane Hookom]

There are bound to be complexity issues but I can't tell you where to find
the thresholds. Subqueries are typical causes of complexity issues with JET.