Re: SQL Losing Data

From: Neil Ginsberg (nrg_at_nrgconsult.com)
Date: 01/14/05 

Date: Fri, 14 Jan 2005 06:32:56 GMT

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