Discussion:
#deleted shows in inserted record subforms after SSMA upsize
(too old to reply)
Michael
2008-09-28 01:58:00 UTC
Permalink
After upsizing a working Access 2003 MDB database in Access 2007 using SSMA,
records inserted through a subform contain #deleted in the fields after the
update has completed. The source for the subform is an ODBC linked join
query implemented in an SQL Server 2005 view. I don't think there is a
fundamental problem with the view because the insert actually does insert the
data into the database. Fields in only one table of the join are being
updated.

A requery in the form AfterUpdate event gets rid of the #deleted values but
the recordset is repositioned to the first record in the set.

Is there a way to fix this? If I must use the requery, I don't want the
position of the recordset to change. Using a bookmark won't work with the
requery.

BTW the VBA code uses DAO 3.6.
Michael
2008-09-28 02:04:02 UTC
Permalink
One other item, the update is caused by executing:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

in response to the user clicking a command button on the subform, if this
has any significance.
unknown
2008-09-28 02:12:35 UTC
Permalink
The most often cause is a missing primary key on the table that has been
upsizing. There are other possibilities but I would check this first.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
in response to the user clicking a command button on the subform, if this
has any significance.
Michael
2008-09-29 02:50:01 UTC
Permalink
The source is not a table but a joined view. The view consists of 5 LEFT
OUTER JOIN's to 3 separate tables on 5 different fields. All tables involved
have primary keys and timestamps. The primary keys in each table are int
identity fields.

Any other suggestions?
Post by unknown
The most often cause is a missing primary key on the table that has been
upsizing. There are other possibilities but I would check this first.
--
Sylvain Lafontaine, ing.The
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
in response to the user clicking a command button on the subform, if this
has any significance.
Alex Dybenko
2008-09-29 04:56:20 UTC
Permalink
Hi,
do you have triggers for insert in these tables?
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
Post by Michael
The source is not a table but a joined view. The view consists of 5 LEFT
OUTER JOIN's to 3 separate tables on 5 different fields. All tables involved
have primary keys and timestamps. The primary keys in each table are int
identity fields.
Any other suggestions?
Post by unknown
The most often cause is a missing primary key on the table that has been
upsizing. There are other possibilities but I would check this first.
--
Sylvain Lafontaine, ing.The
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
in response to the user clicking a command button on the subform, if this
has any significance.
unknown
2008-09-29 05:29:30 UTC
Permalink
First, it's not all complex views that work under Access/JET that will do so
as an ODBC linked view. Don't expect any miracle here. For example, using
composite primary keys or linking the same table more than once are known to
be a source of trouble with JET. You might have to replace your linked view
with a standard Select query and even then, they are many chances that it
won't work either.

One possibility would be to add a virtual indexe; see:
http://support.microsoft.com/kb/q209123/

You should also take a look with the SQL-Server Profiler to see what's
Access is trying to do when it updates the backend. Maybe this will put
some light on this problem.

Finally, showing the relevant parts of the code (design of the tables, code
for the view, etc.) has never hurt anybody here.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
The source is not a table but a joined view. The view consists of 5 LEFT
OUTER JOIN's to 3 separate tables on 5 different fields. All tables involved
have primary keys and timestamps. The primary keys in each table are int
identity fields.
Any other suggestions?
Post by unknown
The most often cause is a missing primary key on the table that has been
upsizing. There are other possibilities but I would check this first.
--
Sylvain Lafontaine, ing.The
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
in response to the user clicking a command button on the subform, if this
has any significance.
Michael
2008-09-29 13:23:00 UTC
Permalink
Alex:

There are no triggers on the modified table nor on any of the other linked
tables.

Sylvain:

What do you mean by a "standard Select" query?

Michael
Post by unknown
First, it's not all complex views that work under Access/JET that will do so
as an ODBC linked view. Don't expect any miracle here. For example, using
composite primary keys or linking the same table more than once are known to
be a source of trouble with JET. You might have to replace your linked view
with a standard Select query and even then, they are many chances that it
won't work either.
http://support.microsoft.com/kb/q209123/
You should also take a look with the SQL-Server Profiler to see what's
Access is trying to do when it updates the backend. Maybe this will put
some light on this problem.
Finally, showing the relevant parts of the code (design of the tables, code
for the view, etc.) has never hurt anybody here.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
The source is not a table but a joined view. The view consists of 5 LEFT
OUTER JOIN's to 3 separate tables on 5 different fields. All tables involved
have primary keys and timestamps. The primary keys in each table are int
identity fields.
Any other suggestions?
Post by unknown
The most often cause is a missing primary key on the table that has been
upsizing. There are other possibilities but I would check this first.
--
Sylvain Lafontaine, ing.The
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
in response to the user clicking a command button on the subform, if this
has any significance.
unknown
2008-09-30 14:28:13 UTC
Permalink
BY using a standard Select query, I mean to replace the view with an
ordinary JET query (querydef?) against the linked tables.

