Re: views not updatable in ACC2k prjct (.adp, SQL Srv 7, MDAC 2.5)
- From: "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
- Date: Mon, 26 May 2008 12:43:06 -0400
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.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Wolfgang Müller" <WolfgangMller@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C8E133E8-6F60-43F7-81E0-4F549B1D5B53@xxxxxxxxxxxxxxxx
Thanks for your fast assistance.
"Sylvain Lafontaine" wrote:
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, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Wolfgang Müller" <WolfgangMller@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:FABC02A5-3BC3-4953-9061-E949EE138A35@xxxxxxxxxxxxxxxx
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
.
- References:
- Re: views not updatable in ACC2k prjct (.adp, SQL Srv 7, MDAC 2.5)
- From: Sylvain Lafontaine
- Re: views not updatable in ACC2k prjct (.adp, SQL Srv 7, MDAC 2.5)
- From: Wolfgang Müller
- Re: views not updatable in ACC2k prjct (.adp, SQL Srv 7, MDAC 2.5)
- Prev by Date: Re: views not updatable in ACC2k prjct (.adp, SQL Srv 7, MDAC 2.5)
- Next by Date: Re: Application.ExportXML
- Previous by thread: Re: views not updatable in ACC2k prjct (.adp, SQL Srv 7, MDAC 2.5)
- Next by thread: melden
- Index(es):
Relevant Pages
|
