[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Strange Problem: Cannot update SQL Server text-fields for some records, but all other fields in a record in a linked table.

Posted on 2003-10-23
9
Medium Priority
?
441 Views
Last Modified: 2008-02-01
Hello Experts,

i have a very strange problem:

I have a table linked to a SQL Server 7.0 table in my MS Access 97 Application containing 5 text-fields (SQL Server Type, in Access seen as MEMO-fields) and several other fields (in total about 140 fields). Updating any field works fine except for some records. In those records I can only edit the non-text-fields in my application. When I try to edit one of the text-fields I get a error message telling me that another user has edited the record and i have to discard my changes. Deleteing characters from the text-fields works fine.

The strange thing is that this problem only crops up for some records. (1 in a 100 or so) All other records work fine.

Does anyone know what happens here? And how this can be fixed?

Lars
0
Comment
Question by:lschweer
[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
  • 4
  • 3
9 Comments
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 9606229
Hi Lars, I had the same problem, and it had to do with a data type. Below is the text of a knowledge base article that helped me. I hope this helps
Karen
ACC97: Write Conflict Error When You Try to Update Records in a Linked SQL Server Table
SYMPTOMS
You receive the following write conflict error when you try to update records in a linked SQL Server table:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.
You are then given the following options: Save Record, Copy to Clipboard, or Drop Changes.
CAUSE
Access is creating Null bit fields, which causes a type mismatch.
RESOLUTION
To resolve this problem, do one of the following:
Using SQL Server, open the table in Design view, and assign a default value of 0 (that is, zero) on all bit fields.

NOTE: With this option, you must update records entered before this change was made. See the next item for more information.


Using SQL Server, run an Update Transact-SQL statement against the table, and set all bit fields that have a value of Null to a new value of 0 or 1.
Using SQL Server, change the bit fields to some other data type, such as int or tinyint.
Using SQL Server, add a timestamp field to the table.
MORE INFORMATION
Steps to Reproduce Behavior
The following steps assume that you have an understanding of how to create tables in SQL Server, and that you are familiar with certain SQL Server tools such as Enterprise Manager.

Also assumed is that you are aware of how to create File, User, and System Data Source Names (DSN), and how to use a DSN to link a table to a Microsoft Access 97 database.
In Microsoft SQL Server 7.0 or later, add a new table to the Pubs sample database, with the following table structure:

Column Name Data Type Length Allow Nulls
fldID int 4 <unchecked>
fldBit bit 1 <checked>
fldDescrip varchar 50 <checked>

Make the following Column property assignments to the fldID:   Identity: Yes
   Identity Seed: 1
   Identity Increment: 1
                              
Set the fldID field as the primary key, and then close and save the table as Table1.
Insert the following records into the Table1 table:

fldID fldBit fldDescrip
1 1 Record #1
2 <NULL> Record #2
3 0 Record #3
4 1 Record #4

Close the table, and then create a User DSN that points to your SQL Server.
Open any Access 97 database, and then link Table1 from the Pubs database into your database.
Change the description of Record #1 to Record #0. Note that you can save your change.
Try to change the description of Record #2 to Record #1. Note that you receive an error when you try to save your change.
The information in this article applies to:
Microsoft Access 97
Last Reviewed: 10/20/2003 (3.0)  
Keywords: kbprb KB278696

 
 
 
 
 Contact Us  


© 2003 Microsoft Corporation. All rights reserved.  Terms of use  Security & Privacy  Accessibility  
0
 
LVL 2

Author Comment

by:lschweer
ID: 9607045
Hi kfalandays,

sorry the problem is not caused by bit-fields, but by text-fields. The problem is that I cannot edit the text-fields (in Access they appear as Memo-Fields) within some records. On other records this works fine. When I change any field in a "problem record", except the text-fields, I can update the record. But when I try to change a text-field I receive this error message:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.

I can delete characters from the text-fields, but I cannot add any characters.

So does anyone have any other suggestions?

Lars

 
0
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 9607110
Lars, I couldn't edit text fields either, when I had this problem. You may want to double check the field types, and refer to the section in the article called Steps to Reproduce Behavior. It's worth a try
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 2

Author Comment

by:lschweer
ID: 9607199
kfalandays could you edit any other field in that record? I knew the bit field problem, but when I had that problem I couldn't edit ANY field (eg. float, integer, ...) in that record. But in this case I can edit all other fields (and update the database) without any problem.

I double checked the assigned bit-field values in the "problem records". They don't have any NULLs.
0
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 9607448
Yes, Lars, the problem was sporadic, that is why it was such a bugger. Sometimes we could edit, sometimes we couldnt'. We could edit in the table itself, but not in forms that were referring to linked values! Some users could edit, some could not!

Once my DBA (who couldn't figure out the probelm to begin with) cleared and removed all null values, the problem went away!
0
 
LVL 18

Accepted Solution

by:
Karen Falandays earned 1000 total points
ID: 9610436
Hi Lars, just curious to know if this worked for you! Were you able to remove all nulls?
Karen
0
 
LVL 2

Author Comment

by:lschweer
ID: 9610907
Hi Karen,

haven't tested it again. I'll do this tomorrow and give you a feedback.

Lars
0
 
LVL 39

Expert Comment

by:stevbe
ID: 9990973
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: kfalandays {http:#9606229}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

stevbe
EE Cleanup Volunteer
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

656 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