:-)

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.org.uk> wrote in message
news:41e634cc$0$2398$db0fefd9@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

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,
>apparently, moving in and out of the subforms is working fine to save the
>data; but doing a Save Record is not. Also, even without the explicit Save
>Record, the users are clicking a New button, which performs a
>docmd.runcommand accmdsaverecord, and then goes to the new record. Even
>without the accmdsaverecord, the data should be saved; but certainly with
>it.
>
>The three fields below the subforms that are losing data are two varchar
>(255) fields, and one text field. (The text field is last.)
>
>So this is peculiar. Any other ideas regarding this would be appreciated.
>
>Thanks,
>
>Neil
>
>
>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>news:7qdbu0diucqfv72eove4jglip8u5cghrrj@4ax.com.. .
>> There isn't anything odd that SQL Server does with new
>> tables. This issue is more likely due to using ODBC or some
>> Access specific configuration, setting.
>> The "record has been changed" error you posted can happen
>> with a few different scenarios with ODBC linked tables in
>> Access. Basically, ODBC will use a timestamp to determine if
>> the record has been updated. If there is no timestamp
>> column, it compares all column values to see if the record
>> has been updated.
>> Some situations can cause Access to become "confused" as to
>> whether the record has been updated - such as having float
>> data types. Try adding a timestamp column to the SQL table
>> and see if the fixes the problem. There should be some
>> Access knowledge base articles on this subject that you may
>> want to search for.
>>
>> -Sue
>>
>> On Wed, 12 Jan 2005 20:28:22 GMT, "Neil Ginsberg"
>> <nrg@nrgconsult.com> wrote:
>>
>>>Another note on this issue. If I go into an existing record, type data in
>>>a
>>>field, save the record (using Shift+Enter), then immediately type more
>>>data
>>>in the same field, I get the message, "The data has been changed. Another
>>>user edited this record and saved the changes before you attempted to save
>>>your changes." Once the message is cleared, the steps of
>>>typing/saving/typing can be performed without any errors. But when you
>>>first
>>>go to a record and perform those steps, the error always comes up.
>>>
>>>This happens both on the network version of the database, as well as in my
>>>personal copy (using MSDE), so I know it's not a multi-user issue, but
>>>some
>>>kind of a glitch. It seems that it might be related to the below message
>>>re.
>>>losing data, so I posted it here.
>>>
>>>Any ideas?
>>>
>>>Thanks!
>>>
>>>Neil
>>>
>>>
>>>"Neil Ginsberg" <nrg@nrgconsult.com> wrote in message
>>>news:yefFd.5637$Ii4.2334@newsread3.news.pas.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
>>>>
>>>
>>
>

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.please> wrote in message
news:m6qdu0t446e57duannoo514hs1ft4235ts@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"
> <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,
>>apparently, moving in and out of the subforms is working fine to save the
>>data; but doing a Save Record is not. Also, even without the explicit Save
>>Record, the users are clicking a New button, which performs a
>>docmd.runcommand accmdsaverecord, and then goes to the new record. Even
>>without the accmdsaverecord, the data should be saved; but certainly with
>>it.
>>
>>The three fields below the subforms that are losing data are two varchar
>>(255) fields, and one text field. (The text field is last.)
>>
>>So this is peculiar. Any other ideas regarding this would be appreciated.
>>
>>Thanks,
>>
>>Neil
>>
>>
>>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>>news:7qdbu0diucqfv72eove4jglip8u5cghrrj@4ax.com. ..
>>> There isn't anything odd that SQL Server does with new
>>> tables. This issue is more likely due to using ODBC or some
>>> Access specific configuration, setting.
>>> The "record has been changed" error you posted can happen
>>> with a few different scenarios with ODBC linked tables in
>>> Access. Basically, ODBC will use a timestamp to determine if
>>> the record has been updated. If there is no timestamp
>>> column, it compares all column values to see if the record
>>> has been updated.
>>> Some situations can cause Access to become "confused" as to
>>> whether the record has been updated - such as having float
>>> data types. Try adding a timestamp column to the SQL table
>>> and see if the fixes the problem. There should be some
>>> Access knowledge base articles on this subject that you may
>>> want to search for.
>>>
>>> -Sue
>>>
>>> On Wed, 12 Jan 2005 20:28:22 GMT, "Neil Ginsberg"
>>> <nrg@nrgconsult.com> wrote:
>>>
>>>>Another note on this issue. If I go into an existing record, type data
>>>>in
>>>>a
>>>>field, save the record (using Shift+Enter), then immediately type more
>>>>data
>>>>in the same field, I get the message, "The data has been changed.
>>>>Another
>>>>user edited this record and saved the changes before you attempted to
>>>>save
>>>>your changes." Once the message is cleared, the steps of
>>>>typing/saving/typing can be performed without any errors. But when you
>>>>first
>>>>go to a record and perform those steps, the error always comes up.
>>>>
>>>>This happens both on the network version of the database, as well as in
>>>>my
>>>>personal copy (using MSDE), so I know it's not a multi-user issue, but
>>>>some
>>>>kind of a glitch. It seems that it might be related to the below message
>>>>re.
>>>>losing data, so I posted it here.
>>>>
>>>>Any ideas?
>>>>
>>>>Thanks!
>>>>
>>>>Neil
>>>>
>>>>
>>>>"Neil Ginsberg" <nrg@nrgconsult.com> wrote in message
>>>>news:yefFd.5637$Ii4.2334@newsread3.news.pas.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
>>>>>
>>>>
>>>
>>
>

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:

