ODBC--update on a linked table table_name failed

Hi all,

Hope someone can help me with this one. Thanks in advance.

We have several Access 97 .mdb front-end applications running against an SQL Server 7 database on NT 4.0. I know, welcome to the way back machine. Any way, all was well and running great. Then the user attempted to run the application, it had been successfully run several times earlier in the day, and received the following error: "ODBC--update on linked table 'table_name' failed (Error 3157)". Everything I know suggests that this would be an MDAC version issue except for the abrupt change. From everything I can tell nothing was altered on the hosting server and we are running MDAC 2.5. I have performed the following tests using several different client systems, 98 and 2K, to no avail.

1. Cycled the power on the client and server systems.

2. Opened the linked table in data view mode and attempted to update a single column within a single row.

3. Created new DSN and re-linked tables.

4. Reloaded MDAC 2.5 on both client and server systems.

This only affects Access 97 clients though. If I create an Access 2K database and link to the same server/database/table the updates work just fine.

Any suggestions would be greatly appreciated.

Thanks,
RickHave you tried to update it to Mdac 2.7. This might resolve the issue.|||Originally posted by oj_n
Have you tried to update it to Mdac 2.7. This might resolve the issue.

I'm in the process of testing with the latest MDAC and ODBC drivers now. What I'm realy curious about though is the fact that this was running fine and then just quit. I have suspicions that something was altered on the server but I can't discover what it was and can't get anyone to admit to it. {;^>}

I know when I talk about Access 97, SQL 7 and MDAC 2.5 that it's all history. It's just the nature of the company I'm doing the work for. I'm just a contractor here. We have just migrated their, near 1 GB, Access 97 application/database to SQL server 7, 7 due to licensing issues, and Access 2000. However some front-end apps are still in Access 97. So they are pretty far behind the technology curve.

Thanks for the suggestion; I'll let you know how it turns out.

Thanks,
Rick|||Just in case anyone has the same problem described in my original post:

I discovered the cause and, as I suspected, it was from someone making a change to the SQL Server configuration. The User Option option had been modified to turn the NOCOUNT attribute on. Once I found this I was able to duplicate or fix the problem on a test SQL Server instance. I assume it has to do with Access 97 requireing a row count be returned from SQL Server through ODBC. Don't know exactly why.... yet.

Thanks,
Rick|||you can have better peformance, better flexibility, easier development

and a solution that will work for the next 10 years

if you just throw out Access 97 and convert everything to Access 2002 ADP Format.

Its a thin client to SQL Server.

and it ROCKS!!!