Updating and Access table with more than 127 columns

  • Thread starter Thread starter Wayne
  • Start date Start date
W

Wayne

Due to an existing limitation of Access, you cannot issue
an UPDATE query with more than 127 fields (Access has a
limit of 255 fields and during an update query, both the
column your are updating and the value your are inserting
are treated as fields, which therefore limits you to
having 127 columns in your query).

ADO didn't have a problem with this if you used a
connected recordset (I'm assuming because it uses cursors
or something). Since updates are submitted via UPDATE
queries in ADO.NET this becomes an issue.

Making the table have less than 127 columns is not an
option as there are existing implmentations of the
database (using old clients that used VB and ADO that
worked...).

Only updating the 'changed' fields does not solve the
problem as the problem still exists when you change more
thant 127 fields...

There are issues with splitting the UPDATE into 127 field
chucks if some of the fields more then one chunk are
marked as 'required'.

My question is, is there any way to deal with this issue
in ADO.NET? I don't want to have to resort to using
interop and old ADO recordsets but it appears to me like
this is the only way to deal with this issue in a generic
way.

Thanks
Wayne
 
Hi Wayne,

A lame solution: create a stored proc (Query) which takes only few
parameters.
Pack (somewow) all parameters into one or few of them.
Unpack them at server side and execute an action.
 
If you can't break up the large table and you don't want to use
Interop, then your only other option is to issue multiple UPDATE
statements against the same row(s). Ugly, but there's no other way Jet
can process it since it doesn't support any kind of programmable logic
in QueryDef objects.

-- Mary
MCW Technologies
http://www.mcwtech.com
 
¤ Due to an existing limitation of Access, you cannot issue
¤ an UPDATE query with more than 127 fields (Access has a
¤ limit of 255 fields and during an update query, both the
¤ column your are updating and the value your are inserting
¤ are treated as fields, which therefore limits you to
¤ having 127 columns in your query).
¤
¤ ADO didn't have a problem with this if you used a
¤ connected recordset (I'm assuming because it uses cursors
¤ or something). Since updates are submitted via UPDATE
¤ queries in ADO.NET this becomes an issue.
¤
¤ Making the table have less than 127 columns is not an
¤ option as there are existing implmentations of the
¤ database (using old clients that used VB and ADO that
¤ worked...).
¤
¤ Only updating the 'changed' fields does not solve the
¤ problem as the problem still exists when you change more
¤ thant 127 fields...
¤
¤ There are issues with splitting the UPDATE into 127 field
¤ chucks if some of the fields more then one chunk are
¤ marked as 'required'.
¤
¤ My question is, is there any way to deal with this issue
¤ in ADO.NET? I don't want to have to resort to using
¤ interop and old ADO recordsets but it appears to me like
¤ this is the only way to deal with this issue in a generic
¤ way.

I think Miha is on the right track. If you are using a SQL statement, try converting it to an Access
parameterized QueryDef and use the OleDbCommand object (CommandType.StoredProcedure). I can't
guarantee it will work but it's worth a try.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
I think Miha is on the right track. If you are using a SQL statement, try converting it to an Access
parameterized QueryDef and use the OleDbCommand object (CommandType.StoredProcedure). I can't
guarantee it will work but it's worth a try.
Click to expand...

My bet is you'll have the same limitations that you would if you just
passed direct SQL. A saved QueryDef object is the same SQL statement
persisted in the Access system tables, and it's hard to visualize why
or how it would process it differently at runtime.

-- Mary
MCW Technologies
http://www.mcwtech.com
 
Back
Top