views not updatable in ACC2k prjct (.adp, SQL Srv 7, MDAC 2.5)

[Wolfgang Müller]

Hi,

some years ago I created a client server solution with an SQL server 7 as
backend and Access 2000 project (.adp/.ade) as frontend. The client platforms
were NT 4.0 WS or W2k (always with MDAC 2.5). Everything worked "fine" for
many years until I caught someday upgrades to MDAC 2.8 with a third software
here and there.
Views, which were conceptually always capable for editing, became sudden
read only if they were based on more than 2 tables.
I went around the problem by turning back the MDAC update (on NT 4.0 WS, on
W2k I had no success therewith).
Now however the migration of the client systems is to Vista forthcoming and
there a 2.8 compatible MDAC version 6.somewhat comes along and there's no way
to work around it.
After deeper penetration into the stuff it turned out that the problem is
due to the timestamp fields contained in the tables. Dependently of the
position of the table in the join chain it succeeds to restore the
updatability of the View after removing the timestamp field of only one
table. Investigations about the sense of the timestamp fields, which were a
result of the upsize operation from the earlier ACCESS 2.0 version of the
database, resulted in that they can serve the fast and safe recognition of
competitive changes of rows and that their absence in tables with floating
decimal point fields causes problems because of the differing internal
representation of such data types in ACCESS and SQL. In such cases ACCESS
then always diagnoses competitive network access, even if nobody else changed
in the data record. Therefore I would remove timestamp fields reluctantly
from there, because of the extent of that Application let badly to divine,
which problems arise for me from it.

In the meantime the problem lies already nearly a three-quarter year,
without any succeess in receiving usefull information despite most intensive
search in the relevant forums.

Thus I tried also the porting of the Acc2k-project to Acc2k7 and the SQL
server 7 db to 2005 express. That was unproblematic and turned out well, the
problem exists however in unchanged form away (both under XP SP2 and under
Vista).

Does someone have experience with such problems or otherwise helpful
information to the topic?

Wolfgang Mueller

 

[Sylvain Lafontaine]

Well, the first thing would be to try replacing the Views with plain Select
statements, UDF or SP.

In your case - and beside your problem with the floating point format
fields, optimistic locking and the timestamps - your main problem seems to
be the multi-step feature of ADO; so if I were you, I would try deactivating
it by specifying the UniqueTable and the ResyncCommand properties. However,
it's not all forms based on multi-tables recordsets that can be used without
the multi-step feature of ADO. When you design your forms from the
beginning to go this way, there is no problem but obviously, this might not
be your situation. (Of course, I'm talking here about views who are used as
recordsource for a form and not about views that you would directly open
from the database windows.).

Finally, you don't say what exact type you're describing with « floating
decimal point fields ». Last time I've checked, I didn't see any problem
using floating point (8 bytes), real (4 bytes) or decimal point fields
between ADP and SQL-Server. However, there are many combinations of decimal
points available; so maybe you're using an invalid combination here.

 

[Wolfgang Müller]

Thanks for your fast assistance.

Well, the first thing would be to try replacing the Views with plain Select
statements, UDF or SP.

I'm already using plain select statements and SP, however with the same
effect.

In your case - and beside your problem with the floating point format
fields, optimistic locking and the timestamps - your main problem seems to
be the multi-step feature of ADO; so if I were you, I would try deactivating
it by specifying the UniqueTable and the ResyncCommand properties. However,
it's not all forms based on multi-tables recordsets that can be used without
the multi-step feature of ADO. When you design your forms from the
beginning to go this way, there is no problem but obviously, this might not
be your situation. (Of course, I'm talking here about views who are used as
recordsource for a form and not about views that you would directly open
from the database windows.).

The UniqueTable and the ResyncCommand properties are used in the Form from
the beginning.

Finally, you don't say what exact type you're describing with « floating
decimal point fields ». Last time I've checked, I didn't see any problem
using floating point (8 bytes), real (4 bytes) or decimal point fields
between ADP and SQL-Server. However, there are many combinations of decimal
points available; so maybe you're using an invalid combination here.

