SEO

We're using SQL Server 7 with an Access 2000 MDB as a front end with ODBC
linked tables. I recently created a new set of tables for the app, and users
are complaining that unsaved data is being lost when they move to a new
record. This seems to be the case when there are multiple users. When there
is a single user using it, we don't seem to have that problem.

It seems that we had this problem when we first converted from an MDB back
end to a SQL 7 back end, years ago, but we haven't had this problem in a
while. These are the first "entirely new" tables created in several years,
and we seem to be having that problem again.

Is this something with SQL 7 when it's dealing with new tables? Any ideas on
what to do?

Thanks!

Neil

Another note on this issue. If I go into an existing record, type data in a
field, save the record (using Shift+Enter), then immediately type more data
in the same field, I get the message, "The data has been changed. Another
user edited this record and saved the changes before you attempted to save
your changes." Once the message is cleared, the steps of
typing/saving/typing can be performed without any errors. But when you first
go to a record and perform those steps, the error always comes up.

This happens both on the network version of the database, as well as in my
personal copy (using MSDE), so I know it's not a multi-user issue, but some
kind of a glitch. It seems that it might be related to the below message re.
losing data, so I posted it here.

Any ideas?

Thanks!

Neil


"Neil Ginsberg" <nrg@nrgconsult.com> wrote in message
news:yefFd.5637$Ii4.2334@newsread3.news.pas.earthl ink.net...
> We're using SQL Server 7 with an Access 2000 MDB as a front end with ODBC
> linked tables. I recently created a new set of tables for the app, and
> users are complaining that unsaved data is being lost when they move to a
> new record. This seems to be the case when there are multiple users. When
> there is a single user using it, we don't seem to have that problem.
>
> It seems that we had this problem when we first converted from an MDB back
> end to a SQL 7 back end, years ago, but we haven't had this problem in a
> while. These are the first "entirely new" tables created in several years,
> and we seem to be having that problem again.
>
> Is this something with SQL 7 when it's dealing with new tables? Any ideas
> on what to do?
>
> Thanks!
>
> Neil
>

Neil Ginsberg (nrg@nrgconsult.com) writes:
> Another note on this issue. If I go into an existing record, type data
> in a field, save the record (using Shift+Enter), then immediately type
> more data in the same field, I get the message, "The data has been
> changed. Another user edited this record and saved the changes before
> you attempted to save your changes." Once the message is cleared, the
> steps of typing/saving/typing can be performed without any errors. But
> when you first go to a record and perform those steps, the error always
> comes up.
>
> This happens both on the network version of the database, as well as in
> my personal copy (using MSDE), so I know it's not a multi-user issue,
> but some kind of a glitch. It seems that it might be related to the
> below message re. losing data, so I posted it here.

Both this problem and the other about losing data, sounds like there are
problem in the application. Since I don't know Access, I can't really
say exactly what.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

There isn't anything odd that SQL Server does with new
tables. This issue is more likely due to using ODBC or some
Access specific configuration, setting.
The "record has been changed" error you posted can happen
with a few different scenarios with ODBC linked tables in
Access. Basically, ODBC will use a timestamp to determine if
the record has been updated. If there is no timestamp
column, it compares all column values to see if the record
has been updated.
Some situations can cause Access to become "confused" as to
whether the record has been updated - such as having float
data types. Try adding a timestamp column to the SQL table
and see if the fixes the problem. There should be some
Access knowledge base articles on this subject that you may
want to search for.

-Sue

On Wed, 12 Jan 2005 20:28:22 GMT, "Neil Ginsberg"
<nrg@nrgconsult.com> wrote:

