Not every data are treated equally

H

homer

Hi, everybody!
I need your help on this one.
Even they are from the same table, these data seem divide up into two camps.
The newer records are not updatable while the older ones are updatable from
MS Access linked table. Out of total of 4200 rows, 104 are un-updatable, and
their Primary Keys tell me they are the newer ones.

The message said “Write Conflict: This record has been changed by another
user since you started editing it…†with options to either Copy to Clipboard
or Drop Changes.

I don’t think that message is accurate, because I have tried many times
during different hours.
I have no problem running an update from SQL Server Query Analyzer to get
the data in.
I have re-linked the table.

Thanks in advance!

By the way here is the Data definition script for the table. It was promoted
from excel by "Power user". I am sure there is room for improvement on data
type and field naming.

Oh, the fields they want to update are [Cnt UM] and [Conv Factr].

CREATE TABLE [dbo].[Sts_ConversionFactors] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Not Active] [bit] NULL ,
[ITEM] [nvarchar] (15) COLLATE Latin1_General_BIN NOT NULL ,
[ITEM_DESC] [nvarchar] (35) COLLATE Latin1_General_BIN NULL ,
[UM] [nvarchar] (2) COLLATE Latin1_General_BIN NULL ,
[Cnt UM] [nvarchar] (50) COLLATE Latin1_General_BIN NULL ,
[Conv Fctr] [float] NULL ,
[AREA] [nvarchar] (50) COLLATE Latin1_General_BIN NULL ,
[StandardSize] [bit] NULL ,
[Category] [nvarchar] (50) COLLATE Latin1_General_BIN NULL ,
[Catalog] [bit] NULL ,
[Cost Ref Item] [bit] NULL ,
[MSI/EACH Conv] [float] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sts_ConversionFactors] ADD
CONSTRAINT [DF_Sts_ConversionFactors_Conv Fctr] DEFAULT (0) FOR [Conv Fctr],
CONSTRAINT [PK_Sts_ConversionFactors] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
 
S

Sharkbyte

Homer:

Are you getting this error - “Write Conflict: This record has been changed
by another user since you started editing it…†- from a form or when trying
to affect the data directly to the table? I'm going to assume a form.

Check the actions you are performing, on the form. You can narrow down the
offending action by commenting things out.

When this has occurred to me, I find that I am attempting to perform an
unnecessary action, after an action query.

A good example, from recently, is that I had wrote in a Requery statement,
against a linked subform. I would add a new record, and the subform would
display it automatically. The Requery was unnecessary, and causing Access to
get confused.

Not sure if this is it, but it is one place to look.

Good luck.

Sharkbyte
 
R

Roger Carlson

H

homer

I have added default =0 in my sql table and relinked it from access, but it
is still not updatable. By the way, before the change, I did not find any
null on those bit fields. and I will search again alone the same line.
But what else should I look out for?

Roger Carlson said:
This can be caused by a couple of problems, most commonly, using a BIT field
as a Yes/No field without a default value (that is, new records have a
null). See the following MS Knowledgebase article:
http://support.microsoft.com/kb/280730/EN-US/

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



homer said:
Hi, everybody!
I need your help on this one.
Even they are from the same table, these data seem divide up into two camps.
The newer records are not updatable while the older ones are updatable from
MS Access linked table. Out of total of 4200 rows, 104 are un-updatable, and
their Primary Keys tell me they are the newer ones.

The message said "Write Conflict: This record has been changed by another
user since you started editing it." with options to either Copy to Clipboard
or Drop Changes.

I don't think that message is accurate, because I have tried many times
during different hours.
I have no problem running an update from SQL Server Query Analyzer to get
the data in.
I have re-linked the table.

Thanks in advance!

By the way here is the Data definition script for the table. It was promoted
from excel by "Power user". I am sure there is room for improvement on data
type and field naming.

Oh, the fields they want to update are [Cnt UM] and [Conv Factr].