Quotation Microsoft (http://msdn.microsoft.com/en-us/library/bb188204.aspx):
"Probably the leading cause of updatability problems in Office Access–linked
tables is that Office Access is unable to verify whether data on the server
matches what was last retrieved by the dynaset being updated. If Office
Access cannot perform this verification, it assumes that the server row has
been modified or deleted by another user and it aborts the update.

There are several types of data that Office Access is unable to check
reliably for matching values. These include large object types, such as text,
ntext, image, and the varchar(max), nvarchar(max), and varbinary(max) types
introduced in SQL Server 2005. In addition, floating-point numeric types,
such as real and float, are subject to rounding issues that can make
comparisons imprecise, resulting in cancelled updates when the values haven't
really changed. Office Access also has trouble updating tables containing bit
columns that do not have a default value and that contain null values.

A quick and easy way to remedy these problems is to add a timestamp column
to the table on SQL Server."

However thats not the basic problem, but I found out that even views are
based on more than two tables become capable for editing again, if I remove a
timestamp field from one of the tables.
Unfortunately this way is not practicable ...

Any other idea?

Wolfgang

 

[Sylvain Lafontaine]

If you have a problem with the optimistic updating of a floating point
numeric type due to a rounding issue, then you should tell us the exact
numeric type that you are using along with a numerical example that gives
you trouble. Use the SQL-Server Profiler is necessary to know the exact
value with the full length of the decimal portion.

If you have a problem with the update of two related tables using timestamp
values, my best guest would be that the problematic table get updated twice
or that there is a trigger on the sql-server side who is also updating the
table. You should already knows it if you are using a trigger and using the
SQL-Server Profiler will tell you exactly if you are hitten by the other
case. Having more than a single primary key/foreign key relationship
between two tables is an easy way to be hitten by the first case. Making a
reference to a foreign table in both the main form and in a subform could be
another way; if I remember correctly. A look at the design of your form and
with the SQL-Server Profiler should tell you what's going on here.

 

[Wolfgang Müller]

Hello Sylvain,

I guess we talk at cross purposes. Therefore I created a sample DB (whitout
any data, 3 tables, the necessary relations and a view) wich you can use to
reproduce the relevant behavior.

To do this please create first a DB named "Test" (I use SQL Server 2005
Express, leave all settings at their default).
Use the following script to create all the objects:

USE [Test]
GO
/* Objekt: Table [dbo].[Title] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Title](
[TitleNr] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[assigned] [float] NULL,
[ts] [timestamp] NULL,
CONSTRAINT [PK_Title] PRIMARY KEY CLUSTERED
(
[TitleNr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/* Objekt: Table [dbo].[SubTitle] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SubTitle](
[SubTitleNr] [int] IDENTITY(1,1) NOT NULL,
[TitleNr] [int] NULL,
[SubTitle] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[assigned] [float] NULL,
[ts] [timestamp] NULL,
CONSTRAINT [PK_SubTitle] PRIMARY KEY CLUSTERED
(
[SubTitleNr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/* Objekt: Table [dbo].[Position] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Position](
[PositionNr] [int] IDENTITY(1,1) NOT NULL,
[SubTitleNr] [int] NULL,
[Position] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[assigned] [float] NULL,
[ts] [timestamp] NULL,
CONSTRAINT [PK_Position] PRIMARY KEY CLUSTERED
(
[PositionNr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/* Objekt: View [dbo].[Orders] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Orders]
AS
SELECT dbo.Position.PositionNr, dbo.Position.SubTitleNr,
dbo.Position.Position, dbo.Position.assigned, dbo.SubTitle.SubTitle,
dbo.SubTitle.assigned AS STassigned,
dbo.Title.Title, dbo.Title.assigned AS Tassigned, dbo.Position.ts
FROM dbo.Position INNER JOIN
dbo.SubTitle ON dbo.Position.SubTitleNr =
dbo.SubTitle.SubTitleNr INNER JOIN
dbo.Title ON dbo.SubTitle.TitleNr = dbo.Title.TitleNr
GO
/* Objekt: ForeignKey [FK_Position_SubTitle] */
ALTER TABLE [dbo].[Position] WITH CHECK ADD CONSTRAINT
[FK_Position_SubTitle] FOREIGN KEY([SubTitleNr])
REFERENCES [dbo].[SubTitle] ([SubTitleNr])
GO
ALTER TABLE [dbo].[Position] CHECK CONSTRAINT [FK_Position_SubTitle]
GO
/* Objekt: ForeignKey [FK_SubTitle_Title] */
ALTER TABLE [dbo].[SubTitle] WITH CHECK ADD CONSTRAINT [FK_SubTitle_Title]
FOREIGN KEY([TitleNr])
REFERENCES [dbo].[Title] ([TitleNr])
GO
ALTER TABLE [dbo].[SubTitle] CHECK CONSTRAINT [FK_SubTitle_Title]
GO

Then create a new Access-project (I use 2007) and use the "Test" DB for it.
Now open the view named "Orders". Are you able to add a new Record now?
(Same effect if you use a form using the UniqueTable and the ResyncCommand
properties).
Otherwise if you delete the field "ts" of the "SubTitle" table - voilà
magically it function! Why?
And why does it work even with the "ts" field in "SubTitle" if I use an OS
with MDAC 2.5 installed instead of any higher Version?

:-? Wolfgang