>Another note on this issue. If I go into an existing record, type data in a
>field, save the record (using Shift+Enter), then immediately type more data
>in the same field, I get the message, "The data has been changed. Another
>user edited this record and saved the changes before you attempted to save
>your changes." Once the message is cleared, the steps of
>typing/saving/typing can be performed without any errors. But when you first
>go to a record and perform those steps, the error always comes up.
>
>This happens both on the network version of the database, as well as in my
>personal copy (using MSDE), so I know it's not a multi-user issue, but some
>kind of a glitch. It seems that it might be related to the below message re.
>losing data, so I posted it here.
>
>Any ideas?
>
>Thanks!
>
>Neil
>
>
>"Neil Ginsberg" <nrg@nrgconsult.com> wrote in message
>news:yefFd.5637$Ii4.2334@newsread3.news.pas.earth link.net...
>> We're using SQL Server 7 with an Access 2000 MDB as a front end with ODBC
>> linked tables. I recently created a new set of tables for the app, and
>> users are complaining that unsaved data is being lost when they move to a
>> new record. This seems to be the case when there are multiple users. When
>> there is a single user using it, we don't seem to have that problem.
>>
>> It seems that we had this problem when we first converted from an MDB back
>> end to a SQL 7 back end, years ago, but we haven't had this problem in a
>> while. These are the first "entirely new" tables created in several years,
>> and we seem to be having that problem again.
>>
>> Is this something with SQL 7 when it's dealing with new tables? Any ideas
>> on what to do?
>>
>> Thanks!
>>
>> Neil
>>
>

I'm using a bound Access form, the same as I am with the other forms that
have been working for years. There is no code to create/modify/update
records; it's all being handled by the form, and it's based on a single
table. Nothing complex here. There has to be something on the SQL side, or
with the ODBC driver. But, again, there's nothing different here than with
other forms in the application with the same database, which have worked for
years.

Neil


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns95DD40B1961BYazorman@127.0.0.1...
> Neil Ginsberg (nrg@nrgconsult.com) writes:
>> Another note on this issue. If I go into an existing record, type data
>> in a field, save the record (using Shift+Enter), then immediately type
>> more data in the same field, I get the message, "The data has been
>> changed. Another user edited this record and saved the changes before
>> you attempted to save your changes." Once the message is cleared, the
>> steps of typing/saving/typing can be performed without any errors. But
>> when you first go to a record and perform those steps, the error always
>> comes up.
>>
>> This happens both on the network version of the database, as well as in
>> my personal copy (using MSDE), so I know it's not a multi-user issue,
>> but some kind of a glitch. It seems that it might be related to the
>> below message re. losing data, so I posted it here.
>
> Both this problem and the other about losing data, sounds like there are
> problem in the application. Since I don't know Access, I can't really
> say exactly what.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

You know, that might be it. I did forget to put the timestamp column in (the
other tables were converted from an MDB file, and the conversion utility
automatically put the timestamp column in). That might just be it.

Thanks,

Neil

"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:7qdbu0diucqfv72eove4jglip8u5cghrrj@4ax.com...
> There isn't anything odd that SQL Server does with new
> tables. This issue is more likely due to using ODBC or some
> Access specific configuration, setting.
> The "record has been changed" error you posted can happen
> with a few different scenarios with ODBC linked tables in
> Access. Basically, ODBC will use a timestamp to determine if
> the record has been updated. If there is no timestamp
> column, it compares all column values to see if the record
> has been updated.
> Some situations can cause Access to become "confused" as to
> whether the record has been updated - such as having float
> data types. Try adding a timestamp column to the SQL table
> and see if the fixes the problem. There should be some
> Access knowledge base articles on this subject that you may
> want to search for.
>
> -Sue
>
> On Wed, 12 Jan 2005 20:28:22 GMT, "Neil Ginsberg"
> <nrg@nrgconsult.com> wrote:
>
>>Another note on this issue. If I go into an existing record, type data in
>>a
>>field, save the record (using Shift+Enter), then immediately type more
>>data
>>in the same field, I get the message, "The data has been changed. Another
>>user edited this record and saved the changes before you attempted to save
>>your changes." Once the message is cleared, the steps of
>>typing/saving/typing can be performed without any errors. But when you
>>first
>>go to a record and perform those steps, the error always comes up.
>>
>>This happens both on the network version of the database, as well as in my
>>personal copy (using MSDE), so I know it's not a multi-user issue, but
>>some
>>kind of a glitch. It seems that it might be related to the below message
>>re.
>>losing data, so I posted it here.
>>
>>Any ideas?
>>
>>Thanks!
>>
>>Neil
>>
>>
>>"Neil Ginsberg" <nrg@nrgconsult.com> wrote in message
>>news:yefFd.5637$Ii4.2334@newsread3.news.pas.eart hlink.net...
>>> We're using SQL Server 7 with an Access 2000 MDB as a front end with
>>> ODBC
>>> linked tables. I recently created a new set of tables for the app, and
>>> users are complaining that unsaved data is being lost when they move to
>>> a
>>> new record. This seems to be the case when there are multiple users.
>>> When
>>> there is a single user using it, we don't seem to have that problem.
>>>
>>> It seems that we had this problem when we first converted from an MDB
>>> back
>>> end to a SQL 7 back end, years ago, but we haven't had this problem in a
>>> while. These are the first "entirely new" tables created in several
>>> years,
>>> and we seem to be having that problem again.
>>>
>>> Is this something with SQL 7 when it's dealing with new tables? Any
>>> ideas
>>> on what to do?
>>>
>>> Thanks!
>>>
>>> Neil
>>>
>>
>

