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!!!

ODBC--connection to unlocking failed

We are using an Access 2002 front-end with SQL Server 2000 as the database.

There is a table (dbo_PrivateGroup) that if you try to open it in the table view, in a query, in a report, or through the application, you get the following Error message:

ODBC--connection to 'unlocking' failed.

Does anyone know what this means and how I am supposed to get around this?

I tried it on the actual server itself and I can read the table through Access, through the application when it is running, and through Enterprise Manager. This machine is logged in with "Administrator" as the user id.

The table is on a different database than the other tables in the application.

It is a user table. And I checked the permissions and all users have select, insert, and delete permissions allowed. However, when I go to look at the columns property, none are checked for any user and when I go to check the boxes for all columns, I hit Apply, then Close. When I go back into that screen for that given user, the columns weren't saved.

My database knowledge is creating tables, stored procedures, and triggers. They don't have a DBA here and my knowledge is limited. Is the problem in SQL Server or Access?

I don't know if any of this makes sense. But if it does, any help would be greatly appreciated.

Thanks.Ive never seen the error, but we use Access 2000 it could be a new one. As its an ODBC error I would say that the problem is on the Access side because Access cant get to the SQL Server. Do your users have read, write and create permissions in the Access directory? Access creates a lock file called databasename.ldb (Well, Access 2000 does). If you cant create the lock file, Access cant maintain the locks.

When you have given full permissions to the row, you dont need to give permissions to specific columns the user already has it, so SQL Server doesnt save it again. If you remove, say, select permissions on the row for a specific user, you will then be able to grant select permissions on specific columns.

Andy|||I think I got the problem solved.

I went and did an Update Table Link Manager and selected the ODBC setting that wasa for the database that held the table (since it was different than the rest of the tables). Once it updated that, it woreked perfectly. I just need to refresh the table link manager on each client that needs to see this table, which fortunately is only 2.