Odbc Connection On Linked Table Failed

[stevo1961]

We have an Access 2003 front end ODBC linked to a SQL Server 2005 backend
In one of the 4 tables (1:Many relationship)we are getting this error when a user tries to add or change a record

ODBC -- update on a linked table 'Delivery Dockets' failed
or
OBBC -- insert on a linked table 'Delivery Dockets' failed

We have this set-up for several years and it's just now acting up.
Data entry via a Tab form: Main form Jobs with 3 tab forms with subforms for Delivery Docket, Job Sales Charges, Job Charges table
Only issue is the getting or changing data on the Delivery Docket table on the BE.
The other 3 tables have no issues

Table on BE setup on SQL Server:

Jobs 103469 Records
Delivery Docket 279895 Records
Job Sales Charges 176956 Records
Job Charges 100150 Records

Any assistance would be greatly appreciated

 

[lameid]

The very first thing I would do is backup the frontend...

Next I would delete the existing ODBC linked table and recreate the link.

Access Might ask you for a combination of fields (SQL calls them columns) that make the record unique if it can't find something to uniquely identify the record. - My hunch is that this was done in the first place and somehow something has changed either on the server or your front end and it is confused.

Anything beyond that and you practically need somebody sitting there looking at it.

 

[genomon]

Could be the table lost its primary key somehow?

Beir bua agus beannacht!

 

[dhookom]

Do you have a bit field in the Delivery Docket table? If so, does it have a default value?

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

So two thoughts for uniqueness and one for the boolean vs. Tri-state variable. Both excellent thoughts... Did it help?

 

[stevo1961]

Hi All,

We tried creating a new ODBC linked to the table and recreated the link but to no avail. checked Primary key and it was still intact, no bit data type in any of the fields.

Unfortunately, we decided to create a new table and transferred the data across as the issue was not going away, so far it's working.

Thank you all for your input, much appreciated,