Problem updating Linked Table in MS Access

Posted in 2003

Topics: Installation, Setup & Upgrades, Connectivity: ODBC / JDBC / .NET, Platform-Specific Issues, Versions, Editions & End-of-Life

Richard Spitz — Thu, 23 Jan 2003 17:22:54 — source: IIUG Forums & Mailing Lists

Hi Informixers, my server is IDS 7.31UD5 on a Linux machine, and I have 2 client PCs running MS Access 2000 (now both SR1 + SP2) and I-Connect 2.70TC3. On the first machine, I already had a system DSN using the INFORMIX 3.80 32bit ODBC driver, which I used to link a table in an Informix database to an Access database on the PC. This table has a SERIAL column as primary key. When I linked the table, Access asked me what the PK column was, which I answered correctly. I can open the table and edit columns just fine, and the data are correctly written to the database. On the second PC, I created the same system DSN with the same ODBC driver, linked the same table, answered the PK question correctly etc. But I CANNOT update any data, I get error "-232 A SERIAL column column-name may not be updated". I am NOT changing the content of the SERIAL column! The original setup on the 2nd PC was Access 2000 w/o any SPs and the newest CSDK 2.80TC1 with ODBC driver 3.81, which gave the above error. I deinstalled CSDK 2.80TC1 and installed Connect 2.70TC3 as on the other machine and updated Access so now the two PCs are identically installed, but the error remains the same. I have no idea why this is working on one PC and not on the other. Does anyone know a solution? Regards, Richard -- +-------------------------------+---------------------------------------+ | Dr. med Richard Spitz | Mail: spitz@ana.med.uni-muenchen.de | | Klinik für Anaesthesiologie | Tel : +49-89-7095-6110 | | Klinikum der Univ. München | FAX : +49-89-7095-6420 | | 81366 München, Germany | GSM : +49-172-8933578 | +-------------------------------+---------------------------------------+

↪ replying to Richard Spitz

Mike — Thu, 23 Jan 2003 18:36:45 — source: IIUG Forums & Mailing Lists

