Resync problem

  • Thread starter Thread starter Helmut Herrmann
  • Start date Start date
H

Helmut Herrmann

After upgrading from Access 2003/SQL Server 2000 to Access 2007/SQL Server
2005 the following problem occurred:

In some forms with subforms the data source of the main form is a view and
the data source of the subform is a table with a field containing the ID of
the record in the main form. When I insert a new record in the subform this
works well. But when I want to edit this new record, I get the error message
that another user has changed the record which is definitely not true. It is
not possible to save the edited record. I have to leave the record in the
main form and reenter it in order to be able to edit the new record in the
subform.

I used SQL profiler to see what is going on. Regarding insertion of the new
record in the subfrom the profiler recorded the following:
---------
exec sp_executesql N'INSERT INTO "zentraleSQL"."dbo"."t_RECHNUNGS_POS"
("ReID","Pos","Anzahl","Bezeichnung","Einzelpreis","Einheit","Rabatt","MwStSatz","EinzelpreisEuro","Währung","Kurs","Betrag","DatumEingabe")
VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13)',N'@P1
int,@P2 smallint,@P3 real,@P4 nvarchar(10),@P5 money,@P6 nvarchar(5),@P7
int,@P8 real,@P9 money,@P10 nvarchar(3),@P11 float,@P12 float,@P13
datetime',9281,4,1,N'BBBBBBBBBB',$0.0000,N'Stück',0,0,18999999761581421,$0.0000,N'EUR',1,0,'2008-08-27 00:00:00:000'

Error: 156, Severity: 15, State: 1

Falsche Syntax in der Nähe des 'WHERE'-Schlüsselwortes.
(in English something like: Wrong syntax near 'WHERE' keyword.)

exec sp_executesql N'SELECT * FROM (SELECT TOP 100 PERCENT
dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS ORDER BY Pos) WHERE
dbo.t_RECHNUNGS_POS.[Re-PosID] = @P1',N'@P1 int',0
---------

I conlude from this that the resync command of the subform has a problem and
that this is the reason why subsequent attempts to update the new record
fail. The resync command was not necessary with Access 3003/SQL Server 2000.
Can anyone please help me with the resync command? I could not figure out
what I am doing wrong.

Thank you in advance.
 
The successful insert statement uses "ReID" as the primary key attribute,
but the select (which I guess is the resync) uses [Re-PosID] in the where
clause. It also uses a value of 0 for that attribute, which might be because
it has the wrong attribute name?
 
First of all, thank you for your reply.
Yes, the SELECT command is the resync command, namely of the subform. "ReID"
is the primary key of the unique table of the main form and it is the link of
the records in the subform to the record in the main form. The primary key of
the table in the subform is "Re-PosID". This is why I tried both "ReID" and
"Re-posID". In either case Access does not fill in the correct value for
"@P1". This value is always 0.


Paul Shapiro said:
The successful insert statement uses "ReID" as the primary key attribute,
but the select (which I guess is the resync) uses [Re-PosID] in the where
clause. It also uses a value of 0 for that attribute, which might be because
it has the wrong attribute name?

Helmut Herrmann said:
After upgrading from Access 2003/SQL Server 2000 to Access 2007/SQL Server
2005 the following problem occurred:

In some forms with subforms the data source of the main form is a view and
the data source of the subform is a table with a field containing the ID
of
the record in the main form. When I insert a new record in the subform
this
works well. But when I want to edit this new record, I get the error
message
that another user has changed the record which is definitely not true. It
is
not possible to save the edited record. I have to leave the record in the
main form and reenter it in order to be able to edit the new record in the
subform.

I used SQL profiler to see what is going on. Regarding insertion of the
new
record in the subfrom the profiler recorded the following:
---------
exec sp_executesql N'INSERT INTO "zentraleSQL"."dbo"."t_RECHNUNGS_POS"
("ReID","Pos","Anzahl","Bezeichnung","Einzelpreis","Einheit","Rabatt","MwStSatz","EinzelpreisEuro","Währung","Kurs","Betrag","DatumEingabe")
VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13)',N'@P1
int,@P2 smallint,@P3 real,@P4 nvarchar(10),@P5 money,@P6 nvarchar(5),@P7
int,@P8 real,@P9 money,@P10 nvarchar(3),@P11 float,@P12 float,@P13
datetime',9281,4,1,N'BBBBBBBBBB',$0.0000,N'Stück',0,0,18999999761581421,$0.0000,N'EUR',1,0,'2008-08-27
00:00:00:000'

Error: 156, Severity: 15, State: 1

Falsche Syntax in der Nähe des 'WHERE'-Schlüsselwortes.
(in English something like: Wrong syntax near 'WHERE' keyword.)

exec sp_executesql N'SELECT * FROM (SELECT TOP 100 PERCENT
dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS ORDER BY Pos) WHERE
dbo.t_RECHNUNGS_POS.[Re-PosID] = @P1',N'@P1 int',0
---------