>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.please> wrote in message
>news:m6qdu0t446e57duannoo514hs1ft4235ts@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"
>> <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,
>>>apparently, moving in and out of the subforms is working fine to save the
>>>data; but doing a Save Record is not. Also, even without the explicit Save
>>>Record, the users are clicking a New button, which performs a
>>>docmd.runcommand accmdsaverecord, and then goes to the new record. Even
>>>without the accmdsaverecord, the data should be saved; but certainly with
>>>it.
>>>
>>>The three fields below the subforms that are losing data are two varchar
>>>(255) fields, and one text field. (The text field is last.)
>>>
>>>So this is peculiar. Any other ideas regarding this would be appreciated.
>>>
>>>Thanks,
>>>
>>>Neil
>>>
>>>
>>>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>>>news:7qdbu0diucqfv72eove4jglip8u5cghrrj@4ax.com ...
>>>> There isn't anything odd that SQL Server does with new
>>>> tables. This issue is more likely due to using ODBC or some
>>>> Access specific configuration, setting.
>>>> The "record has been changed" error you posted can happen
>>>> with a few different scenarios with ODBC linked tables in
>>>> Access. Basically, ODBC will use a timestamp to determine if
>>>> the record has been updated. If there is no timestamp
>>>> column, it compares all column values to see if the record
>>>> has been updated.
>>>> Some situations can cause Access to become "confused" as to
>>>> whether the record has been updated - such as having float
>>>> data types. Try adding a timestamp column to the SQL table
>>>> and see if the fixes the problem. There should be some
>>>> Access knowledge base articles on this subject that you may
>>>> want to search for.
>>>>
>>>> -Sue
>>>>
>>>> On Wed, 12 Jan 2005 20:28:22 GMT, "Neil Ginsberg"
>>>> <nrg@nrgconsult.com> wrote:
>>>>
>>>>>Another note on this issue. If I go into an existing record, type data
>>>>>in
>>>>>a
>>>>>field, save the record (using Shift+Enter), then immediately type more
>>>>>data
>>>>>in the same field, I get the message, "The data has been changed.
>>>>>Another
>>>>>user edited this record and saved the changes before you attempted to
>>>>>save
>>>>>your changes." Once the message is cleared, the steps of
>>>>>typing/saving/typing can be performed without any errors. But when you
>>>>>first
>>>>>go to a record and perform those steps, the error always comes up.
>>>>>
>>>>>This happens both on the network version of the database, as well as in
>>>>>my
>>>>>personal copy (using MSDE), so I know it's not a multi-user issue, but
>>>>>some
>>>>>kind of a glitch. It seems that it might be related to the below message
>>>>>re.
>>>>>losing data, so I posted it here.
>>>>>
>>>>>Any ideas?
>>>>>
>>>>>Thanks!
>>>>>
>>>>>Neil
>>>>>
>>>>>
>>>>>"Neil Ginsberg" <nrg@nrgconsult.com> wrote in message
>>>>>news:yefFd.5637$Ii4.2334@newsread3.news.pas.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
>>>>>>
>>>>>
>>>>
>>>
>>
>

