SQL Upsizing Problem with Left Join Queries

[Joel Maxuel]

I am in the process of testing a database upsized to SQL Express 2005
(originally created through jet/local mdb). The data is comprised a couple
dozen tables: one-to-one, one-to-many and lookups. The "Admission" table
holds all patients. Three other tables (Domains, ChartReview, Discharge)
form the 1:1 and are populated as needed (so in the ChartReview form the
ChartReview table is connected to Admission via a left join - to the unique
identifier). Domains form and Discharge form connect to admission table in
the same manner. I have had no issue with this structure in Jet.

After upsizing, the first time I populate say ChartReview for a patient, go
to a subform and come back, I get the error message "The data has been
changed." I also later became aware of this error in my Domains form and
Discharge form. I was able to rule out bit/YesNo's as the Domains table did
not have any, and VBA code since I can recreate the error just by opening the
data entry query, fill out a field (ChartReview/Domain/Discharge), leave the
record for a few seconds and change that value or another of the same table.

I have tried inserting a timestamp field but it has seemed to have done
nothing - the value remained null. Not quite sure how to go about using this
when left joins are involved.

I would appreciate any suggestions. I'm using Access 2003 with MS SQL 2005
Express and the SQL 2000 ODBC driver (the backend db is in SQL 2000
compatability mode). An example of the data entry query follows. Thanks.

SELECT [Admission Table].ID, [Admission Table].[First Name], [Admission
Table].[Last Name], [Admission Table].[Transfer Date], [Admission
Table].[Location Transferred From], [Admission Table].[Status on Transfer],
[Domains Table].*
FROM [Admission Table] LEFT JOIN [Domains Table] ON [Admission Table].ID =
[Domains Table].[Assigned ID];

 

[Paul Shapiro]

Access is more lenient than SQL Server when updating data via a query with
joins. SQL Server will only update the table on the one side of the join,
not the many side. You should be able to find details on View Updates in SQL
Server's Books OnLine.

For cases like this I have either used:
1. a main form with subforms for the child rows, so each form/subform
represents a single table.
2. a stored procedure and vba code to do the update.

 

[Albert D. Kallal]

Does the child table also have a PK column?

You need both. I had a few tables + sub-forms miss-behaving, and the
discovery was that the child table did no have a PK.

So, you not only want to ensure that the child table has a timestamp, you
want to ensure it also have a PK, and that typically is just a autonumer ID
column in access. Or an integer field on sql server defined as a pk, and set
with a increment...

 

[Joel Maxuel]

Good afternoon Paul,
I haven't noticed any issue with my one-to-many relationships, just the
1:1's. It seems as though any default values in "new" records - in my case,
after entering the patient (into the Admission table - the master table) do
not get set until after the reocrd is saved causing the disturbance. With
the example of my yes/no box, the default "No" (which is part of my table
design in SQL) doesn't get set to No/0 until after I leave the record causing
a disparity when I return my focus. My "Domains" table has the same effect
even though there are no YesNo/bit controls.
My db layout is pretty well that of your first suggestion. I have a
different form for the different tables. However to help those with data
entry, some controls from other tables are included, most commonly the
patient's first & last name (taken from the Admission table) are in these
other forms. The linking piece is the unique identifier - autonumber for
Admission table and number for the rest.
Maybe I'm thinking too much of addressing the illness (delay in default
values, etc causing the disparity) and not the symptom (warning that the data
has changed). I have tried the "if me.dirty = true then me.dirty = false"
trick in the form's afterupdate (which I would imagine would fire after going
to another form or to a subform) but have had no luck.

Access is more lenient than SQL Server when updating data via a query with
joins. SQL Server will only update the table on the one side of the join,
not the many side. You should be able to find details on View Updates in SQL
Server's Books OnLine.

For cases like this I have either used:
1. a main form with subforms for the child rows, so each form/subform
represents a single table.
2. a stored procedure and vba code to do the update.

I am in the process of testing a database upsized to SQL Express 2005
(originally created through jet/local mdb). The data is comprised a
couple
dozen tables: one-to-one, one-to-many and lookups. The "Admission" table
holds all patients. Three other tables (Domains, ChartReview, Discharge)
form the 1:1 and are populated as needed (so in the ChartReview form the
ChartReview table is connected to Admission via a left join - to the
unique
identifier). Domains form and Discharge form connect to admission table
in
the same manner. I have had no issue with this structure in Jet.

After upsizing, the first time I populate say ChartReview for a patient,
go
to a subform and come back, I get the error message "The data has been
changed." I also later became aware of this error in my Domains form and
Discharge form. I was able to rule out bit/YesNo's as the Domains table
did
not have any, and VBA code since I can recreate the error just by opening
the
data entry query, fill out a field (ChartReview/Domain/Discharge), leave
the
record for a few seconds and change that value or another of the same
table.

I have tried inserting a timestamp field but it has seemed to have done
nothing - the value remained null. Not quite sure how to go about using
this
when left joins are involved.

I would appreciate any suggestions. I'm using Access 2003 with MS SQL
2005
Express and the SQL 2000 ODBC driver (the backend db is in SQL 2000
compatability mode). An example of the data entry query follows. Thanks.

