SQL Losing Data

[Neil Ginsberg]

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]

Re: SQL Losing Data

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@newsr ead3.news.pas.e arthlink.net...[color=blue]
> 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
>[/color]

[Erland Sommarskog]

Re: SQL Losing Data

Neil Ginsberg (nrg@nrgconsult .com) writes:[color=blue]
> 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.[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Sue Hoegemeier]

Re: SQL Losing Data

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:
[color=blue]
>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.563 7$Ii4.2334@news read3.news.pas. earthlink.net.. .[color=green]
>> 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
>>[/color]
>[/color]

[Neil Ginsberg]

Re: SQL Losing Data

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@sommars kog.se> wrote in message
news:Xns95DD40B 1961BYazorman@1 27.0.0.1...[color=blue]
> Neil Ginsberg (nrg@nrgconsult .com) writes:[color=green]
>> 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.[/color]
>
> 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@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Neil Ginsberg]

Re: SQL Losing Data

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.p lease> wrote in message
news:7qdbu0diuc qfv72eove4jglip 8u5cghrrj@4ax.c om...[color=blue]
> 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:
>[color=green]
>>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.56 37$Ii4.2334@new sread3.news.pas .earthlink.net. ..[color=darkred]
>>> 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
>>>[/color]
>>[/color]
>[/color]

[Neil Ginsberg]

Re: SQL Losing Data

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.p lease> wrote in message
news:7qdbu0diuc qfv72eove4jglip 8u5cghrrj@4ax.c om...[color=blue]
> 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:
>[color=green]
>>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.56 37$Ii4.2334@new sread3.news.pas .earthlink.net. ..[color=darkred]
>>> 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
>>>[/color]
>>[/color]
>[/color]

[Trevor Best]

Re: SQL Losing Data

Neil Ginsberg wrote:[color=blue]
> 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?[/color]

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

[Trevor Best]

Re: SQL Losing Data

Neil Ginsberg wrote:[color=blue]
> 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![/color]

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

--
This sig left intentionally blank

[Neil Ginsberg]

Re: SQL Losing Data

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.runcomman d 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.p lease> wrote in message
news:7qdbu0diuc qfv72eove4jglip 8u5cghrrj@4ax.c om...[color=blue]
> 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:
>[color=green]
>>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.56 37$Ii4.2334@new sread3.news.pas .earthlink.net. ..[color=darkred]
>>> 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
>>>[/color]
>>[/color]
>[/color]

[Neil Ginsberg]

Re: SQL Losing Data

:-)

Well, I'm not out of the woods yet. See my latest post in this thread. Any
other ideas would be appreciated!

Neil

"Trevor Best" <nospam@besty.o rg.uk> wrote in message
news:41e634cc$0 $2398$db0fefd9@ news.zen.co.uk. ..[color=blue]
> Neil Ginsberg wrote:[color=green]
>> 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![/color]
>
> D'OH! that'll teach me to read the whole thread before replying :-)
>
> --
> This sig left intentionally blank[/color]

[Sue Hoegemeier]

Re: SQL Losing Data

That's something in Access, not SQL Server. Hopefully
someone in one of the Access groups will help you address
that issue. You may want to just post a new message to just
the access groups regarding the issue. You aren't losing
data - looks like the forms aren't saving correctly or how
you expect them to work.

-Sue

On Thu, 13 Jan 2005 21:30:01 GMT, "Neil Ginsberg"
<nrg@nrgconsult .com> wrote:
[color=blue]
>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.runcomma nd 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.p lease> wrote in message
>news:7qdbu0diu cqfv72eove4jgli p8u5cghrrj@4ax. com...[color=green]
>> 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:
>>[color=darkred]
>>>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.5 637$Ii4.2334@ne wsread3.news.pa s.earthlink.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
>>>>
>>>[/color]
>>[/color]
>[/color]

[Neil Ginsberg]

Re: SQL Losing Data

It does seem to be something in Access. However, there's nothing really in
Access, except a bound form -- which is one of many similar ones, all of
which work fine, except for this one.

Also, the other problem was something in SQL Server -- the timestamp
field -- though it appeared to be something in Access. So my feeling is that
there's something with the SQL database vis-a-vis the ODBC driver and its
use in Access. But I don't think there's anything that can be modified on
the Access end that would affect the situation.

