473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL Losing Data

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
Nov 13 '05 #1
32 1728
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" <nr*@nrgconsult.com> wrote in message
news:ye*****************@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

Nov 13 '05 #2
Neil Ginsberg (nr*@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Nov 13 '05 #3
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"
<nr*@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" <nr*@nrgconsult.com> wrote in message
news:ye*****************@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


Nov 13 '05 #4
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" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
Neil Ginsberg (nr*@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, es****@sommarskog.se

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

Nov 13 '05 #5
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" <Su***@nomail.please> wrote in message
news:7q********************************@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"
<nr*@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" <nr*@nrgconsult.com> wrote in message
news:ye*****************@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

Nov 13 '05 #6
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" <Su***@nomail.please> wrote in message
news:7q********************************@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"
<nr*@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" <nr*@nrgconsult.com> wrote in message
news:ye*****************@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

Nov 13 '05 #7
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
Nov 13 '05 #8
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
Nov 13 '05 #9
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" <Su***@nomail.please> wrote in message
news:7q********************************@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"
<nr*@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" <nr*@nrgconsult.com> wrote in message
news:ye*****************@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

Nov 13 '05 #10
:-)

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" <no****@besty.org.uk> wrote in message
news:41**********************@news.zen.co.uk...
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

Nov 13 '05 #11
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"
<nr*@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,
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" <Su***@nomail.please> wrote in message
news:7q********************************@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"
<nr*@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" <nr*@nrgconsult.com> wrote in message
news:ye*****************@newsread3.news.pas.ear thlink.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


Nov 13 '05 #12
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" <Su***@nomail.please> wrote in message
news:m6********************************@4ax.com...
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"
<nr*@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,
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" <Su***@nomail.please> wrote in message
news:7q********************************@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"
<nr*@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" <nr*@nrgconsult.com> wrote in message
news:ye*****************@newsread3.news.pas.ea rthlink.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
>

Nov 13 '05 #13
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"
<nr*@nrgconsult.com> wrote:
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" <Su***@nomail.please> wrote in message
news:m6********************************@4ax.com.. .
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"
<nr*@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,
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" <Su***@nomail.please> wrote in message
news:7q********************************@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"
<nr*@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" <nr*@nrgconsult.com> wrote in message
>news:ye*****************@newsread3.news.pas.e arthlink.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
>>
>


Nov 13 '05 #14

"Sue Hoegemeier" <Su***@nomail.please> wrote in message
news:hm********************************@4ax.com...
Actually, the other problem wasn't a SQL Server thing. It's
an issue with how Access interprets record changes with the
ODBC driver.
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.
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.
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.
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.
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

-Sue

On Fri, 14 Jan 2005 01:36:49 GMT, "Neil Ginsberg"
<nr*@nrgconsult.com> wrote:
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" <Su***@nomail.please> wrote in message
news:m6********************************@4ax.com. ..
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"
<nr*@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,
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" <Su***@nomail.please> wrote in message
news:7q********************************@4ax.co m...
> 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"
> <nr*@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" <nr*@nrgconsult.com> wrote in message
>>news:ye*****************@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
>>>
>>
>

Nov 13 '05 #15
Neil Ginsberg wrote:
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?


There's loads of filters you can use in Profiler. I'd recommend (in the
first screen) getting rid of all events except TSQL:SQL:BatchCompleted,
to simplify things, use it against your MSDE one then you needn't filter
out other users and apps, shut down Enterprise Mangler and all other
apps that connect to the server. (turn off timers in other forms or
close them too)

Start a trace just before hitting save, then end it just after, you'll
get about half a dozen or so lines. You may recognise an update
statement or sp_prepare /sp_Execute pair or just the first, which would
indicate a problem (preparing to execute something but not actually
doing it).

Another thing, if you really can't get on with profiler is to (again
just before the save that would fail) create an update trigger on the
table that raises an error, go back to Access and hit save. If you get
your own error message then this means that Access did actually try to
save the record to the server.

However if you don't there's 2 possibilities:
1. Access did not attempt to save the record or it failed somewhere in
Access/ODBC b4 reaching the server or
2. Universal error trapping (On Error Resume Next, DoCmd.Setwarnings
False, etc) in place.
--
This sig left intentionally blank
Nov 13 '05 #16
Access and SQL is a sick combination.
I have had such strange problems with A2K and SQL2K.
The worst thing is some of these problems are intermittant.
Sometimes these problems go away with a Jet engine update or an MDAC update.
And then sometimes new ones of a similar vein pop up with updates.