SELECT [Admission Table].ID, [Admission Table].[First Name], [Admission
Table].[Last Name], [Admission Table].[Transfer Date], [Admission
Table].[Location Transferred From], [Admission Table].[Status on
Transfer],
[Domains Table].*
FROM [Admission Table] LEFT JOIN [Domains Table] ON [Admission Table].ID =
[Domains Table].[Assigned ID];

.

 

[Joel Maxuel]

Paul,

I had to test it again - sorry that I need to clarify. The default values
are set after you leave one of the controls (versus what I said earlier about
when you leave the record or form) so that makes sense. Now I really don't
know why this happens.

I think the real goal would be to supress the error message by making sure
the form has the most up-to-date copy (probably with VBA) of the record (once
it has been created) but still my attempts to do this have been
unsuccessful.

Good afternoon Paul,
I haven't noticed any issue with my one-to-many relationships, just the
1:1's. It seems as though any default values in "new" records - in my case,
after entering the patient (into the Admission table - the master table) do
not get set until after the reocrd is saved causing the disturbance. With
the example of my yes/no box, the default "No" (which is part of my table
design in SQL) doesn't get set to No/0 until after I leave the record causing
a disparity when I return my focus. My "Domains" table has the same effect
even though there are no YesNo/bit controls.
My db layout is pretty well that of your first suggestion. I have a
different form for the different tables. However to help those with data
entry, some controls from other tables are included, most commonly the
patient's first & last name (taken from the Admission table) are in these
other forms. The linking piece is the unique identifier - autonumber for
Admission table and number for the rest.
Maybe I'm thinking too much of addressing the illness (delay in default
values, etc causing the disparity) and not the symptom (warning that the data
has changed). I have tried the "if me.dirty = true then me.dirty = false"
trick in the form's afterupdate (which I would imagine would fire after going
to another form or to a subform) but have had no luck.

Access is more lenient than SQL Server when updating data via a query with
joins. SQL Server will only update the table on the one side of the join,
not the many side. You should be able to find details on View Updates in SQL
Server's Books OnLine.

For cases like this I have either used:
1. a main form with subforms for the child rows, so each form/subform
represents a single table.
2. a stored procedure and vba code to do the update.

I am in the process of testing a database upsized to SQL Express 2005
(originally created through jet/local mdb). The data is comprised a
couple
dozen tables: one-to-one, one-to-many and lookups. The "Admission" table
holds all patients. Three other tables (Domains, ChartReview, Discharge)
form the 1:1 and are populated as needed (so in the ChartReview form the
ChartReview table is connected to Admission via a left join - to the
unique
identifier). Domains form and Discharge form connect to admission table
in
the same manner. I have had no issue with this structure in Jet.

After upsizing, the first time I populate say ChartReview for a patient,
go
to a subform and come back, I get the error message "The data has been
changed." I also later became aware of this error in my Domains form and
Discharge form. I was able to rule out bit/YesNo's as the Domains table
did
not have any, and VBA code since I can recreate the error just by opening
the
data entry query, fill out a field (ChartReview/Domain/Discharge), leave
the
record for a few seconds and change that value or another of the same
table.

I have tried inserting a timestamp field but it has seemed to have done
nothing - the value remained null. Not quite sure how to go about using
this
when left joins are involved.

I would appreciate any suggestions. I'm using Access 2003 with MS SQL
2005
Express and the SQL 2000 ODBC driver (the backend db is in SQL 2000
compatability mode). An example of the data entry query follows. Thanks.

SELECT [Admission Table].ID, [Admission Table].[First Name], [Admission
Table].[Last Name], [Admission Table].[Transfer Date], [Admission
Table].[Location Transferred From], [Admission Table].[Status on
Transfer],
[Domains Table].*
FROM [Admission Table] LEFT JOIN [Domains Table] ON [Admission Table].ID =
[Domains Table].[Assigned ID];

.

 

[Paul Shapiro]

The warning that data has changed can be caused by triggers running on the
table. Adding "Set NoCount On" at the beginning of a trigger has often
stopped that warning. It stops SQL Server from returning the count of
affected records to Access, which Access sometimes interprets as an "extra"
update.

You can also try adding a timestamp field to your table. SQL Server has a
data type called TimeStamp, which has nothing to
do with date/time. It should have been called rowVersionNumber. The
timestamp is a row version number which is unique for the entire database.
Each time you update a row in any table that has a timestamp field, the
timestamp is given the next larger number of the previous max for the whole
db.

The reason adding a timestamp field to the table can help is that Access
tries to be careful on your behalf. When you update a row using optimistic
concurrency, Access wants to verify that the row has not changed since you
last read it. If you have a timestamp field in the table, Access usually
uses this to perform the concurrency check. This usually works well, since
only a genuine update would change the timestamp value. If you don't have a
timestamp field in the table, Access usually sends a sql statement to SQL
Server that compares the new and old values of every field in the table.
It's this comparison that can sometimes fail, even without an intervening
update between your reading the row and writing the row.

For the timestamp field to help, you need to include it in your select
statement. It should not be included in any manual update statement since
you
cannot update the value of a timestamp field. SQL Server maintains the value
as described above.

