Re: SQL Losing Data

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: User (User_at_aol.com)
Date: 01/14/05 

Date: Fri, 14 Jan 2005 08:49:14 -0800

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.earthlink.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.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
> >>>>>
> >>>>
> >>>
> >>
> >
>
>

Quantcast