--=====================_160024392==.ALT Content-Type: text/plain; charset="iso-8859-1"; format=flowed Content-Transfer-Encoding: quoted-printable I had the same problem; it went away by installing a newer version of the=20 Microsoft Jet Engine (we're using Jet Engine 4.0 SP 5 or 6 - they're freely= =20 available from the Microsoft site). Good luck! Mike (What's happening is that with the older version of the Jet Engine an=20 update to just one column of the table results in SQL being generated to=20 update all the columns, including the serial column. With the newer=20 version the SQL generated just updates the columns that actually got=20 changed. You can see this by turning ODBC tracing on) At 12:22 PM 1/23/03 -0500, Richard Spitz wrote: >Hi Informixers, > >my server is IDS 7.31UD5 on a Linux machine, and I have 2 client PCs >running MS Access 2000 (now both SR1 + SP2) and I-Connect 2.70TC3. > >On the first machine, I already had a system DSN using the INFORMIX 3.80 >32bit ODBC driver, which I used to link a table in an Informix database >to an Access database on the PC. This table has a SERIAL column as >primary key. When I linked the table, Access asked me what the PK column >was, which I answered correctly. I can open the table and edit columns >just fine, and the data are correctly written to the database. > >On the second PC, I created the same system DSN with the same ODBC >driver, linked the same table, answered the PK question correctly etc. >But I CANNOT update any data, I get error "-232 A SERIAL column >column-name may not be updated". I am NOT changing the content of the >SERIAL column! > >The original setup on the 2nd PC was Access 2000 w/o any SPs and >the newest CSDK 2.80TC1 with ODBC driver 3.81, which gave the above >error. I deinstalled CSDK 2.80TC1 and installed Connect 2.70TC3 as >on the other machine and updated Access so now the two PCs are >identically installed, but the error remains the same. > >I have no idea why this is working on one PC and not on the other. Does >anyone know a solution? > >Regards, Richard >-- >+-------------------------------+---------------------------------------+ >| Dr. med Richard Spitz | Mail: spitz@ana.med.uni-muenchen.de | >| Klinik f=FCr Anaesthesiologie | Tel : +49-89-7095-6110 | >| Klinikum der Univ. M=FCnchen | FAX : +49-89-7095-6420 | >| 81366 M=FCnchen, Germany | GSM : +49-172-8933578 | >+-------------------------------+---------------------------------------+ ************************************************* Michael Dunham-Wilkie, M.Sc., M.P.A. Senior Database Analyst Barrodale Computing Services Ltd. Tel: (250) 472-4372 Fax: (250) 472-4373 Web: http://www.barrodale.com Email: mike@barrodale.com ************************************************* Mailing Address: P.O. Box 3075 STN CSC Victoria BC Canada V8W 3W2 Shipping Address: Hut R, McKenzie Avenue University of Victoria Victoria BC Canada V8W 3W2 ************************************************* --=====================_160024392==.ALT Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <html> <body> I had the same problem; it went away by installing a newer version of the Microsoft Jet Engine (we're using Jet Engine 4.0 SP 5 or 6 - they're freely available from the Microsoft site). Good luck! Mike (What's happening is that with the older version of the Jet Engine an update to just one column of the table results in SQL being generated to update all the columns, including the serial column.  With the newer version the SQL generated just updates the columns that actually got changed.  You can see this by turning ODBC tracing on) At 12:22 PM 1/23/03 -0500, Richard Spitz wrote: <blockquote type=3Dcite class=3Dcite cite>Hi Informixers, my server is IDS 7.31UD5 on a Linux machine, and I have 2 client=20 PCs running MS Access 2000 (now both SR1 + SP2) and I-Connect 2.70TC3. On the first machine, I already had a system DSN using the INFORMIX 3.80 32bit ODBC driver, which I used to link a table in an Informix database to an Access database on the PC. This table has a SERIAL column as primary key. When I linked the table, Access asked me what the PK column was, which I answered correctly. I can open the table and edit columns just fine, and the data are correctly written to the database. On the second PC, I created the same system DSN with the same ODBC driver, linked the same table, answered the PK question correctly etc. But I CANNOT update any data, I get error "-232    A SERIAL column column-name may not be updated". I am NOT changing the content of the SERIAL column! The original setup on the 2nd PC was Access 2000 w/o any SPs and the newest CSDK 2.80TC1 with ODBC driver 3.81, which gave the above error. I deinstalled CSDK 2.80TC1 and installed Connect 2.70TC3 as on the other machine and updated Access so now the two PCs are identically installed, but the error remains the same. I have no idea why this is working on one PC and not on the other. Does anyone know a solution? Regards, Richard -- +-------------------------------+---------------------------------------+<br= > | Dr. med Richard Spitz<x-tab> </x-tab><x-tab>       &= nbsp;</x-tab>| Mail: spitz@ana.med.uni-muenchen.de<x-tab>   </x-tab>| | Klinik f=FCr Anaesthesiologie<x-tab>   </x-tab>| Tel : +49-89-7095-6110<x-tab>        </x-t= ab><x-tab>        </x-tab>| | Klinikum der Univ. M=FCnchen<x-tab>    </x-tab>| FAX : +49-89-7095-6420<x-tab>        </x-t= ab><x-tab>        </x-tab>| | 81366 M=FCnchen, Germany<x-tab>        </x-tab>| GSM : +49-172-8933578<x-tab> </x-tab><x-tab>     &nb= sp;  </x-tab><x-tab>       &nbs= p;</x-tab>| +-------------------------------+---------------------------------------+</b= lockquote> <x-sigsep></x-sigsep> ************************************************* Michael Dunham-Wilkie, M.Sc., M.P.A. Senior Database Analyst Barrodale Computing Services Ltd. Tel: (250) 472-4372 Fax: (250) 472-4373 Web: <a href=3D"http://www.barrodale.com/"; eudora=3D"autourl">http://www.barrodale.com</a>; Email: mike@barrodale.com ************************************************* Mailing Address: P.O. Box 3075 STN CSC Victoria BC Canada V8W 3W2 Shipping Address: Hut R, McKenzie Avenue University of Victoria Victoria BC Canada V8W 3W2 *************************************************

↪ replying to Richard Spitz

Spitz, Richard — Mon, 27 Jan 2003 08:56:07 — source: IIUG Forums & Mailing Lists

Mike, > I had the same problem; it went away by installing a newer version > of the Microsoft Jet Engine (we're using Jet Engine 4.0 SP 5 or 6 - > they're freely available from the Microsoft site). thanks for your help! That solved the problem. Regards, Richard -- +-------------------------------+---------------------------------------+ | Dr. med Richard Spitz | Mail: spitz@ana.med.uni-muenchen.de | | Klinik für Anaesthesiologie | Tel : +49-89-7095-6110 | | Klinikum der Univ. München | FAX : +49-89-7095-6420 | | 81366 München, Germany | GSM : +49-172-8933578 | +-------------------------------+---------------------------------------+

Related threads

Glossary

IDS (Informix Dynamic Server)