If your sql statement is selecting from more than one table, you can try
assigning the Unique Table property of the form. Access is less flexible
when the data is in SQL Server than with Access data. SQL Server data can
only update a single table at a time. Or try changing your form's
recordsource to be a single table, and use subforms for any related data.

I have found this to be a nasty issue. I have not seen a clear description
of how you can guarantee to avoid the problem. It's a bit of trial and
error, guided by the principals above. I seem to remember there are some MS
KB articles on the topic, so you could try searching there.

Paul,

I had to test it again - sorry that I need to clarify. The default values
are set after you leave one of the controls (versus what I said earlier
about
when you leave the record or form) so that makes sense. Now I really
don't
know why this happens.

I think the real goal would be to supress the error message by making sure
the form has the most up-to-date copy (probably with VBA) of the record
(once
it has been created) but still my attempts to do this have been
unsuccessful.

Good afternoon Paul,
I haven't noticed any issue with my one-to-many relationships, just the
1:1's. It seems as though any default values in "new" records - in my
case,
after entering the patient (into the Admission table - the master table)
do
not get set until after the reocrd is saved causing the disturbance.
With
the example of my yes/no box, the default "No" (which is part of my table
design in SQL) doesn't get set to No/0 until after I leave the record
causing
a disparity when I return my focus. My "Domains" table has the same
effect
even though there are no YesNo/bit controls.
My db layout is pretty well that of your first suggestion. I have a
different form for the different tables. However to help those with data
entry, some controls from other tables are included, most commonly the
patient's first & last name (taken from the Admission table) are in these
other forms. The linking piece is the unique identifier - autonumber for
Admission table and number for the rest.
Maybe I'm thinking too much of addressing the illness (delay in default
values, etc causing the disparity) and not the symptom (warning that the
data
has changed). I have tried the "if me.dirty = true then me.dirty =
false"
trick in the form's afterupdate (which I would imagine would fire after
going
to another form or to a subform) but have had no luck.

Access is more lenient than SQL Server when updating data via a query
with
joins. SQL Server will only update the table on the one side of the
join,
not the many side. You should be able to find details on View Updates
in SQL
Server's Books OnLine.

For cases like this I have either used:
1. a main form with subforms for the child rows, so each form/subform
represents a single table.
2. a stored procedure and vba code to do the update.

I am in the process of testing a database upsized to SQL Express 2005
(originally created through jet/local mdb). The data is comprised a
couple
dozen tables: one-to-one, one-to-many and lookups. The "Admission"
table
holds all patients. Three other tables (Domains, ChartReview,
Discharge)
form the 1:1 and are populated as needed (so in the ChartReview form
the
ChartReview table is connected to Admission via a left join - to the
unique
identifier). Domains form and Discharge form connect to admission
table
in
the same manner. I have had no issue with this structure in Jet.

After upsizing, the first time I populate say ChartReview for a
patient,
go
to a subform and come back, I get the error message "The data has
been
changed." I also later became aware of this error in my Domains form
and
Discharge form. I was able to rule out bit/YesNo's as the Domains
table
did
not have any, and VBA code since I can recreate the error just by
opening
the
data entry query, fill out a field (ChartReview/Domain/Discharge),
leave
the
record for a few seconds and change that value or another of the same
table.

I have tried inserting a timestamp field but it has seemed to have
done
nothing - the value remained null. Not quite sure how to go about
using
this
when left joins are involved.

I would appreciate any suggestions. I'm using Access 2003 with MS
SQL
2005
Express and the SQL 2000 ODBC driver (the backend db is in SQL 2000
compatability mode). An example of the data entry query follows.
Thanks.

SELECT [Admission Table].ID, [Admission Table].[First Name],
[Admission
Table].[Last Name], [Admission Table].[Transfer Date], [Admission
Table].[Location Transferred From], [Admission Table].[Status on
Transfer],
[Domains Table].*
FROM [Admission Table] LEFT JOIN [Domains Table] ON [Admission
Table].ID =
[Domains Table].[Assigned ID];


.

 

[Joel Maxuel]

