Strange fault

  • Thread starter Thread starter Atlas
  • Start date Start date
A

Atlas

Windows XP, Access 2003 + SP2, ADO, SQL server 2000

I have a very simple single form

it works perfectly, if the recordsource is:

SELECT * FROM [C - Giornale]


Setting a WHERE clause like

SELECT * FROM [C - Giornale] WHERE ( [data operazione] >=
CONVERT(DATETIME, '01-01-2005 00:00:00', 105) AND [data
operazione] <= CONVERT(DATETIME, '31- 12-2005 00:00:00', 105))

if you:

1) Modify any field
2) scroll to next/prev record
3) scroll back to previously modified record
4) modify again any field

it happens that a dialog pops up "Write Conflict- this record has been
changed by another user since you started editing.......Save record, copy to
clipboard drop changes"

What can I do?
 
If you take a look with the SQL-Server Profiler, you will see that Access is
using the same old values previous to the first update for its optimistic
locking when it try to make the second update.

Creating a ResyncCommand and setting the UniqueTable property correctly
should resolve this problem. Make sure that your resync command is called
by using the SQL-Server Profiler.
 
Sylvain Lafontaine said:
If you take a look with the SQL-Server Profiler, you will see that Access
is using the same old values previous to the first update for its
optimistic locking when it try to make the second update.
Click to expand...
Bad news.... and still they didn't fix it? (gen 2005!)

Creating a ResyncCommand and setting the UniqueTable property correctly
Click to expand...

Hum in Access?
should resolve this problem. Make sure that your resync command is called
by using the SQL-Server Profiler.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Atlas said:
Windows XP, Access 2003 + SP2, ADO, SQL server 2000

I have a very simple single form

it works perfectly, if the recordsource is:

SELECT * FROM [C - Giornale]


Setting a WHERE clause like

SELECT * FROM [C - Giornale] WHERE ( [data operazione] >=
CONVERT(DATETIME, '01-01-2005 00:00:00', 105) AND [data
operazione] <= CONVERT(DATETIME, '31- 12-2005 00:00:00', 105))

if you:

1) Modify any field
2) scroll to next/prev record
3) scroll back to previously modified record
4) modify again any field

it happens that a dialog pops up "Write Conflict- this record has been
changed by another user since you started editing.......Save record, copy
to clipboard drop changes"

What can I do?
Click to expand...
Click to expand...
 
You are in the ADP newsgroup, so my suggestion about the ResyncCommand was
for an ADP form.

If you are using ODBC linked tables or making direct calls to the ADO
objects, then you should tell us so that we'll be on the same line of
thought.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Atlas said:
Sylvain Lafontaine said:
If you take a look with the SQL-Server Profiler, you will see that Access
is using the same old values previous to the first update for its
optimistic locking when it try to make the second update.
Click to expand...
Bad news.... and still they didn't fix it? (gen 2005!)

Creating a ResyncCommand and setting the UniqueTable property correctly
Click to expand...

Hum in Access?
should resolve this problem. Make sure that your resync command is
called by using the SQL-Server Profiler.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Atlas said:
Windows XP, Access 2003 + SP2, ADO, SQL server 2000

I have a very simple single form

it works perfectly, if the recordsource is:

SELECT * FROM [C - Giornale]


Setting a WHERE clause like

SELECT * FROM [C - Giornale] WHERE ( [data operazione] >=
CONVERT(DATETIME, '01-01-2005 00:00:00', 105) AND [data
operazione] <= CONVERT(DATETIME, '31- 12-2005 00:00:00', 105))

if you:

1) Modify any field
2) scroll to next/prev record
3) scroll back to previously modified record
4) modify again any field

it happens that a dialog pops up "Write Conflict- this record has been
changed by another user since you started editing.......Save record,
copy to clipboard drop changes"

What can I do?
Click to expand...
Click to expand...
Click to expand...
 
Sylvain Lafontaine said:
You are in the ADP newsgroup, so my suggestion about the ResyncCommand was
for an ADP form.

If you are using ODBC linked tables or making direct calls to the ADO
objects, then you should tell us so that we'll be on the same line of
thought.
Click to expand...

I'm using .adp and ADO.

Didn't undesrtand if you were talking about vb/ado commands........ but it
looks like you're talking about some table properties.... never heard about
those... I'll investigate a bit
 
Creating a ResyncCommand and setting the UniqueTable property correctly
Ok I couldn't set programmatically the uniquetable property (errors),
nevertheless placing the following command in the form's beforeupdate event
solved the problem.

Me.ResyncCommand = " SELECT * FROM [C - Giornale] WHERE [ID giornale] = "
& Me.ID_Giornale

There's a slight "flash" when the command is executed......but it works! And
it looks like only that record is requeried, not the whole recordset.

