Access 2002 queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Why , when accessing a table in the database , you can have greater than 127
fields for an insert and retreive but are limited to 127 fields for an update.
 
Guessing that you have something like
UPDATE ...
Set TableA.FieldA = TableB.FieldA,
TableA.FieldB = TableB.Fieldb

GUESSING (again) that is probably being counted as four fields in the query.

So if you try to update more than 127 fields (2*127=254; 2*128 = 256) you
are running into the maximum of 255 fields allowed in a query.

If you really need to update more than 127 fields at one time then I suggest
you will have to break the update query into two queries. (I do wonder why
anyone would need to do this in a relational database)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
More Info , I am using a scada program called Adroit , it has a database
function that automatically creates the SQL statement . Following are the
statements that it creates for insert , update , and retreive (select from).
My question is does access count the table name in brackets as well as the
field name in brackets as two in the update statement.
as can be seen the insert and retrieve( select from) only need the table
name once and then the fields . why do you need the [table name ].[field] for
every field to that needs updating.
INSERT INTO
([FIELD 1],[FIELD 2]) VALUES (10,20)
SELECT [FIELD 1],[FIELD 2] FROM

UPDATE
SET
.[FIELD 1]= 10,
.[FIELD 2]=20

Ted H
 
I don't know. I can speculate that Access is counting one for the reference
before the equal and one for the reference after the equal.

I don't have access to the code that is used to convert the query to machine
language and even if I did I probably would not understand it.

It is a limitation. You will have to find a method to work around the
limitation.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ted H said:
More Info , I am using a scada program called Adroit , it has a database
function that automatically creates the SQL statement . Following are the
statements that it creates for insert , update , and retreive (select
from).
My question is does access count the table name in brackets as well as the
field name in brackets as two in the update statement.
as can be seen the insert and retrieve( select from) only need the table
name once and then the fields . why do you need the [table name ].[field]
for
every field to that needs updating.
INSERT INTO
([FIELD 1],[FIELD 2]) VALUES (10,20)
SELECT [FIELD 1],[FIELD 2] FROM

UPDATE
SET
.[FIELD 1]= 10,
.[FIELD 2]=20

Ted H

John Spencer said:
Guessing that you have something like
UPDATE ...
Set TableA.FieldA = TableB.FieldA,
TableA.FieldB = TableB.Fieldb

GUESSING (again) that is probably being counted as four fields in the
query.

So if you try to update more than 127 fields (2*127=254; 2*128 = 256)
you
are running into the maximum of 255 fields allowed in a query.

If you really need to update more than 127 fields at one time then I
suggest
you will have to break the update query into two queries. (I do wonder
why
anyone would need to do this in a relational database)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Click to expand...
Click to expand...
 
Back
Top