Okay, so I have spent the greater part of the morning googling the
suggestions, and I did not find a working and straightforward example of the
"Set NoCount On" trigger. I see that it is something that is placed before
the SELECT statement in the (form's) query, but the only example I have seen
that didn't have a lot of other operations elements
(http://msdn.microsoft.com/en-us/library/ms189837.aspx) I get an "Invalid SQL
statement" as Access is anticipating SELECT first (vs GO).

I have also tried the timestamp trick. What I did was upsized my major
tables (those that are not lookup/related tables) to include the field (the
wizard calling itself upsize_ts) and then recreated my links in the frontend.
I am still getting the error message although honestly I do not think the
timestamp fields are working because I can add and edit records in any of
these tables and the upsize_ts field remains blank. Viewing the table's data
in Studio Express the value doesn't show anything specific either - just
"<binary>". Would I expect to see a binary result?

The warning that data has changed can be caused by triggers running on the
table. Adding "Set NoCount On" at the beginning of a trigger has often
stopped that warning. It stops SQL Server from returning the count of
affected records to Access, which Access sometimes interprets as an "extra"
update.

You can also try adding a timestamp field to your table. SQL Server has a
data type called TimeStamp, which has nothing to
do with date/time. It should have been called rowVersionNumber. The
timestamp is a row version number which is unique for the entire database.
Each time you update a row in any table that has a timestamp field, the
timestamp is given the next larger number of the previous max for the whole
db.

The reason adding a timestamp field to the table can help is that Access
tries to be careful on your behalf. When you update a row using optimistic
concurrency, Access wants to verify that the row has not changed since you
last read it. If you have a timestamp field in the table, Access usually
uses this to perform the concurrency check. This usually works well, since
only a genuine update would change the timestamp value. If you don't have a
timestamp field in the table, Access usually sends a sql statement to SQL
Server that compares the new and old values of every field in the table.
It's this comparison that can sometimes fail, even without an intervening
update between your reading the row and writing the row.

For the timestamp field to help, you need to include it in your select
statement. It should not be included in any manual update statement since
you
cannot update the value of a timestamp field. SQL Server maintains the value
as described above.

If your sql statement is selecting from more than one table, you can try
assigning the Unique Table property of the form. Access is less flexible
when the data is in SQL Server than with Access data. SQL Server data can
only update a single table at a time. Or try changing your form's
recordsource to be a single table, and use subforms for any related data.

I have found this to be a nasty issue. I have not seen a clear description
of how you can guarantee to avoid the problem. It's a bit of trial and
error, guided by the principals above. I seem to remember there are some MS
KB articles on the topic, so you could try searching there.

Paul,

I had to test it again - sorry that I need to clarify. The default values
are set after you leave one of the controls (versus what I said earlier
about
when you leave the record or form) so that makes sense. Now I really
don't
know why this happens.

I think the real goal would be to supress the error message by making sure
the form has the most up-to-date copy (probably with VBA) of the record
(once
it has been created) but still my attempts to do this have been
unsuccessful.

Good afternoon Paul,
I haven't noticed any issue with my one-to-many relationships, just the
1:1's. It seems as though any default values in "new" records - in my
case,
after entering the patient (into the Admission table - the master table)
do
not get set until after the reocrd is saved causing the disturbance.
With
the example of my yes/no box, the default "No" (which is part of my table
design in SQL) doesn't get set to No/0 until after I leave the record
causing
a disparity when I return my focus. My "Domains" table has the same
effect
even though there are no YesNo/bit controls.
My db layout is pretty well that of your first suggestion. I have a
different form for the different tables. However to help those with data
entry, some controls from other tables are included, most commonly the
patient's first & last name (taken from the Admission table) are in these
other forms. The linking piece is the unique identifier - autonumber for
Admission table and number for the rest.
Maybe I'm thinking too much of addressing the illness (delay in default
values, etc causing the disparity) and not the symptom (warning that the
data
has changed). I have tried the "if me.dirty = true then me.dirty =
false"
trick in the form's afterupdate (which I would imagine would fire after
going
to another form or to a subform) but have had no luck.

:

Access is more lenient than SQL Server when updating data via a query
with
joins. SQL Server will only update the table on the one side of the
join,
not the many side. You should be able to find details on View Updates
in SQL
Server's Books OnLine.

For cases like this I have either used:
1. a main form with subforms for the child rows, so each form/subform
represents a single table.
2. a stored procedure and vba code to do the update.

I am in the process of testing a database upsized to SQL Express 2005
(originally created through jet/local mdb). The data is comprised a
couple
dozen tables: one-to-one, one-to-many and lookups. The "Admission"
table
holds all patients. Three other tables (Domains, ChartReview,
Discharge)
form the 1:1 and are populated as needed (so in the ChartReview form
the
ChartReview table is connected to Admission via a left join - to the
unique
identifier). Domains form and Discharge form connect to admission
table
in
the same manner. I have had no issue with this structure in Jet.

After upsizing, the first time I populate say ChartReview for a
patient,
go
to a subform and come back, I get the error message "The data has
been
changed." I also later became aware of this error in my Domains form
and
Discharge form. I was able to rule out bit/YesNo's as the Domains
table
did
not have any, and VBA code since I can recreate the error just by
opening
the
data entry query, fill out a field (ChartReview/Domain/Discharge),
leave
the
record for a few seconds and change that value or another of the same
table.

I have tried inserting a timestamp field but it has seemed to have
done
nothing - the value remained null. Not quite sure how to go about
using
this
when left joins are involved.

I would appreciate any suggestions. I'm using Access 2003 with MS
SQL
2005
Express and the SQL 2000 ODBC driver (the backend db is in SQL 2000
compatability mode). An example of the data entry query follows.
Thanks.

SELECT [Admission Table].ID, [Admission Table].[First Name],
[Admission
Table].[Last Name], [Admission Table].[Transfer Date], [Admission
Table].[Location Transferred From], [Admission Table].[Status on
Transfer],
[Domains Table].*
FROM [Admission Table] LEFT JOIN [Domains Table] ON [Admission
Table].ID =
[Domains Table].[Assigned ID];


.

.

 

[Joel Maxuel]

Good afternoon Albert,

