Re: SQL Losing Data

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

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

Date: Fri, 14 Jan 2005 18:37:11 GMT

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

Relevant Pages

  • RE: Error 3197
    ... SQL Server 2005 as the backend. ... The trigger is designed to do a rollback and not commit any changes to the ... Tab1 subform is unbound and has no issues. ... 'Requery subform "Details" to hide records marked IsRemove = true ...
    (microsoft.public.access.tablesdbdesign)
  • Re: ODBC Update on a linked table failed
    ... What you can do is use profiler or even ... subform is leading to the problem. ... from the start button go to the SQL Server program ... It is definitely a locking issue, because I tried splitting the form into ...
    (microsoft.public.sqlserver.odbc)
  • Re: SQL Losing Data
    ... sources that support timestamps when using ODBC linked ... are all SQL Server issues or bugs then that's probably the ... >form fields above the subform, as well as the subform data itself, are being ... >needed to get the ODBC driver to behave correctly. ...
    (microsoft.public.sqlserver.odbc)
  • Subform loses recordset - shows #Name?
    ... Data is pulled from the SQL Server ... additional sproc for each subform. ... ADODB.Recordset is then applied to each of the forms/subforms Recordset ... Dim rstUsers As ADODB.Recordset: ...
    (comp.databases.ms-access)
  • Re: Me.Dirty - Where to put this logic?
    ... an Access frontend with SQL server backend. ... This is when the record is considered "dirty," it's there but not ... When the focus changes from the main form to a subform, ... data problem that is causing Access to *think* there's a write conflict ...
    (microsoft.public.access.forms)