I conlude from this that the resync command of the subform has a problem
and
that this is the reason why subsequent attempts to update the new record
fail. The resync command was not necessary with Access 3003/SQL Server
2000.
Can anyone please help me with the resync command? I could not figure out
what I am doing wrong.
Click to expand...
Click to expand...
 
Can you provide the record source and resync commands you're using in the
subform? You earlier stated that the SQL Profiler trace was from the
successful subform insertion. Is that correct, or is that the trace from the
main form insert? Both the successful insert and the failed select seem to
reference the same table name, so it would seem that either both are from
the main form or both from the subform? In which case the PK attribute name
would have to be the same?

Helmut Herrmann said:
First of all, thank you for your reply.
Yes, the SELECT command is the resync command, namely of the subform.
"ReID"
is the primary key of the unique table of the main form and it is the link
of
the records in the subform to the record in the main form. The primary key
of
the table in the subform is "Re-PosID". This is why I tried both "ReID"
and
"Re-posID". In either case Access does not fill in the correct value for
"@P1". This value is always 0.


Paul Shapiro said:
The successful insert statement uses "ReID" as the primary key attribute,
but the select (which I guess is the resync) uses [Re-PosID] in the where
clause. It also uses a value of 0 for that attribute, which might be
because
it has the wrong attribute name?

Helmut Herrmann said:
After upgrading from Access 2003/SQL Server 2000 to Access 2007/SQL
Server
2005 the following problem occurred:

In some forms with subforms the data source of the main form is a view
and
the data source of the subform is a table with a field containing the
ID
of
the record in the main form. When I insert a new record in the subform
this
works well. But when I want to edit this new record, I get the error
message
that another user has changed the record which is definitely not true.
It
is
not possible to save the edited record. I have to leave the record in
the
main form and reenter it in order to be able to edit the new record in
the
subform.

I used SQL profiler to see what is going on. Regarding insertion of the
new
record in the subfrom the profiler recorded the following:
---------
exec sp_executesql N'INSERT INTO "zentraleSQL"."dbo"."t_RECHNUNGS_POS"
("ReID","Pos","Anzahl","Bezeichnung","Einzelpreis","Einheit","Rabatt","MwStSatz","EinzelpreisEuro","Währung","Kurs","Betrag","DatumEingabe")
VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13)',N'@P1
int,@P2 smallint,@P3 real,@P4 nvarchar(10),@P5 money,@P6
nvarchar(5),@P7
int,@P8 real,@P9 money,@P10 nvarchar(3),@P11 float,@P12 float,@P13
datetime',9281,4,1,N'BBBBBBBBBB',$0.0000,N'Stück',0,0,18999999761581421,$0.0000,N'EUR',1,0,'2008-08-27
00:00:00:000'

Error: 156, Severity: 15, State: 1

Falsche Syntax in der Nähe des 'WHERE'-Schlüsselwortes.
(in English something like: Wrong syntax near 'WHERE' keyword.)

exec sp_executesql N'SELECT * FROM (SELECT TOP 100 PERCENT
dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS ORDER BY Pos) WHERE
dbo.t_RECHNUNGS_POS.[Re-PosID] = @P1',N'@P1 int',0
---------

I conlude from this that the resync command of the subform has a
problem
and
that this is the reason why subsequent attempts to update the new
record
fail. The resync command was not necessary with Access 3003/SQL Server
2000.
Can anyone please help me with the resync command? I could not figure
out
what I am doing wrong.
Click to expand...
Click to expand...
Click to expand...
 
The SQL Profiler trace contains only subform messages because nothing happens
in the main form. The record source in the subform settings is as follows:

SELECT TOP 100 PERCENT dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS ORDER
BY Pos

The resync command is this:

SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS ORDER BY
Pos) WHERE (ReID = ?)
or
SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS ORDER BY
Pos) WHERE ([Re-PosID] = ?)

As I wrote before, "Re-PosID" is the PK of "t_RECHNUNGS_POS" (subform) and
"ReID" is one of the fields in this table and is linked to the PK "ReID" of
"t_Rechnungen" in the main form.
 
Sorry to post this twice, but the first time I replied to the wrong post.
---
The SQL Profiler trace contains only subform messages because nothing
happens in the main form. The record source in the subform settings is as
follows:

SELECT TOP 100 PERCENT dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS ORDER
BY Pos

The resync command is this:

SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS ORDER BY
Pos) WHERE (ReID = ?)
or
SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS ORDER BY
Pos) WHERE ([Re-PosID] = ?)

As I wrote before, "Re-PosID" is the PK of "t_RECHNUNGS_POS" (subform) and
"ReID" is one of the fields in this table and is linked to the PK "ReID" of
"t_Rechnungen" in the main form.

Paul Shapiro said:
Can you provide the record source and resync commands you're using in the
subform? You earlier stated that the SQL Profiler trace was from the
successful subform insertion. Is that correct, or is that the trace from the
main form insert? Both the successful insert and the failed select seem to
reference the same table name, so it would seem that either both are from
the main form or both from the subform? In which case the PK attribute name
would have to be the same?