Thanks for your input.

Neil


"Sue Hoegemeier" <Sue_H@nomail.p lease> wrote in message
news:m6qdu0t446 e57duannoo514hs 1ft4235ts@4ax.c om...[color=blue]
> That's something in Access, not SQL Server. Hopefully
> someone in one of the Access groups will help you address
> that issue. You may want to just post a new message to just
> the access groups regarding the issue. You aren't losing
> data - looks like the forms aren't saving correctly or how
> you expect them to work.
>
> -Sue
>
> On Thu, 13 Jan 2005 21:30:01 GMT, "Neil Ginsberg"
> <nrg@nrgconsult .com> wrote:
>[color=green]
>>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.runcomm and 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.p lease> wrote in message
>>news:7qdbu0di ucqfv72eove4jgl ip8u5cghrrj@4ax .com...[color=darkred]
>>> 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@n ewsread3.news.p as.earthlink.ne t...
>>>>> 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
>>>>>
>>>>
>>>[/color]
>>[/color]
>[/color]

[Sue Hoegemeier]

Re: SQL Losing Data

Actually, the other problem wasn't a SQL Server thing. It's
an issue with how Access interprets record changes with the
ODBC driver.
I'd be more likely to suspect it being something on the
Access side - the records save sometimes and sometimes they
don't. SQL Server will process whatever statements are sent
to it from the client or Access in this case. If the
statement isn't sent to the server, nothing will be done. If
an insert fails because it violates a constraint or
something along those lines then SQL Server is doing what it
is suppose to do. The application needs to determine if that
is happening and inform the user. It's probably something
like that or something with the code on the forms and how
records are being saved.
If you are convinced it is SQL Server that is doing
something wrong, you could tell this pretty easily by just
monitoring the server and the statements sent to it using
Profiler. If the statement doesn't show up, it wasn't sent
to the server. If it is sent and the changes don't take, you
can execute the statement in Query Analyzer and see if you
get an error.

-Sue

On Fri, 14 Jan 2005 01:36:49 GMT, "Neil Ginsberg"
<nrg@nrgconsult .com> wrote:
[color=blue]
>It does seem to be something in Access. However, there's nothing really in
>Access, except a bound form -- which is one of many similar ones, all of
>which work fine, except for this one.
>
>Also, the other problem was something in SQL Server -- the timestamp
>field -- though it appeared to be something in Access. So my feeling is that
>there's something with the SQL database vis-a-vis the ODBC driver and its
>use in Access. But I don't think there's anything that can be modified on
>the Access end that would affect the situation.
>
>Thanks for your input.
>
>Neil
>
>
>"Sue Hoegemeier" <Sue_H@nomail.p lease> wrote in message
>news:m6qdu0t44 6e57duannoo514h s1ft4235ts@4ax. com...[color=green]
>> That's something in Access, not SQL Server. Hopefully
>> someone in one of the Access groups will help you address
>> that issue. You may want to just post a new message to just
>> the access groups regarding the issue. You aren't losing
>> data - looks like the forms aren't saving correctly or how
>> you expect them to work.
>>
>> -Sue
>>
>> On Thu, 13 Jan 2005 21:30:01 GMT, "Neil Ginsberg"
>> <nrg@nrgconsult .com> wrote:
>>[color=darkred]
>>>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.runcom mand 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.p lease> wrote in message
>>>news:7qdbu0d iucqfv72eove4jg lip8u5cghrrj@4a x.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.earthlink.n et...
>>>>>> 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
>>>>>>
>>>>>
>>>>
>>>[/color]
>>[/color]
>[/color]

[Neil Ginsberg]

Re: SQL Losing Data


"Sue Hoegemeier" <Sue_H@nomail.p lease> wrote in message
news:hmgeu0lo7g qenfa5js0qsmq29 jfaqf72ul@4ax.c om...[color=blue]
> Actually, the other problem wasn't a SQL Server thing. It's
> an issue with how Access interprets record changes with the
> ODBC driver.[/color]

Right. But it was something that had to be adjusted on the SQL side, not on
the Access side. That's what I meant.