I just wanted to confirm that my 1:1 tables do have a primary key. The only
differences between them and my Admission table is that they are not
autonumbers (although still do not allow nulls and are indexed to the point
that no duplicates are allowed) and their name (Admssions use the title "ID",
the other tables use "Assigned ID" - it was to avoid ambigous names in the
data entry queries and forms).

Also, I did try the timestamp field but was never able to see the revision
numbers in the data I added and modiied which questioned me if the field ever
worked properly.

 

[Paul Shapiro]

Yes, timestamps are binary numbers. As long as the data type in SQL Server
is Timestamp, then it will have a value.

I don't know if SQL Express includes the SQL Profiler? If it does, you can
use Profiler to see exactly what commands Access is sending to SQL Server.
The update statement will either have a Where clause like:
upsize_ts = old_value
or a much longer where clause comparing every attribute value to the old
value that was retrieved when Access read the row.

The "Set NoCount On" statement only applies if you're using a stored
procedure to retrieve or update the data. It doesn't apply if you're using a
sql statement for the form's record source.

Okay, so I have spent the greater part of the morning googling the
suggestions, and I did not find a working and straightforward example of
the
"Set NoCount On" trigger. I see that it is something that is placed
before
the SELECT statement in the (form's) query, but the only example I have
seen
that didn't have a lot of other operations elements
(http://msdn.microsoft.com/en-us/library/ms189837.aspx) I get an "Invalid
SQL
statement" as Access is anticipating SELECT first (vs GO).

I have also tried the timestamp trick. What I did was upsized my major
tables (those that are not lookup/related tables) to include the field
(the
wizard calling itself upsize_ts) and then recreated my links in the
frontend.
I am still getting the error message although honestly I do not think the
timestamp fields are working because I can add and edit records in any of
these tables and the upsize_ts field remains blank. Viewing the table's
data
in Studio Express the value doesn't show anything specific either - just
"<binary>". Would I expect to see a binary result?

The warning that data has changed can be caused by triggers running on
the
table. Adding "Set NoCount On" at the beginning of a trigger has often
stopped that warning. It stops SQL Server from returning the count of
affected records to Access, which Access sometimes interprets as an
"extra"
update.

You can also try adding a timestamp field to your table. SQL Server has a
data type called TimeStamp, which has nothing to
do with date/time. It should have been called rowVersionNumber. The
timestamp is a row version number which is unique for the entire
database.
Each time you update a row in any table that has a timestamp field, the
timestamp is given the next larger number of the previous max for the
whole
db.

The reason adding a timestamp field to the table can help is that Access
tries to be careful on your behalf. When you update a row using
optimistic
concurrency, Access wants to verify that the row has not changed since
you
last read it. If you have a timestamp field in the table, Access usually
uses this to perform the concurrency check. This usually works well,
since
only a genuine update would change the timestamp value. If you don't have
a
timestamp field in the table, Access usually sends a sql statement to SQL
Server that compares the new and old values of every field in the table.
It's this comparison that can sometimes fail, even without an intervening
update between your reading the row and writing the row.

For the timestamp field to help, you need to include it in your select
statement. It should not be included in any manual update statement since
you
cannot update the value of a timestamp field. SQL Server maintains the
value
as described above.

If your sql statement is selecting from more than one table, you can try
assigning the Unique Table property of the form. Access is less flexible
when the data is in SQL Server than with Access data. SQL Server data can
only update a single table at a time. Or try changing your form's
recordsource to be a single table, and use subforms for any related data.

I have found this to be a nasty issue. I have not seen a clear
description
of how you can guarantee to avoid the problem. It's a bit of trial and
error, guided by the principals above. I seem to remember there are some
MS
KB articles on the topic, so you could try searching there.

Paul,

I had to test it again - sorry that I need to clarify. The default
values
are set after you leave one of the controls (versus what I said earlier
about
when you leave the record or form) so that makes sense. Now I really
don't
know why this happens.

I think the real goal would be to supress the error message by making
sure
the form has the most up-to-date copy (probably with VBA) of the record
(once
it has been created) but still my attempts to do this have been
unsuccessful.

:

Good afternoon Paul,
I haven't noticed any issue with my one-to-many relationships, just
the
1:1's. It seems as though any default values in "new" records - in my
case,
after entering the patient (into the Admission table - the master
table)
do
not get set until after the reocrd is saved causing the disturbance.
With
the example of my yes/no box, the default "No" (which is part of my
table
design in SQL) doesn't get set to No/0 until after I leave the record
causing
a disparity when I return my focus. My "Domains" table has the same
effect
even though there are no YesNo/bit controls.
My db layout is pretty well that of your first suggestion. I have a
different form for the different tables. However to help those with
data
entry, some controls from other tables are included, most commonly the
patient's first & last name (taken from the Admission table) are in
these
other forms. The linking piece is the unique identifier - autonumber
for
Admission table and number for the rest.
Maybe I'm thinking too much of addressing the illness (delay in
default
values, etc causing the disparity) and not the symptom (warning that
the
data
has changed). I have tried the "if me.dirty = true then me.dirty =
false"
trick in the form's afterupdate (which I would imagine would fire
after
going
to another form or to a subform) but have had no luck.

:

