We have a SQL 2008 R2 standard edition installed on Server 2008. We use an access front-end which connects to a SQL backend tables which are linked with the app.
Since last 2 weeks, we are continuously getting application freeze with the error - ODBC Error 3157: Update to linked table "###" failed
So far I have done the below:
1. Added primary key to the table as it had an identity column only 2. Relinked all the table and redistributed the front-end to the users 3. Ensure SQL files are shrunk 4. Checked network connections connecting the server to the switch 5. Checked SQL configuration manager to ensure right accounts are being used for the SQL services
This issue is very sporadic and it is now becoming frustrating as it has been ongoing for couple of weeks.
Can anyone shed some light on this as I am running out of possible ideas.
Can you try this: 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.
I have manged to resolve this issue. For all those people who are struggling to get an answer, please ensure that indexing has been properly set-up on resource hungry big SQL tables.
You can use SQL monitoring tools to analyse and find the long-running queries and then use SSMS (sql server management studio) feature called "Execution Plan" to create indexing. SSMS feature literally tells you what script to run if it finds that indexing of tableis required.
Be careful that you don't simply apply each index that is presented by SSMS. Usually, we look at the current set of indexes and compare it to the suggested index. Often there is a good deal of similarity and you can combine the two together.
=============================================================================== There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)