"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:hmgeu0lo7gqenfa5js0qsmq29jfaqf72ul@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"
> <nrg@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" <Sue_H@nomail.please> wrote in message
>>news:m6qdu0t446e57duannoo514hs1ft4235ts@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"
>>> <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,
>>>>apparently, moving in and out of the subforms is working fine to save
>>>>the
>>>>data; but doing a Save Record is not. Also, even without the explicit
>>>>Save
>>>>Record, the users are clicking a New button, which performs a
>>>>docmd.runcommand accmdsaverecord, and then goes to the new record. Even
>>>>without the accmdsaverecord, the data should be saved; but certainly
>>>>with
>>>>it.
>>>>
>>>>The three fields below the subforms that are losing data are two varchar
>>>>(255) fields, and one text field. (The text field is last.)
>>>>
>>>>So this is peculiar. Any other ideas regarding this would be
>>>>appreciated.
>>>>
>>>>Thanks,
>>>>
>>>>Neil
>>>>
>>>>
>>>>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>>>>news:7qdbu0diucqfv72eove4jglip8u5cghrrj@4ax.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"
>>>>> <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.net...
>>>>>>> We're using SQL Server 7 with an Access 2000 MDB as a front end with
>>>>>>> ODBC
>>>>>>> linked tables. I recently created a new set of tables for the app,
>>>>>>> and
>>>>>>> users are complaining that unsaved data is being lost when they move
>>>>>>> to
>>>>>>> a
>>>>>>> new record. This seems to be the case when there are multiple users.
>>>>>>> When
>>>>>>> there is a single user using it, we don't seem to have that problem.
>>>>>>>
>>>>>>> It seems that we had this problem when we first converted from an
>>>>>>> MDB
>>>>>>> back
>>>>>>> end to a SQL 7 back end, years ago, but we haven't had this problem
>>>>>>> in
>>>>>>> a
>>>>>>> while. These are the first "entirely new" tables created in several
>>>>>>> years,
>>>>>>> and we seem to be having that problem again.
>>>>>>>
>>>>>>> Is this something with SQL 7 when it's dealing with new tables? Any
>>>>>>> ideas
>>>>>>> on what to do?
>>>>>>>
>>>>>>> Thanks!
>>>>>>>
>>>>>>> Neil
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>

Neil Ginsberg wrote:
> 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

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" <nrg@nrgconsult.com> wrote in message
news:RmFFd.6743$Ii4.5233@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" <Sue_H@nomail.please> wrote in message
> news:m6qdu0t446e57duannoo514hs1ft4235ts@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"
> > <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,
> >>apparently, moving in and out of the subforms is working fine to save
the
> >>data; but doing a Save Record is not. Also, even without the explicit
Save
> >>Record, the users are clicking a New button, which performs a
> >>docmd.runcommand accmdsaverecord, and then goes to the new record. Even
> >>without the accmdsaverecord, the data should be saved; but certainly
with
> >>it.
> >>
> >>The three fields below the subforms that are losing data are two varchar
> >>(255) fields, and one text field. (The text field is last.)
> >>
> >>So this is peculiar. Any other ideas regarding this would be
appreciated.
> >>
> >>Thanks,
> >>
> >>Neil
> >>
> >>
> >>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
> >>news:7qdbu0diucqfv72eove4jglip8u5cghrrj@4ax.com. ..
> >>> There isn't anything odd that SQL Server does with new
> >>> tables. This issue is more likely due to using ODBC or some
> >>> Access specific configuration, setting.
> >>> The "record has been changed" error you posted can happen
> >>> with a few different scenarios with ODBC linked tables in
> >>> Access. Basically, ODBC will use a timestamp to determine if
> >>> the record has been updated. If there is no timestamp
> >>> column, it compares all column values to see if the record
> >>> has been updated.
> >>> Some situations can cause Access to become "confused" as to
> >>> whether the record has been updated - such as having float
> >>> data types. Try adding a timestamp column to the SQL table
> >>> and see if the fixes the problem. There should be some
> >>> Access knowledge base articles on this subject that you may
> >>> want to search for.
> >>>
> >>> -Sue
> >>>
> >>> On Wed, 12 Jan 2005 20:28:22 GMT, "Neil Ginsberg"
> >>> <nrg@nrgconsult.com> wrote:
> >>>
> >>>>Another note on this issue. If I go into an existing record, type data
> >>>>in
> >>>>a
> >>>>field, save the record (using Shift+Enter), then immediately type more
> >>>>data
> >>>>in the same field, I get the message, "The data has been changed.
> >>>>Another
> >>>>user edited this record and saved the changes before you attempted to
> >>>>save
> >>>>your changes." Once the message is cleared, the steps of
> >>>>typing/saving/typing can be performed without any errors. But when you
> >>>>first
> >>>>go to a record and perform those steps, the error always comes up.
> >>>>
> >>>>This happens both on the network version of the database, as well as
in
> >>>>my
> >>>>personal copy (using MSDE), so I know it's not a multi-user issue, but
> >>>>some
> >>>>kind of a glitch. It seems that it might be related to the below
message
> >>>>re.
> >>>>losing data, so I posted it here.
> >>>>
> >>>>Any ideas?
> >>>>
> >>>>Thanks!
> >>>>
> >>>>Neil
> >>>>
> >>>>
> >>>>"Neil Ginsberg" <nrg@nrgconsult.com> wrote in message
> >>>>news:yefFd.5637$Ii4.2334@newsread3.news.pas.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
> >>>>>
> >>>>
> >>>
> >>
> >
>
>

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"
<nrg@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.

