Solved

Write conflict when editing a linked table - linked to SQL Server 2000

Posted on 2008-10-09
7
821 Views
Last Modified: 2013-11-29
I have an Access database where the tables are linked to a SQL Server 2000 database. In one of the tables if I add or modify a record within Access (either via the table or a form view) I get a Write Conflict message (see attached). The record can be modified/added through SQL with no problem. I know that this error is because the SQL table has some fields of data type 'bit', but I don't know why this should be a problem or how to fix it, (other than to take the 'bit' fields out).

accesserror.jpg
0
Comment
Question by:Robijef
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 85
ID: 22676832
Why would you think this has anything to do with a Bit datatype field? Write conflicts can occur for several reasons and are not dependent on any particular datatype. While you may be having an issue with a Bit field, I have several SQL Server tables with Bit fields and can update them.

Did you recently upsize this database to SQL Server? If so, then Access uses the Bit datatype for the YesNo field ... in most cases, however, this translates over perfectly well.

The error you're getting typically means that you or another user has the same table/record open and have 'dirtied' the same record. This can occur when if you have a Form opened to that record and have made ANY change to the data - either through the interface or through code - and you then open the Table that form is built from and try to modify the same record.

This can also occur at the form level if you use VBA/SQL to try and update the same record as you're viewing on the form.
0
 

Author Comment

by:Robijef
ID: 22676949
Hi,
Well, because if I delete the bit fields then I can update no problem.
I didn't upsize, I created the The SQL tables independantly and linked them in Access, and there is definitely no other user using this database.
I have had the same issue on other databases linked to SQL.
Regards, Jeff
0
 
LVL 85
ID: 22680996
Then I'd presume that your Access application isn't properly setup. As I mention, I have several Access Frontends to SQL Server backends via linked tables, and I use the Bit datatype with no problems.

Any Triggers on the tables? Constraints?

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Robijef
ID: 22684828
Yes, I also have other applications where this works OK - but they would be Access 2000 rather than 2003.
I'm sure the problem is Access/SQL setup also, but exactly what - I don't know. There are no triggers or constraints on the SQL tables.
Also, the problem isn't constrained to just my PC which suggests its not an Office setup issue so re-installing office wouldn't make any difference.
Very weird I know, but when did I ever get a straightforward problem!!
0
 
LVL 5

Accepted Solution

by:
perezjos earned 125 total points
ID: 22837533
Hi
I had similar problem and I found my answer here http://support.microsoft.com/kb/280730/
After adding the default values to 0 on the bit fields (SQL table) all was fine !

Enjoy

Jose
 

0
 

Author Comment

by:Robijef
ID: 22865064
Worked fine
0
 

Expert Comment

by:animallover
ID: 37443745
Thank you, thank you!  I searched all over for a solution and once again was able to find it here.  I had 4 bit fields and once I set them all to default of 0 i was able to edit new records in my table.  I did have to delete and relink my table through ODBC in Access to make it possible.

You saved my day!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question