ACCESS的是/否转到SQL SERVER 2000的问题

lbs263

在SQL SERVER2000上有一表,建一字段,如是否离职 bit 1
在ACCESS中采用链接表的方式,打开表,修改该字段的值,则报写入冲突.事实上没有第二个人在用这条记录,不知大家有没有遇到过这个问题.

HG

沒有遇到，您可以在bit字段上默認一個值。

lbs263

这个问题是这样的，在一个已有数据的表上，由于业务需要，后来增加一个新bit型字段，
由于SQL SERVER 的缺省值在新增记录时才有效，所以发生这个问题。解决方案是：
ACC2000: Write Conflict Error When You Try to Update Records in a Linked SQL Server Table
The information in this article applies to:
Microsoft Access 2000

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 97 version of this article, see Q278696.

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 (zero) on all bit fields.

NOTE: With this option, you must update records that were 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.
Use a Microsoft Access project (*.adp) instead of an Access database (*.mdb).
NOTE: If you make changes to the data types in the SQL Server tables, relink the tables in Microsoft Access.
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 2000 database.
In Microsoft SQL Server 7.0 or later, add a new table to the Pubs sample database that has 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 field:
   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 2000 database, and then link the Table1 table 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.
First Published: Nov 28 2000 8:50AM
Keywords: kberrmsg kbdta kbprb prb