"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:i8ufu0tngvrjrjqs7j10fksd3jmp7elgd8@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"
> <nrg@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.
>

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" <User@aol.com> wrote in message
news:eWi8zkl%23EHA.3124@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" <nrg@nrgconsult.com> wrote in message
> news:RmFFd.6743$Ii4.5233@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" <Sue_H@nomail.please> wrote in message
>> news:m6qdu0t446e57duannoo514hs1ft4235ts@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"
>> > <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,
>> >>apparently, moving in and out of the subforms is working fine to save
> the
>> >>data; but doing a Save Record is not. Also, even without the explicit
> Save
>> >>Record, the users are clicking a New button, which performs a
>> >>docmd.runcommand accmdsaverecord, and then goes to the new record. Even
>> >>without the accmdsaverecord, the data should be saved; but certainly
> with
>> >>it.
>> >>
>> >>The three fields below the subforms that are losing data are two
>> >>varchar
>> >>(255) fields, and one text field. (The text field is last.)
>> >>
>> >>So this is peculiar. Any other ideas regarding this would be
> appreciated.
>> >>
>> >>Thanks,
>> >>
>> >>Neil
>> >>
>> >>
>> >>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>> >>news:7qdbu0diucqfv72eove4jglip8u5cghrrj@4ax.com. ..
>> >>> There isn't anything odd that SQL Server does with new
>> >>> tables. This issue is more likely due to using ODBC or some
>> >>> Access specific configuration, setting.
>> >>> The "record has been changed" error you posted can happen
>> >>> with a few different scenarios with ODBC linked tables in
>> >>> Access. Basically, ODBC will use a timestamp to determine if
>> >>> the record has been updated. If there is no timestamp
>> >>> column, it compares all column values to see if the record
>> >>> has been updated.
>> >>> Some situations can cause Access to become "confused" as to
>> >>> whether the record has been updated - such as having float
>> >>> data types. Try adding a timestamp column to the SQL table
>> >>> and see if the fixes the problem. There should be some
>> >>> Access knowledge base articles on this subject that you may
>> >>> want to search for.
>> >>>
>> >>> -Sue
>> >>>
>> >>> On Wed, 12 Jan 2005 20:28:22 GMT, "Neil Ginsberg"
>> >>> <nrg@nrgconsult.com> wrote:
>> >>>
>> >>>>Another note on this issue. If I go into an existing record, type
>> >>>>data
>> >>>>in
>> >>>>a
>> >>>>field, save the record (using Shift+Enter), then immediately type
>> >>>>more
>> >>>>data
>> >>>>in the same field, I get the message, "The data has been changed.
>> >>>>Another
>> >>>>user edited this record and saved the changes before you attempted to
>> >>>>save
>> >>>>your changes." Once the message is cleared, the steps of
>> >>>>typing/saving/typing can be performed without any errors. But when
>> >>>>you
>> >>>>first
>> >>>>go to a record and perform those steps, the error always comes up.
>> >>>>
>> >>>>This happens both on the network version of the database, as well as
> in
>> >>>>my
>> >>>>personal copy (using MSDE), so I know it's not a multi-user issue,
>> >>>>but
>> >>>>some
>> >>>>kind of a glitch. It seems that it might be related to the below
> message
>> >>>>re.
>> >>>>losing data, so I posted it here.
>> >>>>
>> >>>>Any ideas?
>> >>>>
>> >>>>Thanks!
>> >>>>
>> >>>>Neil
>> >>>>
>> >>>>
>> >>>>"Neil Ginsberg" <nrg@nrgconsult.com> wrote in message
>> >>>>news:yefFd.5637$Ii4.2334@newsread3.news.pas.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
>> >>>>>
>> >>>>
>> >>>
>> >>
>> >
>>
>>
>
>