"Neil Ginsberg" <nr*@nrgconsult.com> wrote in message
news:Rm*****************@newsread3.news.pas.earthl ink.net...
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" <Su***@nomail.please> wrote in message
news:m6********************************@4ax.com...
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"
<nr*@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, andthe 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 thedata; but doing a Save Record is not. Also, even without the explicit SaveRecord, 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 withit.

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" <Su***@nomail.please> wrote in message
news:7q********************************@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"
<nr*@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" <nr*@nrgconsult.com> wrote in message
>news:ye*****************@newsread3.news.pas.ea rthlink.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
>>
>


Nov 13 '05 #17
Actually not really. I still think you are missing the
point. You often have to do the same thing with other data
sources that support timestamps when using ODBC linked
tables and Access. You have to kluge the data source so that
Access doesn't get confused. You can find more information
in the following Access KB article:
ACC2000: Optimizing for Client/Server Performance
http://support.microsoft.com/?id=208858
The point being, that if you convince yourself that these
are all SQL Server issues or bugs then that's probably the
only place you will look to for solutions. In the first
case, you would not have found the answer searching SQL
Server issues. You'd find the answer under Access and ODBC.
If you leave yourself convinced that the users edit records
on an Access form and the changes aren't saved so it must be
a SQL Server issue, you may not find the answer to the
problem or you may end up wasting a lot of time.
Keep in mind that you posted earlier that:
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.
In terms of Profiler, you can filter the trace to your
access application to limit the results. With bound forms,
the commands would show up under the RPC and SP event
classes. In addition, there is a lot of information on using
profiler in SQL Server books online.

The other resource you may find useful overall is to get a
copy of Microsoft Access Developers Guide to SQL Server by
Mary Chipman and Andy Baron. It's an excellent book.

-Sue

On Fri, 14 Jan 2005 06:32:56 GMT, "Neil Ginsberg"
<nr*@nrgconsult.com> wrote:
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.


Nov 13 '05 #18

"Sue Hoegemeier" <Su***@nomail.please> wrote in message
news:i8********************************@4ax.com...
Actually not really. I still think you are missing the
point. You often have to do the same thing with other data
sources that support timestamps when using ODBC linked
tables and Access. You have to kluge the data source so that
Access doesn't get confused. You can find more information
in the following Access KB article:
ACC2000: Optimizing for Client/Server Performance
http://support.microsoft.com/?id=208858
The point being, that if you convince yourself that these
are all SQL Server issues or bugs then that's probably the
only place you will look to for solutions. In the first
case, you would not have found the answer searching SQL
Server issues. You'd find the answer under Access and ODBC.
I think we're dealing with semantics here. I never said it was a SQL Server
issue. I'm sure SQL Server is doing everything it's supposed to do. Is that
better? The issue is that the data's not getting to SQL Server because the
ODBC driver isn't sending it there for some reason, or is thinking the data
is lost.

And I would never look just under SQL Server for answers. I'd look under all
three, because it involves a hybrid of all three.
If you leave yourself convinced that the users edit records
on an Access form and the changes aren't saved so it must be
a SQL Server issue, you may not find the answer to the
problem or you may end up wasting a lot of time.
Keep in mind that you posted earlier that:
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.

I think this is a misunderstanding on your part about how Access works. My
point in making the above note was that data *was* being saved how it was
supposed to when moving between the main form and subforms. Access performs
an automatic save when you move from the main form to a subform, and vice
versa. So, by saying that "only the fields below the subform are being
lost," I wasn't saying that Access was saving some fields but not others; I
was saying that data is being saved as it should be when the user moves into
the subform. But then, when they move out of the subform and edit the lower
fields below the subform, that data is lost when they move to a new record.

In any case, as noted, I'm not saying that the SQL Server engine, per se, is
malfunctioning; only that the interface between Access and SQL Server is
malfunctioning, and something needs to be adjusted. In the case of the
timestamp field, it was something on the SQL Server side that needed
adjusting, even if it wasn't SQL Server's fault, per se. In this case it's
not clear what needs to be done. All I know is that there's no code to
tweak, and the built-in function of a bound Access form automatically saving
data when leaving the form is something I've never seen fail in all the
years I've been working with Access.

In terms of Profiler, you can filter the trace to your
access application to limit the results. With bound forms,
the commands would show up under the RPC and SP event
classes. In addition, there is a lot of information on using
profiler in SQL Server books online.
Thanks.

The other resource you may find useful overall is to get a
copy of Microsoft Access Developers Guide to SQL Server by
Mary Chipman and Andy Baron. It's an excellent book.
I have it.

Thanks,

Neil

-Sue

On Fri, 14 Jan 2005 06:32:56 GMT, "Neil Ginsberg"
<nr*@nrgconsult.com> wrote:
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.