In your case, you should really take a look with the SQL-Server Profiler to
see if you can see something about what Access is doing against the
SQL-Server.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
There are no triggers on the modified table nor on any of the other linked
tables.
What do you mean by a "standard Select" query?
Michael
Post by unknown
First, it's not all complex views that work under Access/JET that will do so
as an ODBC linked view. Don't expect any miracle here. For example, using
composite primary keys or linking the same table more than once are known to
be a source of trouble with JET. You might have to replace your linked view
with a standard Select query and even then, they are many chances that it
won't work either.
http://support.microsoft.com/kb/q209123/
You should also take a look with the SQL-Server Profiler to see what's
Access is trying to do when it updates the backend. Maybe this will put
some light on this problem.
Finally, showing the relevant parts of the code (design of the tables, code
for the view, etc.) has never hurt anybody here.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
The source is not a table but a joined view. The view consists of 5 LEFT
OUTER JOIN's to 3 separate tables on 5 different fields. All tables involved
have primary keys and timestamps. The primary keys in each table are int
identity fields.
Any other suggestions?
Post by unknown
The most often cause is a missing primary key on the table that has been
upsizing. There are other possibilities but I would check this first.
--
Sylvain Lafontaine, ing.The
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
in response to the user clicking a command button on the subform, if this
has any significance.
Michael
2008-09-30 14:43:00 UTC
Permalink
I replaced that type of query with the view because the query was too slow.

I'll look at the profiler.
Post by unknown
BY using a standard Select query, I mean to replace the view with an
ordinary JET query (querydef?) against the linked tables.
In your case, you should really take a look with the SQL-Server Profiler to
see if you can see something about what Access is doing against the
SQL-Server.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
There are no triggers on the modified table nor on any of the other linked
tables.
What do you mean by a "standard Select" query?
Michael
Post by unknown
First, it's not all complex views that work under Access/JET that will do so
as an ODBC linked view. Don't expect any miracle here. For example, using
composite primary keys or linking the same table more than once are known to
be a source of trouble with JET. You might have to replace your linked view
with a standard Select query and even then, they are many chances that it
won't work either.
http://support.microsoft.com/kb/q209123/
You should also take a look with the SQL-Server Profiler to see what's
Access is trying to do when it updates the backend. Maybe this will put
some light on this problem.
Finally, showing the relevant parts of the code (design of the tables, code
for the view, etc.) has never hurt anybody here.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
The source is not a table but a joined view. The view consists of 5 LEFT
OUTER JOIN's to 3 separate tables on 5 different fields. All tables involved
have primary keys and timestamps. The primary keys in each table are int
identity fields.
Any other suggestions?
Post by unknown
The most often cause is a missing primary key on the table that has been
upsizing. There are other possibilities but I would check this first.
--
Sylvain Lafontaine, ing.The
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
in response to the user clicking a command button on the subform, if this
has any significance.
Michael
2008-09-30 19:18:08 UTC
Permalink
I have determined what is causing the problem, but I do not know why. In the
Access form's BeforeUpdate event handler, I am setting two fields in the view
to NULL under certain conditions using syntax like:

Me.FieldName1.Value = Null
Me.FieldName2.Value = Null