Well, that took care of the issue with the "record has been changed" error
message. And, though the other error was intermittent so I can't directly
test it, I'm sure having the timestamp field will take care of that other
one, as well. Thanks again for your help!

Neil


"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:7qdbu0diucqfv72eove4jglip8u5cghrrj@4ax.com...
> There isn't anything odd that SQL Server does with new
> tables. This issue is more likely due to using ODBC or some
> Access specific configuration, setting.
> The "record has been changed" error you posted can happen
> with a few different scenarios with ODBC linked tables in
> Access. Basically, ODBC will use a timestamp to determine if
> the record has been updated. If there is no timestamp
> column, it compares all column values to see if the record
> has been updated.
> Some situations can cause Access to become "confused" as to
> whether the record has been updated - such as having float
> data types. Try adding a timestamp column to the SQL table
> and see if the fixes the problem. There should be some
> Access knowledge base articles on this subject that you may
> want to search for.
>
> -Sue
>
> On Wed, 12 Jan 2005 20:28:22 GMT, "Neil Ginsberg"
> <nrg@nrgconsult.com> wrote:
>
>>Another note on this issue. If I go into an existing record, type data in
>>a
>>field, save the record (using Shift+Enter), then immediately type more
>>data
>>in the same field, I get the message, "The data has been changed. Another
>>user edited this record and saved the changes before you attempted to save
>>your changes." Once the message is cleared, the steps of
>>typing/saving/typing can be performed without any errors. But when you
>>first
>>go to a record and perform those steps, the error always comes up.
>>
>>This happens both on the network version of the database, as well as in my
>>personal copy (using MSDE), so I know it's not a multi-user issue, but
>>some
>>kind of a glitch. It seems that it might be related to the below message
>>re.
>>losing data, so I posted it here.
>>
>>Any ideas?
>>
>>Thanks!
>>
>>Neil
>>
>>
>>"Neil Ginsberg" <nrg@nrgconsult.com> wrote in message
>>news:yefFd.5637$Ii4.2334@newsread3.news.pas.eart hlink.net...
>>> We're using SQL Server 7 with an Access 2000 MDB as a front end with
>>> ODBC
>>> linked tables. I recently created a new set of tables for the app, and
>>> users are complaining that unsaved data is being lost when they move to
>>> a
>>> new record. This seems to be the case when there are multiple users.
>>> When
>>> there is a single user using it, we don't seem to have that problem.
>>>
>>> It seems that we had this problem when we first converted from an MDB
>>> back
>>> end to a SQL 7 back end, years ago, but we haven't had this problem in a
>>> while. These are the first "entirely new" tables created in several
>>> years,
>>> and we seem to be having that problem again.
>>>
>>> Is this something with SQL 7 when it's dealing with new tables? Any
>>> ideas
>>> on what to do?
>>>
>>> Thanks!
>>>
>>> Neil
>>>
>>
>

Neil Ginsberg wrote:
> We're using SQL Server 7 with an Access 2000 MDB as a front end with ODBC
> linked tables. I recently created a new set of tables for the app, and users
> are complaining that unsaved data is being lost when they move to a new
> record. This seems to be the case when there are multiple users. When there
> is a single user using it, we don't seem to have that problem.
>
> It seems that we had this problem when we first converted from an MDB back
> end to a SQL 7 back end, years ago, but we haven't had this problem in a
> while. These are the first "entirely new" tables created in several years,
> and we seem to be having that problem again.
>
> Is this something with SQL 7 when it's dealing with new tables? Any ideas on
> what to do?