Nov 13 '05 #19
Thanks. I'll check that these users have the latest Jet and MDAC service
packs. I know we made sure all machines had them. But these might be new
machines, and there's a new network guy, and so on. Thanks for the tip.

Neil
e
"User" <Us**@aol.com> wrote in message
news:eW****************@TK2MSFTNGP11.phx.gbl...
Access and SQL is a sick combination.
I have had such strange problems with A2K and SQL2K.
The worst thing is some of these problems are intermittant.
Sometimes these problems go away with a Jet engine update or an MDAC
update.
And then sometimes new ones of a similar vein pop up with updates.

"Neil Ginsberg" <nr*@nrgconsult.com> wrote in message
news:Rm*****************@newsread3.news.pas.earthl ink.net...
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" <Su***@nomail.please> wrote in message
news:m6********************************@4ax.com...
> 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"
> <nr*@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,
>>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" <Su***@nomail.please> wrote in message
>>news:7q********************************@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"
>>> <nr*@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" <nr*@nrgconsult.com> wrote in message
>>>>news:ye*****************@newsread3.news.pas.ea rthlink.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
>>>>>
>>>>
>>>
>>
>



Nov 13 '05 #20
Thanks, Trevor.

"Trevor Best" <no****@besty.org.uk> wrote in message
news:41**********************@news.zen.co.uk...
Neil Ginsberg wrote:
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?


There's loads of filters you can use in Profiler. I'd recommend (in the
first screen) getting rid of all events except TSQL:SQL:BatchCompleted, to
simplify things, use it against your MSDE one then you needn't filter out
other users and apps, shut down Enterprise Mangler and all other apps that
connect to the server. (turn off timers in other forms or close them too)

Start a trace just before hitting save, then end it just after, you'll get
about half a dozen or so lines. You may recognise an update statement or
sp_prepare /sp_Execute pair or just the first, which would indicate a
problem (preparing to execute something but not actually doing it).

Another thing, if you really can't get on with profiler is to (again just
before the save that would fail) create an update trigger on the table
that raises an error, go back to Access and hit save. If you get your own
error message then this means that Access did actually try to save the
record to the server.

However if you don't there's 2 possibilities:
1. Access did not attempt to save the record or it failed somewhere in
Access/ODBC b4 reaching the server or
2. Universal error trapping (On Error Resume Next, DoCmd.Setwarnings
False, etc) in place.
--
This sig left intentionally blank

Nov 13 '05 #21
Okay then. I actually do have a pretty good understanding of
how Access works and in particular how it works when linking
tables via ODBC to various data sources and the issues that
can be encountered. I did know what you meant about the
record saving when moving around on the form. And I'm aware
of the order of events and what fires when in what order and
under what conditions. So I'm not so sure that it's
necessarily related to my not really understanding how
Access works.
I guess what you have seen fail and what I have seen fail in
Access is different but I'd rather move on for now.

-Sue
Nov 13 '05 #22
OK. Well, thanks again for your input!

Neil

"Sue Hoegemeier" <Su***@nomail.please> wrote in message
news:4s********************************@4ax.com...
Okay then. I actually do have a pretty good understanding of
how Access works and in particular how it works when linking
tables via ODBC to various data sources and the issues that
can be encountered. I did know what you meant about the
record saving when moving around on the form. And I'm aware
of the order of events and what fires when in what order and
under what conditions. So I'm not so sure that it's
necessarily related to my not really understanding how
Access works.
I guess what you have seen fail and what I have seen fail in
Access is different but I'd rather move on for now.

-Sue

Nov 13 '05 #23
For those who have been following this, here's a note I just got from one of
the users. If this situation was bizarre before, this just brings it to a
new level.

"I type in either keynotes [varchar 255] or notes [text]. Save (using Save
Record from the Records menu). Then, if I'm working in notes and save, and
click into either keynotes or citation [varchar 255], I immediately lost all
information in both notes and keynotes. This just happened twice. And I
really do make very sure the information is saved, but it disappears anyway.
Doesn't ALWAYS happen like this, sometimes I'll click into another area and
the information doesn't get lost. But it did just happen twice that way."

So it seems the data is getting lost even before the user exits the record
(presumably, clicking in one of the other fields just brings up the latest
data in the table, which is blank in those fields).

So there's some more that goes into the pot of this weirdness.

Neil

"Neil Ginsberg" <nr*@nrgconsult.com> wrote in message
news:GL****************@newsread3.news.pas.earthli nk.net...
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" <nr*@nrgconsult.com> wrote in message
news:ye*****************@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


