Unable to update data in a linked table 2

[GreenFella]

Here is a strange one.

I have an Access 2000 database that is linked to tables in a SQL Server backend via ODBC connections.

There are about 35 links to SQL Server tables. One of these tables is a Customer table.

I can add new records to the Customer table but if I try to update any fields on the table I get a Write Conflict error. You know the one that says "This record has been changed by another user since you started editing it...".

Here's my dilema. I am the only one using my copy of the front end. Multiple people have the same front end on their desktops accessing the same SQL Server backend.

I can add records to the Customer table. I just can't turn around and update any field on any of the records.

The kicker is that, if I go into Enterprise Manager and go to the Customer table directly I can edit and update fields without any problems.

The problem in Access only appears to be affecting the one table. I am able to update information in other tables without any problems.

I have dropped the link to the Customer table and recreated it again with no success.

Any help that can be provided would be greatly appreciated.

Thanks
Greenfella

 

[Trevil]

Two things:
1. If you open Access and double click on the linked Customer table, I assume you can make changes to fields. If yes, then...
2. I'm guessing you may have some code behind a form that will change some data fields in the Customer table? If so, that's the place to look.

35+ years of 'progress' -- can't we all just go wire boards again?

 

[GreenFella]

Thanks for your response.

Acutally, I was just double clicking on the link for the Customer table. That what was so weird about the problem.

Guess what though... a co-worker discovered what the problem was.

In the Customer table there is a bit field. The linked table in Access shows this as a type yes/no. The problem occured because the bit field had been set up as being nullable.

While all of the customer records had the bit apparently turned off a quick setting of the bit to on and then to be back off again fixed the problem.

Why a bit field, incorrectly configured would allow you to add a record to the customer table but not update the record again is beyound me.

But that's what the issue turned out to be.

Another one to write down in the little black book of unfathomable Access errors.

Greenfella

 

[Trevil]

Glad you got it resolved. I gave up on keeping my little black books up to date -- couldn't afford the storage cost!

35+ years of 'progress' -- can't we all just go wire boards again?

 

[addy]

Thanks greenfella - just managed to fix a problem I've been having.

Have a star!

 

[GreenFella]

Thanks for the star!

Glad you were able to use the solution. That's why I figured I would detail it rather than just say I got it resolved.