Try putting a timestamp column onto the new SQL tables, then re-attach.
Your existing tables probably already had them (created by upsizing wizard).

--
This sig left intentionally blank

Neil Ginsberg wrote:
> Well, that took care of the issue with the "record has been changed" error
> message. And, though the other error was intermittent so I can't directly
> test it, I'm sure having the timestamp field will take care of that other
> one, as well. Thanks again for your help!

D'OH! that'll teach me to read the whole thread before replying :-)

--
This sig left intentionally blank

Well, I spoke too soon. The problem with the "data has changed" error
message has been resolved by putting the timestamp field in the tables. But
I just found out that the users are still having problems with losing data.
I just spoke with one of them, and she said that she's doing an explicit
save (by selecting Save Record from the Records menu) before leaving a
record, and she's still losing data. (She moves to the previous record with
the navigation buttons, then back to the record that she just edited, and
the data is gone.) The problem is intermittent, and happened to one user
twice in an hour in which she had entered about 20 records.

Again, there are three subforms in the middle of the main form. All main
form fields above the subform, as well as the subform data itself, are being
saved. It's only the fields below the subform that are being lost. So,
apparently, moving in and out of the subforms is working fine to save the
data; but doing a Save Record is not. Also, even without the explicit Save
Record, the users are clicking a New button, which performs a
docmd.runcommand accmdsaverecord, and then goes to the new record. Even
without the accmdsaverecord, the data should be saved; but certainly with
it.

The three fields below the subforms that are losing data are two varchar
(255) fields, and one text field. (The text field is last.)

So this is peculiar. Any other ideas regarding this would be appreciated.

Thanks,

Neil


"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:7qdbu0diucqfv72eove4jglip8u5cghrrj@4ax.com...
> There isn't anything odd that SQL Server does with new
> tables. This issue is more likely due to using ODBC or some
> Access specific configuration, setting.
> The "record has been changed" error you posted can happen
> with a few different scenarios with ODBC linked tables in
> Access. Basically, ODBC will use a timestamp to determine if
> the record has been updated. If there is no timestamp
> column, it compares all column values to see if the record
> has been updated.
> Some situations can cause Access to become "confused" as to
> whether the record has been updated - such as having float
> data types. Try adding a timestamp column to the SQL table
> and see if the fixes the problem. There should be some
> Access knowledge base articles on this subject that you may
> want to search for.
>
> -Sue
>
> On Wed, 12 Jan 2005 20:28:22 GMT, "Neil Ginsberg"
> <nrg@nrgconsult.com> wrote:
>
>>Another note on this issue. If I go into an existing record, type data in
>>a
>>field, save the record (using Shift+Enter), then immediately type more
>>data
>>in the same field, I get the message, "The data has been changed. Another
>>user edited this record and saved the changes before you attempted to save
>>your changes." Once the message is cleared, the steps of
>>typing/saving/typing can be performed without any errors. But when you
>>first
>>go to a record and perform those steps, the error always comes up.
>>
>>This happens both on the network version of the database, as well as in my
>>personal copy (using MSDE), so I know it's not a multi-user issue, but
>>some
>>kind of a glitch. It seems that it might be related to the below message
>>re.
>>losing data, so I posted it here.
>>
>>Any ideas?
>>
>>Thanks!
>>
>>Neil
>>
>>
>>"Neil Ginsberg" <nrg@nrgconsult.com> wrote in message
>>news:yefFd.5637$Ii4.2334@newsread3.news.pas.eart hlink.net...
>>> We're using SQL Server 7 with an Access 2000 MDB as a front end with
>>> ODBC
>>> linked tables. I recently created a new set of tables for the app, and
>>> users are complaining that unsaved data is being lost when they move to
>>> a
>>> new record. This seems to be the case when there are multiple users.
>>> When
>>> there is a single user using it, we don't seem to have that problem.
>>>
>>> It seems that we had this problem when we first converted from an MDB
>>> back
>>> end to a SQL 7 back end, years ago, but we haven't had this problem in a
>>> while. These are the first "entirely new" tables created in several
>>> years,
>>> and we seem to be having that problem again.
>>>
>>> Is this something with SQL 7 when it's dealing with new tables? Any
>>> ideas
>>> on what to do?
>>>
>>> Thanks!
>>>
>>> Neil
>>>
>>
>