Nov 13 '05 #24
Neil Ginsberg wrote:
For those who have been following this, here's a note I just got from one of
the users. If this situation was bizarre before, this just brings it to a
new level.

"I type in either keynotes [varchar 255] or notes [text]. Save (using Save
Record from the Records menu). Then, if I'm working in notes and save, and
click into either keynotes or citation [varchar 255], I immediately lost all
information in both notes and keynotes. This just happened twice. And I
really do make very sure the information is saved, but it disappears anyway.
Doesn't ALWAYS happen like this, sometimes I'll click into another area and
the information doesn't get lost. But it did just happen twice that way."

So it seems the data is getting lost even before the user exits the record
(presumably, clicking in one of the other fields just brings up the latest
data in the table, which is blank in those fields).

So there's some more that goes into the pot of this weirdness.


What method do you use to save the record in code?
--
This sig left intentionally blank
Nov 13 '05 #25
As noted, I'm just using the bound form's built-in save command. In this
case, the user is selecting Save Record from Access' Records menu. Other
times, the users may move to a new record, causing Access to perform an
automatic save.

The only time where code comes into play is if the user clicks the New
button, which first performs a docmd.runcommand accmdsaverecord before
moving to the new record. But that wasn't the case here; the user just
selected the built-in Save Record command.

Neil

"Trevor Best" <no****@besty.org.uk> wrote in message
news:41**********************@news.zen.co.uk...
Neil Ginsberg wrote:
For those who have been following this, here's a note I just got from one
of the users. If this situation was bizarre before, this just brings it
to a new level.

"I type in either keynotes [varchar 255] or notes [text]. Save (using
Save Record from the Records menu). Then, if I'm working in notes and
save, and click into either keynotes or citation [varchar 255], I
immediately lost all information in both notes and keynotes. This just
happened twice. And I really do make very sure the information is saved,
but it disappears anyway. Doesn't ALWAYS happen like this, sometimes I'll
click into another area and the information doesn't get lost. But it did
just happen twice that way."

So it seems the data is getting lost even before the user exits the
record (presumably, clicking in one of the other fields just brings up
the latest data in the table, which is blank in those fields).

So there's some more that goes into the pot of this weirdness.


What method do you use to save the record in code?
--
This sig left intentionally blank

Nov 13 '05 #26
Neil Ginsberg wrote:
As noted, I'm just using the bound form's built-in save command. In this
case, the user is selecting Save Record from Access' Records menu. Other
times, the users may move to a new record, causing Access to perform an
automatic save.

The only time where code comes into play is if the user clicks the New
button, which first performs a docmd.runcommand accmdsaverecord before
moving to the new record. But that wasn't the case here; the user just
selected the built-in Save Record command.


