ODBC - update failed problem

[fieldling]

I've got a table "Main" running on SQL which is linked to a local table "Bank Details" on an Access front end. They are linked by a field named "Sort Code" which is the primary key in Bank Details.

I can add a new record to bank details but when I enter the sort code in the Main table i get this message:
ODBC - update on a linked table 'Main' failed.

However if I use a sort code that is already stored in the bank details table it works fine. Also if I edit the table "Main" using data from any of the other linked tables this also works fine. Just seems to be the sort code field that causes the problem.
Hope I'm making sense.

Any ideas anyone?
Thanks

 

[Banana]

I'm kind of having trouble following you.

You say you have a table on backend (SQL Server? or something else?) named "Main" and a local table named Bank Details on Access.

Is Main a linked table?

When you say "Main is linked to Bank Details" do you mean you have a auto-join defined? That is, when you select both in a query, they automatically join on Sort Code?

What data type is Sort Code? Is it a IDENTITY field?

 

[fieldling]

Main is a linked table on SQL Server. Bank Details is a local table on access. Yes they do have an auto-join defined. Sort Code is the primary key on Bank Details table. Data type is 'nvachar'.

I put the Bank Details table back on SQL server and this solved the issue but it took over a minute to open the forms based on the tables. This is why I stored it locally.

Any help is much appreciated.

 

[Banana]

Let's see how we can make it a bit quicker.

What is the bank details' table definition, and what indexes does it have currently?

Are you using a continuous subform or datasheet to view the data?

 

[fieldling]

Bank Details table defs:
Sort Code (Primary Key, Indexed No Duplicates, Required) Linked to sort code in main table.
Address 1, Address2, Town, County, Postcode.
All data types are text.

Data is viewed on a single form which shows customer details and bank sort code. When data is entered in the sort code field (from Main table) it populates the bank address details.

As stated before it only happens when I select a sort code that has just been added to the bank details table. Select one that was in the table before this problem started and it works perfectly. Strange.