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
426 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
  • 4
  • 3
9 Comments
 
LVL 17

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 17

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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 17

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 17

Accepted Solution

by:
Karen Falandays earned 250 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now