|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
In an SQL server 2000 I have a table with 99 fields, 3 of which are of
datatype Bit. In an Access 2003 database (mdb) I make a link to this table.
In the linked table everything is normal, I can edit the fields
successfully. Now in SQL-Server I add another field of type Bit. Then I
delete the link in Access and link the table once again. Now I can't edit
the fields in the linked table anymore (I mean, not just the new field, but
all fields!). If I write something in a field and then, in the moment I take
the cursor away from that field, the following message appears: "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. (...)" If,
instead of a field of type Bit, I added a field of type smallint, then
everything is OK. This whole thing does make no sense at all to me.
According to "Maximum Capacity Specifications" in the Online Books I do not
seem to violate any limit whatsoever (I calculated the sum of all "length"
values of the fields, that gave 1687 Byte, so this can't be a problem,
because the "Bytes per row" is allowed to be up to 8060). Do anybody have an
idea what's wrong here?
|
|
hi Lorenz,
Lorenz Ingold wrote:
[Quoted Text] > In an SQL server 2000 I have a table with 99 fields, 3 of which are of
> datatype Bit. In an Access 2003 database (mdb) I make a link to this table.
> In the linked table everything is normal, I can edit the fields
> successfully. Now in SQL-Server I add another field of type Bit. Then I
> delete the link in Access and link the table once again. Now I can't edit
> the fields in the linked table anymore (I mean, not just the new field, but
> all fields!). If I write something in a field and then, in the moment I take
> the cursor away from that field, the following message appears: "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. (...)" If,
> instead of a field of type Bit, I added a field of type smallint, then
> everything is OK. This whole thing does make no sense at all to me.
> According to "Maximum Capacity Specifications" in the Online Books I do not
> seem to violate any limit whatsoever (I calculated the sum of all "length"
> values of the fields, that gave 1687 Byte, so this can't be a problem,
> because the "Bytes per row" is allowed to be up to 8060). Do anybody have an
> idea what's wrong here?
Nope. Just ensure your table has a primary key using precise values (no
floats) and your table as a field of typ TIMESTAMP.
mfG
--> stefan <--
|
|
Looks like optimistic locking is defeated. Basically it works by selecting
the updated row using original values in the where clause. If the row is not
found this is someone else changed something (if not found for some other
reason this will be thought to be a concurrency problem).
My approach would be to use the SQL Server Profiler to see the update
statement sent to the DB and to cut/pase in QA to see why it doesn't match.
AFAIK adding a timestamp field (automatically updated whenever the row
change) allows the SQL statement to use only this field instead of using all
non blob fields in the where clause to check optimistic concurrency.
Hope this helps.
--
Patrice
"Lorenz Ingold" <l.ingold[ at ]comteach.ch> a écrit dans le message de news:
eck4cv$2tar$4[ at ]news.imp.ch...
[Quoted Text] > In an SQL server 2000 I have a table with 99 fields, 3 of which are of
> datatype Bit. In an Access 2003 database (mdb) I make a link to this
> table.
> In the linked table everything is normal, I can edit the fields
> successfully. Now in SQL-Server I add another field of type Bit. Then I
> delete the link in Access and link the table once again. Now I can't edit
> the fields in the linked table anymore (I mean, not just the new field,
> but
> all fields!). If I write something in a field and then, in the moment I
> take
> the cursor away from that field, the following message appears: "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. (...)" If,
> instead of a field of type Bit, I added a field of type smallint, then
> everything is OK. This whole thing does make no sense at all to me.
> According to "Maximum Capacity Specifications" in the Online Books I do
> not
> seem to violate any limit whatsoever (I calculated the sum of all "length"
> values of the fields, that gave 1687 Byte, so this can't be a problem,
> because the "Bytes per row" is allowed to be up to 8060). Do anybody have
> an
> idea what's wrong here?
>
>
|
|
You must set the new bit field to have a constraint NOT NULL.
As you have seen, Access will not work with SQL Server bit
fields with default Null values, and in fact, Access will not work
with Null values in bit fields at all.
(david)
"Lorenz Ingold" <l.ingold[ at ]comteach.ch> wrote in message
news:eck4cv$2tar$4[ at ]news.imp.ch...
[Quoted Text] > In an SQL server 2000 I have a table with 99 fields, 3 of which are of
> datatype Bit. In an Access 2003 database (mdb) I make a link to this
table.
> In the linked table everything is normal, I can edit the fields
> successfully. Now in SQL-Server I add another field of type Bit. Then I
> delete the link in Access and link the table once again. Now I can't edit
> the fields in the linked table anymore (I mean, not just the new field,
but
> all fields!). If I write something in a field and then, in the moment I
take
> the cursor away from that field, the following message appears: "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. (...)" If,
> instead of a field of type Bit, I added a field of type smallint, then
> everything is OK. This whole thing does make no sense at all to me.
> According to "Maximum Capacity Specifications" in the Online Books I do
not
> seem to violate any limit whatsoever (I calculated the sum of all "length"
> values of the fields, that gave 1687 Byte, so this can't be a problem,
> because the "Bytes per row" is allowed to be up to 8060). Do anybody have
an
> idea what's wrong here?
>
>
|
|
|