"Data Has Been Changed" error with MSSQL 2005 backend using left j

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." Any further edits for that patient in this form did not repeat
that warning. 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];

Please disregard - my original post ("SQL Upsizing Problem with Left Join
Queries") did not show up for me until now.

"Joel Maxuel" wrote:

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." Any further edits for that patient in this form did not repeat
that warning. 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];