CREATE TABLE [dbo].[Sts_ConversionFactors] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Not Active] [bit] NULL ,
[ITEM] [nvarchar] (15) COLLATE Latin1_General_BIN NOT NULL ,
[ITEM_DESC] [nvarchar] (35) COLLATE Latin1_General_BIN NULL ,
[UM] [nvarchar] (2) COLLATE Latin1_General_BIN NULL ,
[Cnt UM] [nvarchar] (50) COLLATE Latin1_General_BIN NULL ,
[Conv Fctr] [float] NULL ,
[AREA] [nvarchar] (50) COLLATE Latin1_General_BIN NULL ,
[StandardSize] [bit] NULL ,
[Category] [nvarchar] (50) COLLATE Latin1_General_BIN NULL ,
[Catalog] [bit] NULL ,
[Cost Ref Item] [bit] NULL ,
[MSI/EACH Conv] [float] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sts_ConversionFactors] ADD
CONSTRAINT [DF_Sts_ConversionFactors_Conv Fctr] DEFAULT (0) FOR [Conv Fctr],
CONSTRAINT [PK_Sts_ConversionFactors] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
Click to expand...
Click to expand...
 
A

aaron.kempf

roger

that is a fabulous bug..

why can't those retards from redmond just fix bugs and stop making
excuses??

seriously redmond..

instead of PUBLISHING AN ARTICLE ABOUT A BUG... FIX IT!!!!
 
R

Roger Carlson

Go back and re-read the article. The problem has other causes and
solutions. Try those as well.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


homer said:
I have added default =0 in my sql table and relinked it from access, but it
is still not updatable. By the way, before the change, I did not find any
null on those bit fields. and I will search again alone the same line.
But what else should I look out for?

Roger Carlson said:
This can be caused by a couple of problems, most commonly, using a BIT field
as a Yes/No field without a default value (that is, new records have a
null). See the following MS Knowledgebase article:
http://support.microsoft.com/kb/280730/EN-US/

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



homer said:
Hi, everybody!
I need your help on this one.
Even they are from the same table, these data seem divide up into two camps.
The newer records are not updatable while the older ones are updatable from
MS Access linked table. Out of total of 4200 rows, 104 are
Click to expand...
un-updatable,
and
their Primary Keys tell me they are the newer ones.

The message said "Write Conflict: This record has been changed by another
user since you started editing it." with options to either Copy to Clipboard
or Drop Changes.

I don't think that message is accurate, because I have tried many times
during different hours.
I have no problem running an update from SQL Server Query Analyzer to get
the data in.
I have re-linked the table.

Thanks in advance!

By the way here is the Data definition script for the table. It was promoted
from excel by "Power user". I am sure there is room for improvement on data
type and field naming.

Oh, the fields they want to update are [Cnt UM] and [Conv Factr].

CREATE TABLE [dbo].[Sts_ConversionFactors] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Not Active] [bit] NULL ,
[ITEM] [nvarchar] (15) COLLATE Latin1_General_BIN NOT NULL ,
[ITEM_DESC] [nvarchar] (35) COLLATE Latin1_General_BIN NULL ,
[UM] [nvarchar] (2) COLLATE Latin1_General_BIN NULL ,
[Cnt UM] [nvarchar] (50) COLLATE Latin1_General_BIN NULL ,
[Conv Fctr] [float] NULL ,
[AREA] [nvarchar] (50) COLLATE Latin1_General_BIN NULL ,
[StandardSize] [bit] NULL ,
[Category] [nvarchar] (50) COLLATE Latin1_General_BIN NULL ,
[Catalog] [bit] NULL ,
[Cost Ref Item] [bit] NULL ,
[MSI/EACH Conv] [float] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sts_ConversionFactors] ADD
CONSTRAINT [DF_Sts_ConversionFactors_Conv Fctr] DEFAULT (0) FOR [Conv Fctr],
CONSTRAINT [PK_Sts_ConversionFactors] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
Click to expand...
Click to expand...
Click to expand...
 
A

aaron.kempf

no roger

you see.. when microsoft DIAGNOSES a bug.. instead of FIXING IT.. they
publish a friggin KB article

i just swear that company needs to GROW UP someday.

instead of telling US how to FIX OUR DATABASE

they need to tell their goddamn programmers HOW TO FIX THEIR DATABASE
SERVER

no amount of rationalization is going to make this go away.

i dont care if there are 4 different CAUSES for this BUG.

fix it redmond or eat shit redmond

they choose to eat shit
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MS-SQL AND ACCESS LINKED TABLE PROBLEM (#ERROR FIELDS) (cross) 1
Updatable SQL 2000 partitioned view not updatable in Access 3
Query not Updateable 1
Subform The value you entered isn't valid for this field 0
Problem accessing property or method of OLE object / Sort Order 0
Subform Foreign key not working 0
Stored Procedure Error Question 3
Forward Engineer Visio ER Diagram to SQL DDL 2

Top