I am still only modifying information in one of the tables in the joined
view. If I remove those statements, the #deleted does not appear and the
recordset is in the state I expect after the insertion. Unfortunately, it is
not as simple as removing those lines of code as they are needed to keep the
data in the correct state.
Alex Dybenko
2008-10-01 14:46:00 UTC
Permalink
Hi,
but these 2 fields allow nulls? Try to open this linked view in access and
enter such values manually
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
Post by Michael
I have determined what is causing the problem, but I do not know why. In the
Access form's BeforeUpdate event handler, I am setting two fields in the view
Me.FieldName1.Value = Null
Me.FieldName2.Value = Null
I am still only modifying information in one of the tables in the joined
view. If I remove those statements, the #deleted does not appear and the
recordset is in the state I expect after the insertion. Unfortunately, it is
not as simple as removing those lines of code as they are needed to keep the
data in the correct state.
Michael
2008-10-01 17:59:02 UTC
Permalink
They certainly do allow Nulls. As a matter of fact, the records are being
inserted with Null's present. Only in the Access form AfterUpdate event does
the current record contain the #deleted values.
Post by Alex Dybenko
Hi,
but these 2 fields allow nulls? Try to open this linked view in access and
enter such values manually
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
Post by Michael
I have determined what is causing the problem, but I do not know why. In the
Access form's BeforeUpdate event handler, I am setting two fields in the view
Me.FieldName1.Value = Null
Me.FieldName2.Value = Null
I am still only modifying information in one of the tables in the joined
view. If I remove those statements, the #deleted does not appear and the
recordset is in the state I expect after the insertion. Unfortunately, it is
not as simple as removing those lines of code as they are needed to keep the
data in the correct state.
Alex Dybenko
2008-10-02 15:46:55 UTC
Permalink
Hi,
I would then try two things:

- change logic, so null will be default, and you set Me.FieldName1.Value to
other values it required
- put this logic in insert/update trigger
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
Post by Michael
They certainly do allow Nulls. As a matter of fact, the records are being
inserted with Null's present. Only in the Access form AfterUpdate event does
the current record contain the #deleted values.
Post by Alex Dybenko
Hi,
but these 2 fields allow nulls? Try to open this linked view in access and
enter such values manually
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
Post by Michael
I have determined what is causing the problem, but I do not know why.
In
the
Access form's BeforeUpdate event handler, I am setting two fields in
the
view
Me.FieldName1.Value = Null
Me.FieldName2.Value = Null
I am still only modifying information in one of the tables in the joined
view. If I remove those statements, the #deleted does not appear and the
recordset is in the state I expect after the insertion. Unfortunately,
it
is
not as simple as removing those lines of code as they are needed to
keep
the
data in the correct state.
Michael
2008-10-02 16:13:03 UTC
Permalink
That's basically what I'm going to do.

But I still don't understand why this doesn't work as is.
Post by Alex Dybenko
Hi,
- change logic, so null will be default, and you set Me.FieldName1.Value to
other values it required
- put this logic in insert/update trigger
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
Post by Michael
They certainly do allow Nulls. As a matter of fact, the records are being
inserted with Null's present. Only in the Access form AfterUpdate event does
the current record contain the #deleted values.
Post by Alex Dybenko
Hi,
but these 2 fields allow nulls? Try to open this linked view in access and
enter such values manually
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
Post by Michael
I have determined what is causing the problem, but I do not know why.
In
the
Access form's BeforeUpdate event handler, I am setting two fields in
the
view
Me.FieldName1.Value = Null
Me.FieldName2.Value = Null
I am still only modifying information in one of the tables in the joined
view. If I remove those statements, the #deleted does not appear and the
recordset is in the state I expect after the insertion. Unfortunately,
it
is
not as simple as removing those lines of code as they are needed to
keep
the
data in the correct state.
Michael
2008-09-29 13:37:02 UTC
Permalink
Sylvain:

I created an index when I performed the link (as requested by Access) and
verified that it still exists. It is on a unique field.

Michael
Post by unknown
First, it's not all complex views that work under Access/JET that will do so
as an ODBC linked view. Don't expect any miracle here. For example, using
composite primary keys or linking the same table more than once are known to
be a source of trouble with JET. You might have to replace your linked view
with a standard Select query and even then, they are many chances that it
won't work either.
http://support.microsoft.com/kb/q209123/
You should also take a look with the SQL-Server Profiler to see what's
Access is trying to do when it updates the backend. Maybe this will put
some light on this problem.
Finally, showing the relevant parts of the code (design of the tables, code
for the view, etc.) has never hurt anybody here.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
The source is not a table but a joined view. The view consists of 5 LEFT
OUTER JOIN's to 3 separate tables on 5 different fields. All tables involved
have primary keys and timestamps. The primary keys in each table are int
identity fields.
Any other suggestions?
Post by unknown
The most often cause is a missing primary key on the table that has been
upsizing. There are other possibilities but I would check this first.
--
Sylvain Lafontaine, ing.The
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by Michael
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
in response to the user clicking a command button on the subform, if this
has any significance.
Loading...