One funny beheaviour: I've began setting the me.ResyncCommand in the form's
Load event; obviously it wouldn't work because the field Me.ID_Giornale was
set at load and never changed, hence the resynched query would load always
the first record int the recordset and MAGIC: multiple instance of the first
record would appear!!!!

Strange no?

Another strange thing is that the form did'nt have any multitable or
innerjoined table. The problem arised only when a WHERE clause was
specified.

What is unbeleivable is that since jan 2005 they didn't fix the problem that
occurs only in access 2003.

Did they decide to abandon the pot?
 
Very strange that you couldn't set the UniqueTable property in code, first
time that I see this. In what event did you try to do this?

For your ResyncCommand, you can try something like this:

"SELECT * FROM [C - Giornale] WHERE [ID giornale] = ? "

Access should replace the ? with the value of the primary key for the
current record. However, as you have some trouble with the UniqueTable
property, I'm not sure if this will work. Also, personally, I prefer to use
a stored procedure and set this value for the form with something like:

MyResyncSP ?

For the question about the support from MS; it's look like that MS has
dropped the ball in favor of .NET or for those who insists to use Access, in
favor of ODBC linked tables in a MDF file; so don't expect to see any ADP's
problem solved (and quite probably, this will also be true for the next
version of Office).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Atlas said:
Ok I couldn't set programmatically the uniquetable property (errors),
nevertheless placing the following command in the form's beforeupdate
event solved the problem.

Me.ResyncCommand = " SELECT * FROM [C - Giornale] WHERE [ID giornale] =
" & Me.ID_Giornale

There's a slight "flash" when the command is executed......but it works!
And it looks like only that record is requeried, not the whole recordset.

One funny beheaviour: I've began setting the me.ResyncCommand in the
form's Load event; obviously it wouldn't work because the field
Me.ID_Giornale was set at load and never changed, hence the resynched
query would load always the first record int the recordset and MAGIC:
multiple instance of the first record would appear!!!!

Strange no?

Another strange thing is that the form did'nt have any multitable or
innerjoined table. The problem arised only when a WHERE clause was
specified.

What is unbeleivable is that since jan 2005 they didn't fix the problem
that occurs only in access 2003.

Did they decide to abandon the pot?
Click to expand...
 
For the question about the support from MS; it's look like that MS has
dropped the ball in favor of .NET or for those who insists to use Access,
in favor of ODBC linked tables in a MDF file; so don't expect to see any
ADP's problem solved (and quite probably, this will also be true for the
next version of Office).

--
Click to expand...

Hy there my friend, just came back from winter holiday.

What do you mean in favor of .net? VB + data sources? No more RAD?
 
Well, I cannot enter into a discussion on .NET and if we should call it a
RAD or not (Microsoft still call it a RAD but many others disagrees).

VB6 is no longer sold by MS and VB.NET is so much different from VB that we
cannot call it the next version of VB. Clearly, VB.NET is to much different
from VB6 to be called VB7 but that doesn't mean that it cannot be called a
RAD. You will have to learn and use the software and make a decision by
yourself.

All I can tell you now is that the future of ADP (and DAP) doesn't seem very
good at the moment.
 
Sylvain Lafontaine said:
Well, I cannot enter into a discussion on .NET and if we should call it a
RAD or not (Microsoft still call it a RAD but many others disagrees).

VB6 is no longer sold by MS and VB.NET is so much different from VB that
we cannot call it the next version of VB. Clearly, VB.NET is to much
different from VB6 to be called VB7 but that doesn't mean that it cannot
be called a RAD. You will have to learn and use the software and make a
decision by yourself.

All I can tell you now is that the future of ADP (and DAP) doesn't seem
very good at the moment.
Click to expand...

So it would be wise to use a converter? An .adp to VBsomething?

Are you still developing .adps?
 
Hi, I've written a fairly complicated application in Access 2003. The
database is fairly complicated. I'm always running into difficulties trying
to update anything beyond a couple joined tables, so I thought I'd experiment
with ADO to hopefully get around this difficulty. Yet, even with ADO, I've
run into the same problem. I've seen reference to a UniqueTable property and
ResyncCommand, but no where have I seen clear examples of there use. Could
you please provide these or direct me to where I might find them? Thanks.

Jerry
 
Hi, I've written a fairly complicated application in Access 2003. The
database is fairly complicated. I'm always running into difficulties trying
to update anything beyond a couple joined tables, so I thought I'd experiment
with ADO to hopefully get around this difficulty. Yet, even with ADO, I've
run into the same problem. I've seen reference to a UniqueTable property and
ResyncCommand, but no where have I seen clear examples of there use. Could
you please provide these or direct me to where I might find them? Thanks.

Jerry
 
Back
Top