Access is more lenient than SQL Server when updating data via a
query
with
joins. SQL Server will only update the table on the one side of the
join,
not the many side. You should be able to find details on View
Updates
in SQL
Server's Books OnLine.

For cases like this I have either used:
1. a main form with subforms for the child rows, so each
form/subform
represents a single table.
2. a stored procedure and vba code to do the update.

message
I am in the process of testing a database upsized to SQL Express
2005
(originally created through jet/local mdb). The data is comprised
a
couple
dozen tables: one-to-one, one-to-many and lookups. The
"Admission"
table
holds all patients. Three other tables (Domains, ChartReview,
Discharge)
form the 1:1 and are populated as needed (so in the ChartReview
form
the
ChartReview table is connected to Admission via a left join - to
the
unique
identifier). Domains form and Discharge form connect to admission
table
in
the same manner. I have had no issue with this structure in Jet.

After upsizing, the first time I populate say ChartReview for a
patient,
go
to a subform and come back, I get the error message "The data has
been
changed." I also later became aware of this error in my Domains
form
and
Discharge form. I was able to rule out bit/YesNo's as the Domains
table
did
not have any, and VBA code since I can recreate the error just by
opening
the
data entry query, fill out a field (ChartReview/Domain/Discharge),
leave
the
record for a few seconds and change that value or another of the
same
table.

I have tried inserting a timestamp field but it has seemed to have
done
nothing - the value remained null. Not quite sure how to go about
using
this
when left joins are involved.

I would appreciate any suggestions. I'm using Access 2003 with MS
SQL
2005
Express and the SQL 2000 ODBC driver (the backend db is in SQL
2000
compatability mode). An example of the data entry query follows.
Thanks.

SELECT [Admission Table].ID, [Admission Table].[First Name],
[Admission
Table].[Last Name], [Admission Table].[Transfer Date], [Admission
Table].[Location Transferred From], [Admission Table].[Status on
Transfer],
[Domains Table].*
FROM [Admission Table] LEFT JOIN [Domains Table] ON [Admission
Table].ID =
[Domains Table].[Assigned ID];


.

.

 

[John W. Vinson]

I do not think the
timestamp fields are working because I can add and edit records in any of
these tables and the upsize_ts field remains blank. Viewing the table's data
in Studio Express the value doesn't show anything specific either - just
"<binary>". Would I expect to see a binary result?

Not in my experience. It's a big binary number and it's not displayed (that
I've ever seen) in any Number format.

 

[Armen Stein]

On Fri, 13 Nov 2009 07:07:36 -0500, "Paul Shapiro"

Hi Paul,

You can also try adding a timestamp field to your table. SQL Server has a
data type called TimeStamp, which has nothing to
do with date/time. It should have been called rowVersionNumber.

It is called RowVersion starting with SQL 2005. The old term
TimeStamp is still supported though.

The reason adding a timestamp field to the table can help is that Access
tries to be careful on your behalf. When you update a row using optimistic
concurrency, Access wants to verify that the row has not changed since you
last read it. If you have a timestamp field in the table, Access usually
uses this to perform the concurrency check. This usually works well, since
only a genuine update would change the timestamp value. If you don't have a
timestamp field in the table, Access usually sends a sql statement to SQL
Server that compares the new and old values of every field in the table.
It's this comparison that can sometimes fail, even without an intervening
update between your reading the row and writing the row.

However, adding RowVersion does force full-row concurrency checking,
while leaving it out allows you to do partial-row conconcurrency.
Thanks to Sylvain Lafontaine for educating me on this! I have a few
slides on using RowVersion in my presentation "Best of Both Worlds" at
www.JStreetTech.com/Downloads.

For the timestamp field to help, you need to include it in your select
statement.

No, actually you don't. If you merely have a RowVersion field in the
table, concurrency checking in Access will use it instead of comparing
all the fields in the recordset. You don't need to include it in the
recordset of the form or in the update query.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Joel Maxuel]

Good afternoon Paul,

SQL Express does not have an SQL Profiler, but I was able to download one
off GoogleCode (by AnjLab). I'm hoping the layout is similar enough to MS's
version.

So the update statements do include values for the RowVersion (but not
INSERT), however the WHERE clause is picking up other fields. I was able to
pull away a few other SQL statements and placed them below.

---

Admission Table - demonstrates that RowVersion is saving values:

exec sp_executesql N'UPDATE "dbo"."Admission Table" SET
"Gender"=@P1,"Marital Status"=@P2 WHERE "ID" = @P3 AND "upsize_ts" =
@P4',N'@P1 nvarchar(8),@P2 nvarchar(25),@P3 int,@P4
binary(8)',N'Male',N'Divorced',30,0x000000000000177A

The first save - this creates the record on the Weekly Review side based on
the ID on the Admission Table side (interesting that RowVersion doesn't
populate here)

exec sp_executesql N'INSERT INTO "dbo"."Weekly Review Table" ("Assigned
ID","Case Conference") VALUES (@P1,@P2)',N'@P1 int,@P2 smallint',30,120

Access retrieves the updated record (for the weekly review side):

exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "Assigned ID","Case
Summary","Case
Conference","ReferralDate","Destination","ReadinessStatus","upsize_ts" FROM
"dbo"."Weekly Review Table" WHERE "Assigned ID" = @P1',30

I think this is the entire text for the code I have recieved the error ("The
data has been changed.") for:

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "Assigned ID","Case
Summary","Case
Conference","ReferralDate","Destination","ReadinessStatus","upsize_ts" FROM
"dbo"."Weekly Review Table" WHERE "Assigned ID" = @P1',30
select @p1

I think after closing the dialog box and making the change again, the same
code above sends out but line 2 is different:

set @p1=26

Finally, the successful save:

exec sp_executesql N'UPDATE "dbo"."Weekly Review Table" SET "Case
Conference"=@P1,"ReadinessStatus"=@P2 WHERE "Assigned ID" = @P3 AND
"upsize_ts" = @P4',N'@P1 smallint,@P2 nvarchar(7),@P3 int,@P4
binary(8)',60,N'Red',30,0x000000000000177C

 

[Paul Shapiro]

Hi Joel,

I didn't understand which statement is generating the Write Conflict error.
Everything looked ok to me. The RowVersion cannot be updated in a sql update
or insert statement. It's a system-maintained value. If you repeated the
same update statement, even with the same data values, you would see the
RowVersion attribute automatically incrementing with each update.

I think you are saying that this statement causes the "data has changed"
error, but that doesn't make sense to me since it's only a retrieval. Does
this statement fail to retrieve any data, so Access is complaining that
after the update the row can't be found? If so, is 30 the correct value for
the assignedID? It looks correct based on the Insert statement you listed,
but that would be something to verify.

 

[Joel Maxuel]

Paul,

I may have wandered away from the path - so capturing the SQL communications
showed that the WHERE clause does not include just the timestamp/rowversion
field as hoped. (Or would any changed fields in the record be included in
the WHERE as well?) If the timestamp/rowversion is indeed working correctly
and yet I still get the error, I think this means the row version idea is a
dead end.

I went back into the SQL Profiler and reran the test, this time with a one
minute pause between every action. The code I had posted below fired almost
immediately after the INSERT query. Also, the time frame that I had the
error only posted "exec sp_execute 9,30" and "SET TEXTSIZE" messages, nothing
offending. I wouldn't expect any INSERT or UPDATE messages anyway as the
error fires on dirty (just when I type the first character). Given my
conclusion in my first paragraph, I think it's time to go another direction.
You have mentioned the "Set NoCount On" trigger before - how would I go about
using it?

 

[Paul Shapiro]

Joel,

The 'Set NoCount On' is a transact-sql command that could be added as the
first line of any stored procedure you're using for data updates. But you're
using dynamic sql statements, not stored procedures, so it doesn't apply
there. If the table you're working with has any triggers, then the same
command could be added at the beginning of the trigger. All it does is tell
SQL Server NOT to return the count of rows affected by the sql commands.
Sometimes Access, or other client applications, gets confused by the extra
message with the affected row count and misinterprets that message as the
requested result set, leading to odd error messages usually indicating a
write error or a failure to read the row that was just updated. I don't
think it applies here.

Unless there's something strange going on in your VBA code, this seems to be
a lot more complicated than it should be. Is there any code executing when
you type the first character that you mentioned below? Is this a character
in a text box or a combo or list box? If it's a text box, I don't think
anything would be sent to SQL Server just from typing a character unless you
have code that is executing the sql statement. If it's a combo box or a list
box, then Access could be filling the rows by executing the combo box's row
source query.

Maybe it's worth trying some very simple sql for the record source, like
just the table name, and disable any VBA code, just to see that simple
things work correctly. I've forgotten- is this running in an adp or an mdb?
If it's an adp, make sure the record source qualifier is correct (usually
dbo, but doesn't have to be- this is the schema containing the table in
question). Sometimes the Unique Table needs to be specified, but that's
usually only applicable if the form's record source is a query with a join.
Finally, the Resync Command (and Input Parameters if applicable) sometimes
helps and sometimes causes problems, so you could try that either way. BUT,
if the record source is just the table name, I don't think any of these
settings should be necessary.

And to make sure I'm not talking about the wrong things, what's the exact
error message text?

 

[Joel Maxuel]

Paul,