And I would argue that it was an ODBC driver issue, solely, not even Access.
But that's splitting hairs.
[color=blue]
> I'd be more likely to suspect it being something on the
> Access side - the records save sometimes and sometimes they
> don't. SQL Server will process whatever statements are sent
> to it from the client or Access in this case. If the
> statement isn't sent to the server, nothing will be done. If
> an insert fails because it violates a constraint or
> something along those lines then SQL Server is doing what it
> is suppose to do. The application needs to determine if that
> is happening and inform the user. It's probably something
> like that or something with the code on the forms and how
> records are being saved.[/color]

Well, I'm not sure how familiar you are with Access. But, in this case, a)
there is no code with the forms that's used for saving -- it's just a
plain-vanilla bound form that uses built-in Access functions that work
perfectly in all cases, and in all other tables/forms in this database. Now,
I won't say that Access isn't without it's quirks, because it has plenty.
But when it comes to just automatically saving a record in a bound form when
you move to another record or close the form, Access is flawless there. I've
seen Access do many strange things in the 8+ years I've been working with
it; but I've never seen it fail to save a record -- especially not when an
explicit save command is given.

Furthermore, Access provides in the sidebar of the form an indicator of
whether or not the data in the form is saved. If there's unsaved data, the
sidebar shows a pencil, indicating that edits are being performed. If all
the data in the form has been saved, the sidebar shows an arrowhead. In this
case, the users are performing an explicit save and are getting the
arrowhead. Thus, from Access' perspective, it's doing everything it's
supposed to do, and there's nothing to change there (and, again, there's no
code, just a bound form).

My guess for the culprit would be the ODBC driver, as I've seen it do some
funky things over the years, this last situation being one of them. And, in
this last situation, though it wasn't SQL Server's fault, per se, but,
rather how the ODBC driver interfaced with SQL Server, the end result was
that there was a configuration issue on the SQL end (timestamp fields)
needed to get the ODBC driver to behave correctly. That's what I'm talking
about.
[color=blue]
> If you are convinced it is SQL Server that is doing
> something wrong, you could tell this pretty easily by just
> monitoring the server and the statements sent to it using
> Profiler. If the statement doesn't show up, it wasn't sent
> to the server. If it is sent and the changes don't take, you
> can execute the statement in Query Analyzer and see if you
> get an error.[/color]

I've tried using Profiler in the past and found it a bit unwieldy. Perhaps
you have some tips here. In this case, the problem is intermittent, so I'd
have to be able to find the command that may or may not have been sent by
the ODBC driver from several users over a period of time. Last time I had an
intermittent problem like this, I found the Profiler data less than
useful -- most likely due to my inexperience with it, but still not very
useful. Any tips?

Thanks!

Neil
[color=blue]
>
> -Sue
>
> On Fri, 14 Jan 2005 01:36:49 GMT, "Neil Ginsberg"
> <nrg@nrgconsult .com> wrote:
>[color=green]
>>It does seem to be something in Access. However, there's nothing really in
>>Access, except a bound form -- which is one of many similar ones, all of
>>which work fine, except for this one.
>>
>>Also, the other problem was something in SQL Server -- the timestamp
>>field -- though it appeared to be something in Access. So my feeling is
>>that
>>there's something with the SQL database vis-a-vis the ODBC driver and its
>>use in Access. But I don't think there's anything that can be modified on
>>the Access end that would affect the situation.
>>
>>Thanks for your input.
>>
>>Neil
>>
>>
>>"Sue Hoegemeier" <Sue_H@nomail.p lease> wrote in message
>>news:m6qdu0t4 46e57duannoo514 hs1ft4235ts@4ax .com...[color=darkred]
>>> That's something in Access, not SQL Server. Hopefully
>>> someone in one of the Access groups will help you address
>>> that issue. You may want to just post a new message to just
>>> the access groups regarding the issue. You aren't losing
>>> data - looks like the forms aren't saving correctly or how
>>> you expect them to work.
>>>
>>> -Sue
>>>
>>> On Thu, 13 Jan 2005 21:30:01 GMT, "Neil Ginsberg"
>>> <nrg@nrgconsult .com> wrote:
>>>
>>>>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,
>>>>apparentl y, 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.runco mmand 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.p lease> wrote in message
>>>>news:7qdbu0 diucqfv72eove4j glip8u5cghrrj@4 ax.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
>>>>>>persona l 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:yefF d.5637$Ii4.2334 @newsread3.news .pas.earthlink. 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
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>[/color]
>>[/color]
>[/color]