Helmut Herrmann said:
First of all, thank you for your reply.
Yes, the SELECT command is the resync command, namely of the subform.
"ReID"
is the primary key of the unique table of the main form and it is the link
of
the records in the subform to the record in the main form. The primary key
of
the table in the subform is "Re-PosID". This is why I tried both "ReID"
and
"Re-posID". In either case Access does not fill in the correct value for
"@P1". This value is always 0.


Paul Shapiro said:
The successful insert statement uses "ReID" as the primary key attribute,
but the select (which I guess is the resync) uses [Re-PosID] in the where
clause. It also uses a value of 0 for that attribute, which might be
because
it has the wrong attribute name?

message After upgrading from Access 2003/SQL Server 2000 to Access 2007/SQL
Server
2005 the following problem occurred:

In some forms with subforms the data source of the main form is a view
and
the data source of the subform is a table with a field containing the
ID
of
the record in the main form. When I insert a new record in the subform
this
works well. But when I want to edit this new record, I get the error
message
that another user has changed the record which is definitely not true.
It
is
not possible to save the edited record. I have to leave the record in
the
main form and reenter it in order to be able to edit the new record in
the
subform.

I used SQL profiler to see what is going on. Regarding insertion of the
new
record in the subfrom the profiler recorded the following:
---------
exec sp_executesql N'INSERT INTO "zentraleSQL"."dbo"."t_RECHNUNGS_POS"
("ReID","Pos","Anzahl","Bezeichnung","Einzelpreis","Einheit","Rabatt","MwStSatz","EinzelpreisEuro","Währung","Kurs","Betrag","DatumEingabe")
VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13)',N'@P1
int,@P2 smallint,@P3 real,@P4 nvarchar(10),@P5 money,@P6
nvarchar(5),@P7
int,@P8 real,@P9 money,@P10 nvarchar(3),@P11 float,@P12 float,@P13
datetime',9281,4,1,N'BBBBBBBBBB',$0.0000,N'Stück',0,0,18999999761581421,$0.0000,N'EUR',1,0,'2008-08-27
00:00:00:000'

Error: 156, Severity: 15, State: 1

Falsche Syntax in der Nähe des 'WHERE'-Schlüsselwortes.
(in English something like: Wrong syntax near 'WHERE' keyword.)

exec sp_executesql N'SELECT * FROM (SELECT TOP 100 PERCENT
dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS ORDER BY Pos) WHERE
dbo.t_RECHNUNGS_POS.[Re-PosID] = @P1',N'@P1 int',0
---------

I conlude from this that the resync command of the subform has a
problem
and
that this is the reason why subsequent attempts to update the new
record
fail. The resync command was not necessary with Access 3003/SQL Server
2000.
Can anyone please help me with the resync command? I could not figure
out
what I am doing wrong.
Click to expand...
Click to expand...
Click to expand...
 
Since you're only selecting a single row, you can simplify the resync
command. I believe it has to use the PK attributes, and cannot use any other
attributes. What happens if you set the resync command to:

SELECT * FROM dbo.t_RECHNUNGS_POS WHERE ([Re-PosID] = ?

Also, sometimes Access seems to do better when the resync command is left
blank. I think though your initial post said that's how it was in Access
2003, and now it stopped working in Access 2007, which is why you're
specifying the resync command?

If you're entering the sql directly into the subform's record source
setting, you don't need the TOP 100 PERCENT clause. That's only needed in a
view definition, where it was never a recommended syntax. I believe sorting
in a view is ignored in SQL 2005 and later even with that clause. Access
seems to do better with simpler record source settings. You could try
simplifying the subform record source to:

SELECT * FROM dbo.t_RECHNUNGS_POS ORDER BY Pos

Helmut Herrmann said:
Sorry to post this twice, but the first time I replied to the wrong post.
---
The SQL Profiler trace contains only subform messages because nothing
happens in the main form. The record source in the subform settings is as
follows:

SELECT TOP 100 PERCENT dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS
ORDER
BY Pos

The resync command is this:

SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS ORDER BY
Pos) WHERE (ReID = ?)
or
SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS ORDER BY
Pos) WHERE ([Re-PosID] = ?)

As I wrote before, "Re-PosID" is the PK of "t_RECHNUNGS_POS" (subform) and
"ReID" is one of the fields in this table and is linked to the PK "ReID"
of
"t_Rechnungen" in the main form.

Paul Shapiro said:
Can you provide the record source and resync commands you're using in the
subform? You earlier stated that the SQL Profiler trace was from the
successful subform insertion. Is that correct, or is that the trace from
the
main form insert? Both the successful insert and the failed select seem
to
reference the same table name, so it would seem that either both are from
the main form or both from the subform? In which case the PK attribute
name
would have to be the same?