I have been getting the same error message when VBA (or even forms for that
matter) don't enter the situation. I made a copy of the front end that had
all the VBA removed for that form (and it's subform) and had the same result.


My current test is (i.e for patient #30 - which is my primary key) to remove
the record on the 1:1 related side (making sure that 30 is still on the
Admission Table side). I then open the same query that the form I discovered
the problem with uses. From there, I enter a field on the Weekly
Review/related side, switch to abother record (if I was in the form, I could
just switch to the subform) momentarily, just so the record saves. The final
piece is going back to the same record (30) and entering or changing _any_
field. I get the error (next paragraph), but that is the only time it will
show per patient, per related (1:1) table:

The data has been changed.
Another user edited this record and saved the changes before you attempted
to save your changes.
Re-edit the record.

A few questions to tidy up - I use combo boxes with lookups, but usually at
the form level. My data entry query mentioned in the above test are all text
boxes. I have very little experience with ADP, so my front-end is in MDB
format (version 2000, although my version of Access is 2003). The tail end
of your message - is this still in perspective of ADP? Because as you can
see above my record source is a query with a join (my first post has the
exact SQL statement) - yet specifying a Unique Table and using the Resync
command is something unfamiliar for me (looks like I have some homework).

The other suggestion brought up was having a single table record source. I
could go on about the number of input fields when I inheirited the database
(which would be up to 250 now), but the point being, there was a performance
gain when the table was split into functional units: Admission (aka Master),
Domains (recovery review), Chart Review, Discharge, and although I didn't see
the message, a single table record would have easily broken the 2K limit
(important in Jet, but probably irrelevant in SQL). Anyways, using the
single table method for each part seems either that there would be domain
lookups for retrieving the Admission/master data, i.e. First and Last Name,
as well as synchronizing the primary key's (which would be bad as you are now
forced to have only the options available in that table to add/change versus
being able to pick and choose - in this case my utilization manager will have
to go to the domains form to set the target discharge date because the
occupational therapists use that field as well). The other option was using
a subform for the related side - this could work in most places (except my
domains form, as there is a nested subform series already with calculated
fields which I played with and could not go in three layers - deeper than
Forms![Domains Form]![Domain Goals].form!intStep). Finally, I'm a bit
adverse to changing the back end much at this point as it is currently being
piloted and using live data (using Jet) - I wouldn't want to interrupt their
access by doing something drastic.

Anyways, In hope this answers your questions without information overload.

 

[Joel Maxuel]

Paul,

So it looks like the Unique Table/Resync is something for ADP, not MDB.

I was rethinking the single table recordset, and I think I can get away with
having the 1:1 data in a subform so each form is either Admission Table or a
query with the minimal fields needed based off admission. My 1:many subforms
will link to same, so I do not have to deal with an extra level. The trick
will be combining my Domain Table and my Weely Review Table, but if I set up
an append query right, combining the data shouldn't be too much of a pain.

Thanks for your assistance. But if you have come up a cooler solution,
please let me know, beacuse I'm still all ears.

 

[Joel Maxuel]

Paul,

Thanks for your help so far. I have turned a couple data entry forms (right
when I'm writing this I forgot to change the third) and made them their own
subform (the record set of the parent form is a few records from the
Admissions table). Honestly less stuff broke than I expected <grins>.

Everything is coming back together, but there is one caveat I still have to
address. Given the 1:1 relationship subform, it is possible to stumble onto
a new record in the subform (but with my relationships a second cannot be
saved). I stripped the properties down to a single form with no record
selectors, navigation, borders - basically it looks the same as before.
However, there are still two ways to get to a second record (which means I'll
get calls from others complaing their data disappeared and/or their entry
cannot save):
1) The scroll wheel - this is the most common, some have mouse drivers
that prevent this from happening, but is not a guarantee.
2) Page Up/Page Down - less often, but still noteworthy

Optimally, I would like a VBA event that if the user went to a second record
that it will fire (at the first opportunity) and return to the first.
However I cannot concieve one that will take into account (allowing) 1st time
data enter or editing the existing. It will be a waste of resources to have
an event that fires at every opportunity either. Also, because I would like
to keep the database front end portable, I do not imagine the mousewheel
disable DLL a good option.

Do you have any code suggestions?

 

[Paul Shapiro]

For 1:1 relationships you could use a single form, rather than a main form
and subform. I don't know if Access will automatically update both rows, or
insert the matching row in the child table, but you can work around things
like that by adding code to the correct events.

You could also use code to prevent adding a new record in the subform if one
already exists. Set the subform's Allow Additions property to false, so the
subform can't add any records. If you have a control on the main form whose
value dictates whether the 1:1 child record should exist, you can write code
in the AfterUpdate event of that control to add a new row to the child
table. If the child should always exist, you can write the code to add the
child record in the main form's AfterInsert event.

 

[Joel Maxuel]

Good morning Paul,

Thanks for all your help. I was able to dummy-proof the subform - I will
post the procedure below for anyone else who may be looking for a similar
solution:

---

Using your Allow Additions property with the AfterUpdate event, I created
the VBA to prevent a freshly saved record from going to a second:

Private Sub Form_AfterUpdate()
Me.AllowAdditions = False
End Sub

I then extrapolated for when a record is opened. To do this, I created a
control on the subform side called IDTest, which is the tables PK and tested
if it had data (I could not use the master/child link control as it always
had data) - if it did, then it prevented any data entry, if it was null, then
an addition was allowed:

Private Sub Form_Current()
Me.SubForm_Weekly_Top.Form.AllowAdditions = True
If IsNull(Me.SubForm_Weekly_Top.Form.[IDTest]) Then
' MsgBox "Blank"
Else
' MsgBox Me.SubForm_Weekly_Top.Form.[IDTest]
Me.SubForm_Weekly_Top.Form.AllowAdditions = False
End If
End Sub

If you are about to wonder why the if structure did nothing when true, it
was I had to move the AllowAdditions object above, as even though my debug
would fire when true, the subform would not display at all, until you change
the patient to one that does have a (i.e weekly review) record.

This has tested out well in Jet, as well as MSSQL (ODBC).