And everything's patched? e.g. Latest Service Pack for Access (SP3) and
MDAC2.8 (will be if WinXP as it's part of the OS now).

--
This sig left intentionally blank
Nov 13 '05 #27
Yes.

By the way, I tried the trace, but the users complained that it was slowing
things down, and I had to turn it off.

Which newsgroup do you read this in?

Thanks,

Neil

"Trevor Best" <no****@besty.org.uk> wrote in message
news:41**********************@news.zen.co.uk...
Neil Ginsberg wrote:
As noted, I'm just using the bound form's built-in save command. In this
case, the user is selecting Save Record from Access' Records menu. Other
times, the users may move to a new record, causing Access to perform an
automatic save.

The only time where code comes into play is if the user clicks the New
button, which first performs a docmd.runcommand accmdsaverecord before
moving to the new record. But that wasn't the case here; the user just
selected the built-in Save Record command.


And everything's patched? e.g. Latest Service Pack for Access (SP3) and
MDAC2.8 (will be if WinXP as it's part of the OS now).

--
This sig left intentionally blank

Nov 13 '05 #28
Neil Ginsberg wrote:
Yes.

By the way, I tried the trace, but the users complained that it was slowing
things down, and I had to turn it off.
That's another reason to try it on your MSDE :-)
Which newsgroup do you read this in?


comp.databases.ms-access
--
This sig left intentionally blank
Nov 13 '05 #29

"Trevor Best" <no****@besty.org.uk> wrote in message
news:41***********************@news.zen.co.uk...
Neil Ginsberg wrote:
Yes.

By the way, I tried the trace, but the users complained that it was
slowing things down, and I had to turn it off.
That's another reason to try it on your MSDE :-)


Yeah, well, I can't even get the problem to occur on my MSDE! I even tried
an automated data entry thing using SendKeys and creating 500 records.
Didn't lose data once. So I don't know what the f* is going on over there,
but users are experiencing it consistently enough that I don't think it's a
fluke user-error thing.

Neil
Which newsgroup do you read this in?


comp.databases.ms-access
--
This sig left intentionally blank

Nov 13 '05 #30
For those who have been following this, here's a follow-up and (hopefully)
the conclusion of this issue.

I switched the mechanism for creating a new record from going to the new
record in the bound form to creating one in the table, requerying the form,
and then going to that newly created record. The problem is gone.

So, apparently, there was some glitch with creating a new record through the
form, then immediately going to a subform and then out of it again to the
newly-created record that Access or the ODBC driver couldn't handle. As
noted, data that was entered after coming out of the subform was lost about
10% of the time, even though the users did an explicit save with the Save
Record menu command and/or Shift+Enter.

In any case, as noted, creating the new record in code and then going there
cleared up the problem. So, whatever the reason was, I'll just be doing that
from now one.

Thanks to everyone for their input!

Neil
"Neil Ginsberg" <nr*@nrgconsult.com> wrote in message
news:ye*****************@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

Nov 13 '05 #31
Neil Ginsberg wrote:
For those who have been following this, here's a follow-up and (hopefully)
the conclusion of this issue.

I switched the mechanism for creating a new record from going to the new
record in the bound form to creating one in the table, requerying the form,
and then going to that newly created record. The problem is gone.

So, apparently, there was some glitch with creating a new record through the
form, then immediately going to a subform and then out of it again to the
newly-created record that Access or the ODBC driver couldn't handle. As
noted, data that was entered after coming out of the subform was lost about
10% of the time, even though the users did an explicit save with the Save
Record menu command and/or Shift+Enter.

In any case, as noted, creating the new record in code and then going there
cleared up the problem. So, whatever the reason was, I'll just be doing that
from now one.

Thanks to everyone for their input!


That's Wierd City Arizona, glad you've got a workaround but I bet the
root cause will niggle away at the back of your mind for some time to come.

--
This sig left intentionally blank
Nov 13 '05 #32

"Trevor Best" <no****@besty.org.uk> wrote in message
news:41***********************@news.zen.co.uk...
Neil Ginsberg wrote:
For those who have been following this, here's a follow-up and
(hopefully) the conclusion of this issue.

I switched the mechanism for creating a new record from going to the new
record in the bound form to creating one in the table, requerying the
form, and then going to that newly created record. The problem is gone.

So, apparently, there was some glitch with creating a new record through
the form, then immediately going to a subform and then out of it again to
the newly-created record that Access or the ODBC driver couldn't handle.
As noted, data that was entered after coming out of the subform was lost
about 10% of the time, even though the users did an explicit save with
the Save Record menu command and/or Shift+Enter.

In any case, as noted, creating the new record in code and then going
there cleared up the problem. So, whatever the reason was, I'll just be
doing that from now one.

Thanks to everyone for their input!
That's Wierd City Arizona,


:-)

glad you've got a workaround but I bet the root cause will niggle away at the back of your mind for some time to
come.
I dunno. I've already wracked my brains so much on this, that I doubt I'll
be able to do more. I'll probably I'll just chalk it up to inadequacies in
the system and move on.

Thanks again for your input!

Neil

--
This sig left intentionally blank

Nov 13 '05 #33

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Scott Lyon | last post by:
I'm maintaining (read: I didn't write it, nor do I have the time to spend to rewrite it) an application that is suddenly giving me grief. The reason I say suddenly, is because we're in the...
32
by: Neil Ginsberg | last post by:
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...
6
by: Nad | last post by:
Hello, I need some help with this: I am trying to update some data using XmlTextWriter. I instantiate first and then do some validation and then copy data into the dataset. Finally I commit...
5
by: fbwhite | last post by:
I know this issue has been brought up many times, but I have tried many of the solutions to no avail. I wanted to give my specific case to see if someone could be of any help. We are using the...
9
by: Adrian Parker | last post by:
We have a website that works everywhere but on a few PCs on this one site.. Asp.Net 1.1 Server = Windows 2003 Client = XP In the web.config we use - cookieless="false" in the browser settings...
7
by: Mike | last post by:
While working on a few projects I have this annoying issue whereby my computer is losing the connection to my SQL database. I have to save my work exit VS log out of windowsXP and log back in,...
2
by: RRoma | last post by:
Does anybody know how I can import lists of numbers from Excel into Access without losing the preceeding Zeros??? The field must have a total of 10 digits.... Is this a function of how its saved in...
7
by: DaTurk | last post by:
Hi, I'm trying to send a large number of messages to a UDP multicast group with which I have one client subscribed. But I'm losing mesages, and I'm not entirely sure why. I can't seem to find...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.