Helmut Herrmann said:
First of all, thank you for your reply.
Yes, the SELECT command is the resync command, namely of the subform.
"ReID"
is the primary key of the unique table of the main form and it is the
link
of
the records in the subform to the record in the main form. The primary
key
of
the table in the subform is "Re-PosID". This is why I tried both "ReID"
and
"Re-posID". In either case Access does not fill in the correct value
for
"@P1". This value is always 0.


:

The successful insert statement uses "ReID" as the primary key
attribute,
but the select (which I guess is the resync) uses [Re-PosID] in the
where
clause. It also uses a value of 0 for that attribute, which might be
because
it has the wrong attribute name?

message After upgrading from Access 2003/SQL Server 2000 to Access 2007/SQL
Server
2005 the following problem occurred:

In some forms with subforms the data source of the main form is a
view
and
the data source of the subform is a table with a field containing
the
ID
of
the record in the main form. When I insert a new record in the
subform
this
works well. But when I want to edit this new record, I get the error
message
that another user has changed the record which is definitely not
true.
It
is
not possible to save the edited record. I have to leave the record
in
the
main form and reenter it in order to be able to edit the new record
in
the
subform.

I used SQL profiler to see what is going on. Regarding insertion of
the
new
record in the subfrom the profiler recorded the following:
---------
exec sp_executesql N'INSERT INTO
"zentraleSQL"."dbo"."t_RECHNUNGS_POS"
("ReID","Pos","Anzahl","Bezeichnung","Einzelpreis","Einheit","Rabatt","MwStSatz","EinzelpreisEuro","Währung","Kurs","Betrag","DatumEingabe")
VALUES
(@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13)',N'@P1
int,@P2 smallint,@P3 real,@P4 nvarchar(10),@P5 money,@P6
nvarchar(5),@P7
int,@P8 real,@P9 money,@P10 nvarchar(3),@P11 float,@P12 float,@P13
datetime',9281,4,1,N'BBBBBBBBBB',$0.0000,N'Stück',0,0,18999999761581421,$0.0000,N'EUR',1,0,'2008-08-27
00:00:00:000'

Error: 156, Severity: 15, State: 1

Falsche Syntax in der Nähe des 'WHERE'-Schlüsselwortes.
(in English something like: Wrong syntax near 'WHERE' keyword.)

exec sp_executesql N'SELECT * FROM (SELECT TOP 100 PERCENT
dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS ORDER BY Pos) WHERE
dbo.t_RECHNUNGS_POS.[Re-PosID] = @P1',N'@P1 int',0
---------

I conlude from this that the resync command of the subform has a
problem
and
that this is the reason why subsequent attempts to update the new
record
fail. The resync command was not necessary with Access 3003/SQL
Server
2000.
Can anyone please help me with the resync command? I could not
figure
out
what I am doing wrong.
Click to expand...
Click to expand...
Click to expand...
 
I followed your hint and removed the "TOP 100 PERCENT" clause. The subform
record source is now

SELECT * FROM dbo.t_RECHNUNGS_POS ORDER BY Pos

and everything works as before. Then I removed the resync command in order
to see what happens (sure, I tested this before but could not remember the
details). Without the resync command I get:

1. SQL Profiler trace (note that the value for @P1 is correct):
exec sp_executesql N'SELECT * FROM (SELECT dbo.t_RECHNUNGS_POS.* FROM
dbo.t_RECHNUNGS_POS) AS DRVD_TBL WHERE "Re-PosID" = @P1 ORDER BY Pos',N'@P1
int',22411

2. Error message in Access:
The data was added to the database but the data won't be displayed in the
form because it doesn't satisfy the criteria in the underlying record source.

This is the reason why I added the resync command after upgrading from
Access 2003/SQL 2000 to Access 2007/SQL 2005. Now I inserted the simplified
resync command which now reads

SELECT * FROM (SELECT * FROM dbo.t_RECHNUNGS_POS) WHERE ([Re-PosID] = ?)

Unfortunately, this results in the same error described in my first post.
SQL Profiler trace (note that the value for @P1 is 0):

exec sp_executesql N'SELECT * FROM (SELECT * FROM dbo.t_RECHNUNGS_POS) WHERE
([Re-PosID] = @P1)',N'@P1 int',0

By the way, I found a similar problem report at
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2663643&SiteID=17
but the solution affects Vista or Server 2008 only. Our problem is with
Vista and XP and with Server 2003.

Paul Shapiro said:
Since you're only selecting a single row, you can simplify the resync
command. I believe it has to use the PK attributes, and cannot use any other
attributes. What happens if you set the resync command to:

