Resync problem

Discussion in 'Microsoft Access ADP SQL Server' started by Helmut Herrmann, Aug 27, 2008.

  1. 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.
     
    Helmut Herrmann, Aug 27, 2008
    #1
    1. Advertisements

  2. Helmut Herrmann

    Paul Shapiro Guest

    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" <> wrote in
    message news:...
    > 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.
     
    Paul Shapiro, Aug 27, 2008
    #2
    1. Advertisements

  3. 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" wrote:

    > 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" <> wrote in
    > message news:...
    > > 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.
     
    Helmut Herrmann, Aug 27, 2008
    #3
  4. Helmut Herrmann

    Paul Shapiro Guest

    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" <> wrote in
    message news:...
    > 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" wrote:
    >
    >> 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" <> wrote in
    >> message news:...
    >> > 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.

    >
     
    Paul Shapiro, Aug 27, 2008
    #4
  5. 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.


    "Helmut Herrmann" wrote:

    > 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.
     
    Helmut Herrmann, Aug 27, 2008
    #5
  6. 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" wrote:

    > 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" <> wrote in
    > message news:...
    > > 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" wrote:
    > >
    > >> 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" <> wrote in
    > >> message news:...
    > >> > 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.

    > >

    >
    >
     
    Helmut Herrmann, Aug 28, 2008
    #6
  7. Helmut Herrmann

    Paul Shapiro Guest

    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" <> wrote in
    message news:...
    > 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" wrote:
    >
    >> 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" <> wrote in
    >> message news:...
    >> > 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" wrote:
    >> >
    >> >> 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" <> wrote in
    >> >> message news:...
    >> >> > 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.
    >> >

    >>
    >>
     
    Paul Shapiro, Aug 28, 2008
    #7
  8. 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" wrote:

    > 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" <> wrote in
    > message news:...
    > > 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" wrote:
    > >
    > >> 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" <> wrote in
    > >> message news:...
    > >> > 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" wrote:
    > >> >
    > >> >> 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" <> wrote in
    > >> >> message news:...
    > >> >> > 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.
    > >> >
    > >>
    > >>

    >
    >
     
    Helmut Herrmann, Aug 28, 2008
    #8
  9. Helmut Herrmann

    Paul Shapiro Guest

    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" <> wrote in
    message news:...
    >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" wrote:
    >
    >> 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" <> wrote in
    >> message news:...
    >> > 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" wrote:
    >> >
    >> >> 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" <> wrote in
    >> >> message news:...
    >> >> > 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" wrote:
    >> >> >
    >> >> >> 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" <> wrote
    >> >> >> in
    >> >> >> message news:...
    >> >> >> > 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.
    >> >> >
    >> >>
    >> >>

    >>
    >>
     
    Paul Shapiro, Aug 28, 2008
    #9
  10. 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" wrote:

    > 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" <> wrote in
    > message news:...
    > >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" wrote:
    > >
    > >> 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" <> wrote in
    > >> message news:...
    > >> > 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" wrote:
    > >> >
    > >> >> 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" <> wrote in
    > >> >> message news:...
    > >> >> > 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" wrote:
    > >> >> >
    > >> >> >> 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" <> wrote
    > >> >> >> in
    > >> >> >> message news:...
    > >> >> >> > 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.
    > >> >> >
    > >> >>
    > >> >>
    > >>
    > >>

    >
    >
     
    Helmut Herrmann, Aug 30, 2008
    #10
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Tina Fisher

    MySQL select problem

    Tina Fisher, Jul 1, 2003, in forum: Microsoft Access ADP SQL Server
    Replies:
    2
    Views:
    681
    BJ Freeman
    Jul 2, 2003
  2. Gerald Aichholzer

    problem with 1:0/1 relationship in detail form (problem updatingrecord in two tables)

    Gerald Aichholzer, Jan 7, 2005, in forum: Microsoft Access ADP SQL Server
    Replies:
    2
    Views:
    247
    Gerald Aichholzer
    Jan 8, 2005
  3. JoelB

    Resync fails to use primary key

    JoelB, Jul 28, 2008, in forum: Microsoft Access ADP SQL Server
    Replies:
    0
    Views:
    293
    JoelB
    Jul 28, 2008
  4. Paul Shapiro

    Re: Resync problem

    Paul Shapiro, Aug 28, 2008, in forum: Microsoft Access ADP SQL Server
    Replies:
    1
    Views:
    482
    fatema belle
    Nov 1, 2008
  5. Re: Trigger Causes Resync Problems

    , Dec 18, 2012, in forum: Microsoft Access ADP SQL Server
    Replies:
    0
    Views:
    3,055
Loading...

Share This Page