Error 3157: "ODBC - update on a linked table <table name> failed

[Jannah]

I started receiving the following error last week: "ODBC - Update on a linked
table <table name> failed" (error 3157). I'm not able to add records to the
database and the system is also preventing me from editing existing records.
I'm using Microsoft Access 2002 as the "front end" and SQL Server as the
"back end". Any suggestions?

 

[John Spencer]

You might see this message if the table does not have a primary key
defined. In most cases you also need a datetimestamp field in the table
if you are going to update the record.

One other possibility is that you may need to relink the table if the
table design was changed on the SQL server.

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

 

[Jannah]

Thanks for your response John.

The table does have a primary key, so I think that should be fine. And as
far as I know, the table design was not changed, but perhaps something
happened there. I'm not sure how to relink the tables. I didn't design this
database, so I'm afraid I don't know all the ins and outs of its construction.

If you have any other ideas or suggestions, I'd really appreciate your help.
I've tried restoring backups of the database, but the same problem keeps
happening. This is a heavily used database so I need to get it up and
running.

 

[John Spencer]

First, make a backup copy of your current database. Just in case things
go wrong.

Open the database in design view.
Select Tools: Utilities: Linked Table Manager
Check the table you are having a problem with
Click the Ok Button
Since this is an ODBC link you may be prompted to select the appropriate
DSN. If you can identify it, then do so - otherwise cancel out.

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

 

[Jannah]

I think it worked!! After following your instructions for using the Linked
Table Manager, I successfully entered several records and I seem to be able
to edit existing records. I'll test it again on Monday before I let our
volunteer start doing data entry again, but I think this may actually solve
my problem.

Thanks!!!