SELECT * FROM dbo.t_RECHNUNGS_POS WHERE ([Re-PosID] = ?

Also, sometimes Access seems to do better when the resync command is left
blank. I think though your initial post said that's how it was in Access
2003, and now it stopped working in Access 2007, which is why you're
specifying the resync command?

If you're entering the sql directly into the subform's record source
setting, you don't need the TOP 100 PERCENT clause. That's only needed in a
view definition, where it was never a recommended syntax. I believe sorting
in a view is ignored in SQL 2005 and later even with that clause. Access
seems to do better with simpler record source settings. You could try
simplifying the subform record source to:

SELECT * FROM dbo.t_RECHNUNGS_POS ORDER BY Pos

Helmut Herrmann said:
Sorry to post this twice, but the first time I replied to the wrong post.
---
The SQL Profiler trace contains only subform messages because nothing
happens in the main form. The record source in the subform settings is as
follows:

SELECT TOP 100 PERCENT dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS
ORDER
BY Pos

The resync command is this:

SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS ORDER BY
Pos) WHERE (ReID = ?)
or
SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS ORDER BY
Pos) WHERE ([Re-PosID] = ?)

As I wrote before, "Re-PosID" is the PK of "t_RECHNUNGS_POS" (subform) and
"ReID" is one of the fields in this table and is linked to the PK "ReID"
of
"t_Rechnungen" in the main form.

Paul Shapiro said:
Can you provide the record source and resync commands you're using in the
subform? You earlier stated that the SQL Profiler trace was from the
successful subform insertion. Is that correct, or is that the trace from
the
main form insert? Both the successful insert and the failed select seem
to
reference the same table name, so it would seem that either both are from
the main form or both from the subform? In which case the PK attribute
name
would have to be the same?

message First of all, thank you for your reply.
Yes, the SELECT command is the resync command, namely of the subform.
"ReID"
is the primary key of the unique table of the main form and it is the
link
of
the records in the subform to the record in the main form. The primary
key
of
the table in the subform is "Re-PosID". This is why I tried both "ReID"
and
"Re-posID". In either case Access does not fill in the correct value
for
"@P1". This value is always 0.


:

The successful insert statement uses "ReID" as the primary key
attribute,
but the select (which I guess is the resync) uses [Re-PosID] in the
where
clause. It also uses a value of 0 for that attribute, which might be
because
it has the wrong attribute name?

message After upgrading from Access 2003/SQL Server 2000 to Access 2007/SQL
Server
2005 the following problem occurred:

In some forms with subforms the data source of the main form is a
view
and
the data source of the subform is a table with a field containing
the
ID
of
the record in the main form. When I insert a new record in the
subform
this
works well. But when I want to edit this new record, I get the error
message
that another user has changed the record which is definitely not
true.
It
is
not possible to save the edited record. I have to leave the record
in
the
main form and reenter it in order to be able to edit the new record
in
the
subform.

I used SQL profiler to see what is going on. Regarding insertion of
the
new
record in the subfrom the profiler recorded the following:
---------
exec sp_executesql N'INSERT INTO
"zentraleSQL"."dbo"."t_RECHNUNGS_POS"
("ReID","Pos","Anzahl","Bezeichnung","Einzelpreis","Einheit","Rabatt","MwStSatz","EinzelpreisEuro","Währung","Kurs","Betrag","DatumEingabe")
VALUES
(@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13)',N'@P1
int,@P2 smallint,@P3 real,@P4 nvarchar(10),@P5 money,@P6
nvarchar(5),@P7
int,@P8 real,@P9 money,@P10 nvarchar(3),@P11 float,@P12 float,@P13
datetime',9281,4,1,N'BBBBBBBBBB',$0.0000,N'Stück',0,0,18999999761581421,$0.0000,N'EUR',1,0,'2008-08-27
00:00:00:000'

Error: 156, Severity: 15, State: 1

Falsche Syntax in der Nähe des 'WHERE'-Schlüsselwortes.
(in English something like: Wrong syntax near 'WHERE' keyword.)

exec sp_executesql N'SELECT * FROM (SELECT TOP 100 PERCENT
dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS ORDER BY Pos) WHERE
dbo.t_RECHNUNGS_POS.[Re-PosID] = @P1',N'@P1 int',0
---------

I conlude from this that the resync command of the subform has a
problem
and
that this is the reason why subsequent attempts to update the new
record
fail. The resync command was not necessary with Access 3003/SQL
Server
2000.
Can anyone please help me with the resync command? I could not
figure
out
what I am doing wrong.
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I know this is a real pain. This kind of hidden inner working is part
of why I plan on shifting my development out of Access. I've fought through
this one before too.

Searching for the error message on google yields plenty of hits, with
suggestions for:
1. Specify the Unique Table in the form properties, with or without a resync
command.
2. If using any stored procedures, include Set NoCount On in the beginning
of the SP since Access seems to get confused when affected record counts are
returned.
3. A trigger on any affected tables can return the incorrect ID value,
similar to what you're seeing, since Access uses @@identity instead of the
scope identity value. This might have been changed in Access 2007. Check the
details with SQL Profiler.
4. Replicated databases are more subject to this issue since they always
have system triggers.
5. http://support.microsoft.com/kb/951937 has a hotfix for "On a computer
that is running Windows Vista, Windows Server 2008, or Windows XP, an
incorrect value is returned when an application queries the identity column
value of a newly inserted row in various versions of SQL Server 2005 and of
SQL Server 2000".
6. From another discussion, "We now have updates to correct this problem
available on the Microsoft Download Center. Windows Vista and Windows
Server2008 link is:"
http://www.microsoft.com/downloads/...33-83E3-4D06-9807-06EF573BC168&displaylang=en
7. The issue is also discussed in http://support.microsoft.com/kb/q248095/

Helmut Herrmann said:
I followed your hint and removed the "TOP 100 PERCENT" clause. The subform
record source is now

SELECT * FROM dbo.t_RECHNUNGS_POS ORDER BY Pos

and everything works as before. Then I removed the resync command in order
to see what happens (sure, I tested this before but could not remember the
details). Without the resync command I get:

1. SQL Profiler trace (note that the value for @P1 is correct):
exec sp_executesql N'SELECT * FROM (SELECT dbo.t_RECHNUNGS_POS.* FROM
dbo.t_RECHNUNGS_POS) AS DRVD_TBL WHERE "Re-PosID" = @P1 ORDER BY
Pos',N'@P1
int',22411

2. Error message in Access:
The data was added to the database but the data won't be displayed in the
form because it doesn't satisfy the criteria in the underlying record
source.

This is the reason why I added the resync command after upgrading from
Access 2003/SQL 2000 to Access 2007/SQL 2005. Now I inserted the
simplified
resync command which now reads

SELECT * FROM (SELECT * FROM dbo.t_RECHNUNGS_POS) WHERE ([Re-PosID] = ?)

Unfortunately, this results in the same error described in my first post.
SQL Profiler trace (note that the value for @P1 is 0):

exec sp_executesql N'SELECT * FROM (SELECT * FROM dbo.t_RECHNUNGS_POS)
WHERE
([Re-PosID] = @P1)',N'@P1 int',0

By the way, I found a similar problem report at
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2663643&SiteID=17
but the solution affects Vista or Server 2008 only. Our problem is with
Vista and XP and with Server 2003.

Paul Shapiro said:
Since you're only selecting a single row, you can simplify the resync
command. I believe it has to use the PK attributes, and cannot use any
other
attributes. What happens if you set the resync command to:

SELECT * FROM dbo.t_RECHNUNGS_POS WHERE ([Re-PosID] = ?

Also, sometimes Access seems to do better when the resync command is left
blank. I think though your initial post said that's how it was in Access
2003, and now it stopped working in Access 2007, which is why you're
specifying the resync command?

If you're entering the sql directly into the subform's record source
setting, you don't need the TOP 100 PERCENT clause. That's only needed in
a
view definition, where it was never a recommended syntax. I believe
sorting
in a view is ignored in SQL 2005 and later even with that clause. Access
seems to do better with simpler record source settings. You could try
simplifying the subform record source to:

SELECT * FROM dbo.t_RECHNUNGS_POS ORDER BY Pos

Helmut Herrmann said:
Sorry to post this twice, but the first time I replied to the wrong
post.
---
The SQL Profiler trace contains only subform messages because nothing
happens in the main form. The record source in the subform settings is
as
follows:

SELECT TOP 100 PERCENT dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS
ORDER
BY Pos

The resync command is this:

SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS ORDER
BY
Pos) WHERE (ReID = ?)
or
SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS ORDER
BY
Pos) WHERE ([Re-PosID] = ?)

As I wrote before, "Re-PosID" is the PK of "t_RECHNUNGS_POS" (subform)
and
"ReID" is one of the fields in this table and is linked to the PK
"ReID"
of
"t_Rechnungen" in the main form.

:

Can you provide the record source and resync commands you're using in
the
subform? You earlier stated that the SQL Profiler trace was from the
successful subform insertion. Is that correct, or is that the trace
from
the
main form insert? Both the successful insert and the failed select
seem
to
reference the same table name, so it would seem that either both are
from
the main form or both from the subform? In which case the PK attribute
name
would have to be the same?

message First of all, thank you for your reply.
Yes, the SELECT command is the resync command, namely of the
subform.
"ReID"
is the primary key of the unique table of the main form and it is
the
link
of
the records in the subform to the record in the main form. The
primary
key
of
the table in the subform is "Re-PosID". This is why I tried both
"ReID"
and
"Re-posID". In either case Access does not fill in the correct value
for
"@P1". This value is always 0.


:

The successful insert statement uses "ReID" as the primary key
attribute,
but the select (which I guess is the resync) uses [Re-PosID] in the
where
clause. It also uses a value of 0 for that attribute, which might
be
because
it has the wrong attribute name?

in
message After upgrading from Access 2003/SQL Server 2000 to Access
2007/SQL
Server
2005 the following problem occurred:

In some forms with subforms the data source of the main form is a
view
and
the data source of the subform is a table with a field containing
the
ID
of
the record in the main form. When I insert a new record in the
subform
this
works well. But when I want to edit this new record, I get the
error
message
that another user has changed the record which is definitely not
true.
It
is
not possible to save the edited record. I have to leave the
record
in
the
main form and reenter it in order to be able to edit the new
record
in
the
subform.

I used SQL profiler to see what is going on. Regarding insertion
of
the
new
record in the subfrom the profiler recorded the following:
---------
exec sp_executesql N'INSERT INTO
"zentraleSQL"."dbo"."t_RECHNUNGS_POS"
("ReID","Pos","Anzahl","Bezeichnung","Einzelpreis","Einheit","Rabatt","MwStSatz","EinzelpreisEuro","Währung","Kurs","Betrag","DatumEingabe")
VALUES
(@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13)',N'@P1
int,@P2 smallint,@P3 real,@P4 nvarchar(10),@P5 money,@P6
nvarchar(5),@P7
int,@P8 real,@P9 money,@P10 nvarchar(3),@P11 float,@P12
float,@P13
datetime',9281,4,1,N'BBBBBBBBBB',$0.0000,N'Stück',0,0,18999999761581421,$0.0000,N'EUR',1,0,'2008-08-27
00:00:00:000'

Error: 156, Severity: 15, State: 1

Falsche Syntax in der Nähe des 'WHERE'-Schlüsselwortes.
(in English something like: Wrong syntax near 'WHERE' keyword.)

exec sp_executesql N'SELECT * FROM (SELECT TOP 100 PERCENT
dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS ORDER BY Pos)
WHERE
dbo.t_RECHNUNGS_POS.[Re-PosID] = @P1',N'@P1 int',0
---------

I conlude from this that the resync command of the subform has a
problem
and
that this is the reason why subsequent attempts to update the new
record
fail. The resync command was not necessary with Access 3003/SQL
Server
2000.
Can anyone please help me with the resync command? I could not
figure
out
what I am doing wrong.
Click to expand...
Click to expand...
Click to expand...
 
None of these suggestions seems to apply to our problem. Fortunately, we
found a solution by chance when we tackled another problem in another form.
For ordering we do no longer use SELECT statements in the RecordSource form
property. Instead we set the form properties OrderBy and OrderByOnLoad
appropriately.

Thank you very much for your time.

Paul Shapiro said:
Sorry, I know this is a real pain. This kind of hidden inner working is part
of why I plan on shifting my development out of Access. I've fought through
this one before too.

Searching for the error message on google yields plenty of hits, with
suggestions for:
1. Specify the Unique Table in the form properties, with or without a resync
command.
2. If using any stored procedures, include Set NoCount On in the beginning
of the SP since Access seems to get confused when affected record counts are
returned.
3. A trigger on any affected tables can return the incorrect ID value,
similar to what you're seeing, since Access uses @@identity instead of the
scope identity value. This might have been changed in Access 2007. Check the
details with SQL Profiler.
4. Replicated databases are more subject to this issue since they always
have system triggers.
5. http://support.microsoft.com/kb/951937 has a hotfix for "On a computer
that is running Windows Vista, Windows Server 2008, or Windows XP, an
incorrect value is returned when an application queries the identity column
value of a newly inserted row in various versions of SQL Server 2005 and of
SQL Server 2000".
6. From another discussion, "We now have updates to correct this problem
available on the Microsoft Download Center. Windows Vista and Windows
Server2008 link is:"
http://www.microsoft.com/downloads/...33-83E3-4D06-9807-06EF573BC168&displaylang=en
7. The issue is also discussed in http://support.microsoft.com/kb/q248095/

Helmut Herrmann said:
I followed your hint and removed the "TOP 100 PERCENT" clause. The subform
record source is now

SELECT * FROM dbo.t_RECHNUNGS_POS ORDER BY Pos

and everything works as before. Then I removed the resync command in order
to see what happens (sure, I tested this before but could not remember the
details). Without the resync command I get:

1. SQL Profiler trace (note that the value for @P1 is correct):
exec sp_executesql N'SELECT * FROM (SELECT dbo.t_RECHNUNGS_POS.* FROM
dbo.t_RECHNUNGS_POS) AS DRVD_TBL WHERE "Re-PosID" = @P1 ORDER BY
Pos',N'@P1
int',22411

2. Error message in Access:
The data was added to the database but the data won't be displayed in the
form because it doesn't satisfy the criteria in the underlying record
source.

This is the reason why I added the resync command after upgrading from
Access 2003/SQL 2000 to Access 2007/SQL 2005. Now I inserted the
simplified
resync command which now reads

SELECT * FROM (SELECT * FROM dbo.t_RECHNUNGS_POS) WHERE ([Re-PosID] = ?)

Unfortunately, this results in the same error described in my first post.
SQL Profiler trace (note that the value for @P1 is 0):

exec sp_executesql N'SELECT * FROM (SELECT * FROM dbo.t_RECHNUNGS_POS)
WHERE
([Re-PosID] = @P1)',N'@P1 int',0

By the way, I found a similar problem report at
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2663643&SiteID=17
but the solution affects Vista or Server 2008 only. Our problem is with
Vista and XP and with Server 2003.

Paul Shapiro said:
Since you're only selecting a single row, you can simplify the resync
command. I believe it has to use the PK attributes, and cannot use any
other
attributes. What happens if you set the resync command to:

SELECT * FROM dbo.t_RECHNUNGS_POS WHERE ([Re-PosID] = ?

Also, sometimes Access seems to do better when the resync command is left
blank. I think though your initial post said that's how it was in Access
2003, and now it stopped working in Access 2007, which is why you're
specifying the resync command?

If you're entering the sql directly into the subform's record source
setting, you don't need the TOP 100 PERCENT clause. That's only needed in
a
view definition, where it was never a recommended syntax. I believe
sorting
in a view is ignored in SQL 2005 and later even with that clause. Access
seems to do better with simpler record source settings. You could try
simplifying the subform record source to:

SELECT * FROM dbo.t_RECHNUNGS_POS ORDER BY Pos

message Sorry to post this twice, but the first time I replied to the wrong
post.
---
The SQL Profiler trace contains only subform messages because nothing
happens in the main form. The record source in the subform settings is
as
follows:

SELECT TOP 100 PERCENT dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS
ORDER
BY Pos

The resync command is this:

SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS ORDER
BY
Pos) WHERE (ReID = ?)
or
SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS ORDER
BY
Pos) WHERE ([Re-PosID] = ?)

As I wrote before, "Re-PosID" is the PK of "t_RECHNUNGS_POS" (subform)
and
"ReID" is one of the fields in this table and is linked to the PK
"ReID"
of
"t_Rechnungen" in the main form.

:

Can you provide the record source and resync commands you're using in
the
subform? You earlier stated that the SQL Profiler trace was from the
successful subform insertion. Is that correct, or is that the trace
from
the
main form insert? Both the successful insert and the failed select
seem
to
reference the same table name, so it would seem that either both are
from
the main form or both from the subform? In which case the PK attribute
name
would have to be the same?

message First of all, thank you for your reply.
Yes, the SELECT command is the resync command, namely of the
subform.
"ReID"
is the primary key of the unique table of the main form and it is
the
link
of
the records in the subform to the record in the main form. The
primary
key
of
the table in the subform is "Re-PosID". This is why I tried both
"ReID"
and
"Re-posID". In either case Access does not fill in the correct value
for
"@P1". This value is always 0.


:

The successful insert statement uses "ReID" as the primary key
attribute,
but the select (which I guess is the resync) uses [Re-PosID] in the
where
clause. It also uses a value of 0 for that attribute, which might
be
because
it has the wrong attribute name?

in
message After upgrading from Access 2003/SQL Server 2000 to Access
2007/SQL
Server
2005 the following problem occurred:

In some forms with subforms the data source of the main form is a
view
and
the data source of the subform is a table with a field containing
the
ID
of
the record in the main form. When I insert a new record in the
subform
this
works well. But when I want to edit this new record, I get the
error
message
that another user has changed the record which is definitely not
true.
It
is
not possible to save the edited record. I have to leave the
record
in
the
main form and reenter it in order to be able to edit the new
record
in
the
subform.

I used SQL profiler to see what is going on. Regarding insertion
of
the
new
record in the subfrom the profiler recorded the following:
---------
exec sp_executesql N'INSERT INTO
"zentraleSQL"."dbo"."t_RECHNUNGS_POS"
("ReID","Pos","Anzahl","Bezeichnung","Einzelpreis","Einheit","Rabatt","MwStSatz","EinzelpreisEuro","Währung","Kurs","Betrag","DatumEingabe")
VALUES
(@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13)',N'@P1
int,@P2 smallint,@P3 real,@P4 nvarchar(10),@P5 money,@P6
nvarchar(5),@P7
int,@P8 real,@P9 money,@P10 nvarchar(3),@P11 float,@P12
float,@P13
datetime',9281,4,1,N'BBBBBBBBBB',$0.0000,N'Stück',0,0,18999999761581421,$0.0000,N'EUR',1,0,'2008-08-27
00:00:00:000'

Error: 156, Severity: 15, State: 1

Falsche Syntax in der Nähe des 'WHERE'-Schlüsselwortes.
(in English something like: Wrong syntax near 'WHERE' keyword.)

exec sp_executesql N'SELECT * FROM (SELECT TOP 100 PERCENT
dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS ORDER BY Pos)
WHERE
dbo.t_RECHNUNGS_POS.[Re-PosID] = @P1',N'@P1 int',0
---------

I conlude from this that the resync command of the subform has a
problem
and
that this is the reason why subsequent attempts to update the new
record
fail. The resync command was not necessary with Access 3003/SQL
Server
2000.
Can anyone please help me with the resync command? I could not
figure
out
what I am doing wrong.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top