We are using SQL Server as a back end to an Access front end on a LAN using 
ODBC linked tables. Users are periodically getting the "data has been 
changed by another user" error, and it's causing them to have to re-enter a 
lot of data.

The ODBC refresh interval has been left at the default (1500 sec) on all 
computers. I'm wondering if maybe changing that to a much smaller number 
(150 seconds?) would help alleviate the problem. Also, I'm wondering if 
there are other settings that could be tweaked to help with this problem.

Thank you for any assistance.

I would take a look at your Record Locking settings. What is it currently set 
to?

Changing the ODBC Refresh Interval to a lower number would cause more 
network traffic...

Steve

"Phil Reynolds" wrote:

> We are using SQL Server as a back end to an Access front end on a LAN using 
> ODBC linked tables. Users are periodically getting the "data has been 
> changed by another user" error, and it's causing them to have to re-enter a 
> lot of data.
> 
> The ODBC refresh interval has been left at the default (1500 sec) on all 
> computers. I'm wondering if maybe changing that to a much smaller number 
> (150 seconds?) would help alleviate the problem. Also, I'm wondering if 
> there are other settings that could be tweaked to help with this problem.
> 
> Thank you for any assistance. 
> 
> 
>

I might be wrong but changing the Record Lock setting should have no effect 
when working against linked SQL-Server tables.

In some case, lowering the ODBC refresh interval might help but an analysis 
of why this error is occurring should provide a better answer.  In most - 
but not all - well designed system, this error shouldn't occur because two 
different peoples shouldn't work on the same data.  For example, there is 
probably no reason why someone would change the telephone number of a client 
while another would change its address at the same time.  However, the fact 
that the telephone number of a client is in the process of beeing changed 
should have no effect at all on the mecanism of, for example, completing an 
order for him.  This is why the design should be reviewed in order to 
determine why this kind of error is occurring at all.

Using the SQL-Server Profiler would be a tool of choice for helping in the 
analysis of this problem.

Finally, the newsgroup m.p.access.adp.sqlserver has nothing to do with ODBC 
linked tables.

-- 
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"SteveM"  wrote in message 
news:F27A646D-89D8-4CB3-8070-02830A1713EE@microsoft.com...
>I would take a look at your Record Locking settings. What is it currently 
>set
> to?
>
> Changing the ODBC Refresh Interval to a lower number would cause more
> network traffic...
>
> Steve
>
> "Phil Reynolds" wrote:
>
>> We are using SQL Server as a back end to an Access front end on a LAN 
>> using
>> ODBC linked tables. Users are periodically getting the "data has been
>> changed by another user" error, and it's causing them to have to re-enter 
>> a
>> lot of data.
>>
>> The ODBC refresh interval has been left at the default (1500 sec) on all
>> computers. I'm wondering if maybe changing that to a much smaller number
>> (150 seconds?) would help alleviate the problem. Also, I'm wondering if
>> there are other settings that could be tweaked to help with this problem.
>>
>> Thank you for any assistance.
>>
>>
>>

Please disregard the first sentence (« I might be wrong but changing the 
Record Lock setting should have no effect when working against linked 
SQL-Server tables. ») in my previous post.  This was for another post and 
has nothing to do with this thread.

Sorry for the confusion.

-- 
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
wrote in message news:uS3wFz6$HHA.1204@TK2MSFTNGP03.phx.gbl...
>I might be wrong but changing the Record Lock setting should have no effect 
>when working against linked SQL-Server tables.
>
> In some case, lowering the ODBC refresh interval might help but an 
> analysis of why this error is occurring should provide a better answer. 
> In most - but not all - well designed system, this error shouldn't occur 
> because two different peoples shouldn't work on the same data.  For 
> example, there is probably no reason why someone would change the 
> telephone number of a client while another would change its address at the 
> same time.  However, the fact that the telephone number of a client is in 
> the process of beeing changed should have no effect at all on the mecanism 
> of, for example, completing an order for him.  This is why the design 
> should be reviewed in order to determine why this kind of error is 
> occurring at all.
>
> Using the SQL-Server Profiler would be a tool of choice for helping in the 
> analysis of this problem.
>
> Finally, the newsgroup m.p.access.adp.sqlserver has nothing to do with 
> ODBC linked tables.
>
> -- 
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "SteveM"  wrote in message 
> news:F27A646D-89D8-4CB3-8070-02830A1713EE@microsoft.com...
>>I would take a look at your Record Locking settings. What is it currently 
>>set
>> to?
>>
>> Changing the ODBC Refresh Interval to a lower number would cause more
>> network traffic...
>>
>> Steve
>>
>> "Phil Reynolds" wrote:
>>
>>> We are using SQL Server as a back end to an Access front end on a LAN 
>>> using
>>> ODBC linked tables. Users are periodically getting the "data has been
>>> changed by another user" error, and it's causing them to have to 
>>> re-enter a
>>> lot of data.
>>>
>>> The ODBC refresh interval has been left at the default (1500 sec) on all
>>> computers. I'm wondering if maybe changing that to a much smaller number
>>> (150 seconds?) would help alleviate the problem. Also, I'm wondering if
>>> there are other settings that could be tweaked to help with this 
>>> problem.
>>>
>>> Thank you for any assistance.
>>>
>>>
>>>
>
>

Good recovery <g>

Pieter


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
wrote in message news:um6vP86$HHA.5164@TK2MSFTNGP05.phx.gbl...
> Please disregard the first sentence (« I might be wrong but changing the 
> Record Lock setting should have no effect when working against linked 
> SQL-Server tables. ») in my previous post.  This was for another post and 
> has nothing to do with this thread.
>
> Sorry for the confusion.
>
> -- 
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
> wrote in message news:uS3wFz6$HHA.1204@TK2MSFTNGP03.phx.gbl...
>>I might be wrong but changing the Record Lock setting should have no 
>>effect when working against linked SQL-Server tables.
>>
>> In some case, lowering the ODBC refresh interval might help but an 
>> analysis of why this error is occurring should provide a better answer. 
>> In most - but not all - well designed system, this error shouldn't occur 
>> because two different peoples shouldn't work on the same data.  For 
>> example, there is probably no reason why someone would change the 
>> telephone number of a client while another would change its address at 
>> the same time.  However, the fact that the telephone number of a client 
>> is in the process of beeing changed should have no effect at all on the 
>> mecanism of, for example, completing an order for him.  This is why the 
>> design should be reviewed in order to determine why this kind of error is 
>> occurring at all.
>>
>> Using the SQL-Server Profiler would be a tool of choice for helping in 
>> the analysis of this problem.
>>
>> Finally, the newsgroup m.p.access.adp.sqlserver has nothing to do with 
>> ODBC linked tables.
>>
>> -- 
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>>
>> "SteveM"  wrote in message 
>> news:F27A646D-89D8-4CB3-8070-02830A1713EE@microsoft.com...
>>>I would take a look at your Record Locking settings. What is it currently 
>>>set
>>> to?
>>>
>>> Changing the ODBC Refresh Interval to a lower number would cause more
>>> network traffic...
>>>
>>> Steve
>>>
>>> "Phil Reynolds" wrote:
>>>
>>>> We are using SQL Server as a back end to an Access front end on a LAN 
>>>> using
>>>> ODBC linked tables. Users are periodically getting the "data has been
>>>> changed by another user" error, and it's causing them to have to 
>>>> re-enter a
>>>> lot of data.
>>>>
>>>> The ODBC refresh interval has been left at the default (1500 sec) on 
>>>> all
>>>> computers. I'm wondering if maybe changing that to a much smaller 
>>>> number
>>>> (150 seconds?) would help alleviate the problem. Also, I'm wondering if
>>>> there are other settings that could be tweaked to help with this 
>>>> problem.
>>>>
>>>> Thank you for any assistance.
>>>>
>>>>
>>>>
>>
>>
>
>

<smirk>

Steve

"Pieter Wijnen" wrote:

> Good recovery <g>
> 
> Pieter
> 
> 
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
> wrote in message news:um6vP86$HHA.5164@TK2MSFTNGP05.phx.gbl...
> > Please disregard the first sentence (« I might be wrong but changing the 
> > Record Lock setting should have no effect when working against linked 
> > SQL-Server tables. ») in my previous post.  This was for another post and 
> > has nothing to do with this thread.
> >
> > Sorry for the confusion.
> >
> > -- 
> > Sylvain Lafontaine, ing.
> > MVP - Technologies Virtual-PC
> > E-mail: sylvain aei ca (fill the blanks, no spam please)
> >
> >
> > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
> > wrote in message news:uS3wFz6$HHA.1204@TK2MSFTNGP03.phx.gbl...
> >>I might be wrong but changing the Record Lock setting should have no 
> >>effect when working against linked SQL-Server tables.
> >>
> >> In some case, lowering the ODBC refresh interval might help but an 
> >> analysis of why this error is occurring should provide a better answer. 
> >> In most - but not all - well designed system, this error shouldn't occur 
> >> because two different peoples shouldn't work on the same data.  For 
> >> example, there is probably no reason why someone would change the 
> >> telephone number of a client while another would change its address at 
> >> the same time.  However, the fact that the telephone number of a client 
> >> is in the process of beeing changed should have no effect at all on the 
> >> mecanism of, for example, completing an order for him.  This is why the 
> >> design should be reviewed in order to determine why this kind of error is 
> >> occurring at all.
> >>
> >> Using the SQL-Server Profiler would be a tool of choice for helping in 
> >> the analysis of this problem.
> >>
> >> Finally, the newsgroup m.p.access.adp.sqlserver has nothing to do with 
> >> ODBC linked tables.
> >>
> >> -- 
> >> Sylvain Lafontaine, ing.
> >> MVP - Technologies Virtual-PC
> >> E-mail: sylvain aei ca (fill the blanks, no spam please)
> >>
> >>
> >> "SteveM"  wrote in message 
> >> news:F27A646D-89D8-4CB3-8070-02830A1713EE@microsoft.com...
> >>>I would take a look at your Record Locking settings. What is it currently 
> >>>set
> >>> to?
> >>>
> >>> Changing the ODBC Refresh Interval to a lower number would cause more
> >>> network traffic...
> >>>
> >>> Steve
> >>>
> >>> "Phil Reynolds" wrote:
> >>>
> >>>> We are using SQL Server as a back end to an Access front end on a LAN 
> >>>> using
> >>>> ODBC linked tables. Users are periodically getting the "data has been
> >>>> changed by another user" error, and it's causing them to have to 
> >>>> re-enter a
> >>>> lot of data.
> >>>>
> >>>> The ODBC refresh interval has been left at the default (1500 sec) on 
> >>>> all
> >>>> computers. I'm wondering if maybe changing that to a much smaller 
> >>>> number
> >>>> (150 seconds?) would help alleviate the problem. Also, I'm wondering if
> >>>> there are other settings that could be tweaked to help with this 
> >>>> problem.
> >>>>
> >>>> Thank you for any assistance.
> >>>>
> >>>>
> >>>>
> >>
> >>
> >
> > 
> 
> 
>

« Good recovery <g> » in what sense?  That this information would be false 
in the case of SQL-Server linked tables?

-- 
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Pieter Wijnen" 
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway> 
wrote in message news:O1ioXP7$HHA.1208@TK2MSFTNGP05.phx.gbl...
> Good recovery <g>
>
> Pieter
>
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
> wrote in message news:um6vP86$HHA.5164@TK2MSFTNGP05.phx.gbl...
>> Please disregard the first sentence (« I might be wrong but changing the 
>> Record Lock setting should have no effect when working against linked 
>> SQL-Server tables. ») in my previous post.  This was for another post and 
>> has nothing to do with this thread.
>>
>> Sorry for the confusion.
>>
>> -- 
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>>
>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
>> wrote in message news:uS3wFz6$HHA.1204@TK2MSFTNGP03.phx.gbl...
>>>I might be wrong but changing the Record Lock setting should have no 
>>>effect when working against linked SQL-Server tables.
>>>
>>> In some case, lowering the ODBC refresh interval might help but an 
>>> analysis of why this error is occurring should provide a better answer. 
>>> In most - but not all - well designed system, this error shouldn't occur 
>>> because two different peoples shouldn't work on the same data.  For 
>>> example, there is probably no reason why someone would change the 
>>> telephone number of a client while another would change its address at 
>>> the same time.  However, the fact that the telephone number of a client 
>>> is in the process of beeing changed should have no effect at all on the 
>>> mecanism of, for example, completing an order for him.  This is why the 
>>> design should be reviewed in order to determine why this kind of error 
>>> is occurring at all.
>>>
>>> Using the SQL-Server Profiler would be a tool of choice for helping in 
>>> the analysis of this problem.
>>>
>>> Finally, the newsgroup m.p.access.adp.sqlserver has nothing to do with 
>>> ODBC linked tables.
>>>
>>> -- 
>>> Sylvain Lafontaine, ing.
>>> MVP - Technologies Virtual-PC
>>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>>
>>>
>>> "SteveM"  wrote in message 
>>> news:F27A646D-89D8-4CB3-8070-02830A1713EE@microsoft.com...
>>>>I would take a look at your Record Locking settings. What is it 
>>>>currently set
>>>> to?
>>>>
>>>> Changing the ODBC Refresh Interval to a lower number would cause more
>>>> network traffic...
>>>>
>>>> Steve
>>>>
>>>> "Phil Reynolds" wrote:
>>>>
>>>>> We are using SQL Server as a back end to an Access front end on a LAN 
>>>>> using
>>>>> ODBC linked tables. Users are periodically getting the "data has been
>>>>> changed by another user" error, and it's causing them to have to 
>>>>> re-enter a
>>>>> lot of data.
>>>>>
>>>>> The ODBC refresh interval has been left at the default (1500 sec) on 
>>>>> all
>>>>> computers. I'm wondering if maybe changing that to a much smaller 
>>>>> number
>>>>> (150 seconds?) would help alleviate the problem. Also, I'm wondering 
>>>>> if
>>>>> there are other settings that could be tweaked to help with this 
>>>>> problem.
>>>>>
>>>>> Thank you for any assistance.
>>>>>
>>>>>
>>>>>
>>>
>>>
>>
>>
>
>

Record locking has no effect when using SQL Server as a back end. Record 
locking works with Jet databases. Basically, when you use ODBC linked 
tables, Jet manages the changes and then sends the changes to SQL Server 
when the record is updated. Thus, SQL Server isn't "aware" of the record 
being edited until it's updated. And since the data's not in the Jet engine, 
Jet can't manage record locking for the table. So the record locking 
settings have no effect.


"SteveM"  wrote in message 
news:F27A646D-89D8-4CB3-8070-02830A1713EE@microsoft.com...
>I would take a look at your Record Locking settings. What is it currently 
>set
> to?
>
> Changing the ODBC Refresh Interval to a lower number would cause more
> network traffic...
>
> Steve
>
> "Phil Reynolds" wrote:
>
>> We are using SQL Server as a back end to an Access front end on a LAN 
>> using
>> ODBC linked tables. Users are periodically getting the "data has been
>> changed by another user" error, and it's causing them to have to re-enter 
>> a
>> lot of data.
>>
>> The ODBC refresh interval has been left at the default (1500 sec) on all
>> computers. I'm wondering if maybe changing that to a much smaller number
>> (150 seconds?) would help alleviate the problem. Also, I'm wondering if
>> there are other settings that could be tweaked to help with this problem.
>>
>> Thank you for any assistance.
>>
>>
>>

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
wrote in message news:uS3wFz6$HHA.1204@TK2MSFTNGP03.phx.gbl...
>I might be wrong but changing the Record Lock setting should have no effect 
>when working against linked SQL-Server tables.

That is correct.

>
> In some case, lowering the ODBC refresh interval might help but an 
> analysis of why this error is occurring should provide a better answer. 
> In most - but not all - well designed system, this error shouldn't occur 
> because two different peoples shouldn't work on the same data.  For 
> example, there is probably no reason why someone would change the 
> telephone number of a client while another would change its address at the 
> same time.  However, the fact that the telephone number of a client is in 
> the process of beeing changed should have no effect at all on the mecanism 
> of, for example, completing an order for him.  This is why the design 
> should be reviewed in order to determine why this kind of error is 
> occurring at all.

In our case, there are items, and one person may be completing information 
for the item, while another person may put the item "on hold" while the 
first person's doing their edits (which may take 20 minutes or so). Thus, 
the second person updating a single field (the Hold field) and saving the 
record screws up the person spending 20 minutes doing edits.

>
> Using the SQL-Server Profiler would be a tool of choice for helping in the 
> analysis of this problem.

Never could really get much useful information out of that. I'm sure I'm 
just not using it right. But never really could use it to solve problems.

>
> Finally, the newsgroup m.p.access.adp.sqlserver has nothing to do with 
> ODBC linked tables.

True. And I apologize if I shouldn't have cross-posted here. But I felt that 
people familiar with using SQL Server for ADPs might also be familiar with 
using it with ODBC. Again, my apologies.


>
> -- 
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "SteveM"  wrote in message 
> news:F27A646D-89D8-4CB3-8070-02830A1713EE@microsoft.com...
>>I would take a look at your Record Locking settings. What is it currently 
>>set
>> to?
>>
>> Changing the ODBC Refresh Interval to a lower number would cause more
>> network traffic...
>>
>> Steve
>>
>> "Phil Reynolds" wrote:
>>
>>> We are using SQL Server as a back end to an Access front end on a LAN 
>>> using
>>> ODBC linked tables. Users are periodically getting the "data has been
>>> changed by another user" error, and it's causing them to have to 
>>> re-enter a
>>> lot of data.
>>>
>>> The ODBC refresh interval has been left at the default (1500 sec) on all
>>> computers. I'm wondering if maybe changing that to a much smaller number
>>> (150 seconds?) would help alleviate the problem. Also, I'm wondering if
>>> there are other settings that could be tweaked to help with this 
>>> problem.
>>>
>>> Thank you for any assistance.
>>>
>>>
>>>
>
>

Actually that was for this thread. So the only post that was confusing was 
this one saying to ignore the first sentence... :-)

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
wrote in message news:um6vP86$HHA.5164@TK2MSFTNGP05.phx.gbl...
> Please disregard the first sentence (« I might be wrong but changing the 
> Record Lock setting should have no effect when working against linked 
> SQL-Server tables. ») in my previous post.  This was for another post and 
> has nothing to do with this thread.
>
> Sorry for the confusion.
>
> -- 
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
> wrote in message news:uS3wFz6$HHA.1204@TK2MSFTNGP03.phx.gbl...
>>I might be wrong but changing the Record Lock setting should have no 
>>effect when working against linked SQL-Server tables.
>>
>> In some case, lowering the ODBC refresh interval might help but an 
>> analysis of why this error is occurring should provide a better answer. 
>> In most - but not all - well designed system, this error shouldn't occur 
>> because two different peoples shouldn't work on the same data.  For 
>> example, there is probably no reason why someone would change the 
>> telephone number of a client while another would change its address at 
>> the same time.  However, the fact that the telephone number of a client 
>> is in the process of beeing changed should have no effect at all on the 
>> mecanism of, for example, completing an order for him.  This is why the 
>> design should be reviewed in order to determine why this kind of error is 
>> occurring at all.
>>
>> Using the SQL-Server Profiler would be a tool of choice for helping in 
>> the analysis of this problem.
>>
>> Finally, the newsgroup m.p.access.adp.sqlserver has nothing to do with 
>> ODBC linked tables.
>>
>> -- 
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>>
>> "SteveM"  wrote in message 
>> news:F27A646D-89D8-4CB3-8070-02830A1713EE@microsoft.com...
>>>I would take a look at your Record Locking settings. What is it currently 
>>>set
>>> to?
>>>
>>> Changing the ODBC Refresh Interval to a lower number would cause more
>>> network traffic...
>>>
>>> Steve
>>>
>>> "Phil Reynolds" wrote:
>>>
>>>> We are using SQL Server as a back end to an Access front end on a LAN 
>>>> using
>>>> ODBC linked tables. Users are periodically getting the "data has been
>>>> changed by another user" error, and it's causing them to have to 
>>>> re-enter a
>>>> lot of data.
>>>>
>>>> The ODBC refresh interval has been left at the default (1500 sec) on 
>>>> all
>>>> computers. I'm wondering if maybe changing that to a much smaller 
>>>> number
>>>> (150 seconds?) would help alleviate the problem. Also, I'm wondering if
>>>> there are other settings that could be tweaked to help with this 
>>>> problem.
>>>>
>>>> Thank you for any assistance.
>>>>
>>>>
>>>>
>>
>>
>
>

Ah didn't know that. I've never used Access as a frontend to SQL Server. I 
would always create a .NET frontend.

Can you not lock the record on Edit?
Can you use record locking through a transaction when updating?

Actually, this reminds me of another question about record locking in Access 
that has bugged me for some time...I'll start a new thread.

Steve

"Phil Reynolds" wrote:

> Record locking has no effect when using SQL Server as a back end. Record 
> locking works with Jet databases. Basically, when you use ODBC linked 
> tables, Jet manages the changes and then sends the changes to SQL Server 
> when the record is updated. Thus, SQL Server isn't "aware" of the record 
> being edited until it's updated. And since the data's not in the Jet engine, 
> Jet can't manage record locking for the table. So the record locking 
> settings have no effect.
> 
> 
> "SteveM"  wrote in message 
> news:F27A646D-89D8-4CB3-8070-02830A1713EE@microsoft.com...
> >I would take a look at your Record Locking settings. What is it currently 
> >set
> > to?
> >
> > Changing the ODBC Refresh Interval to a lower number would cause more
> > network traffic...
> >
> > Steve
> >
> > "Phil Reynolds" wrote:
> >
> >> We are using SQL Server as a back end to an Access front end on a LAN 
> >> using
> >> ODBC linked tables. Users are periodically getting the "data has been
> >> changed by another user" error, and it's causing them to have to re-enter 
> >> a
> >> lot of data.
> >>
> >> The ODBC refresh interval has been left at the default (1500 sec) on all
> >> computers. I'm wondering if maybe changing that to a much smaller number
> >> (150 seconds?) would help alleviate the problem. Also, I'm wondering if
> >> there are other settings that could be tweaked to help with this problem.
> >>
> >> Thank you for any assistance.
> >>
> >>
> >> 
> 
> 
>

"Phil Reynolds" <wrote:
<snip>
> In our case, there are items, and one person may be completing information 
> for the item, while another person may put the item "on hold" while the 
> first person's doing their edits (which may take 20 minutes or so). Thus, 
> the second person updating a single field (the Hold field) and saving the 
> record screws up the person spending 20 minutes doing edits.
>
>>
Hi Phil,

In a general sense, I imagine you will have to decide
whether to handle this as one specific case, or a general
solution.

Probably more complicated than following, but I might
use Mary Chipman's "ConcurrencyID" (what follows
should only be considered as my possibly-misinterpreted
implementation of her method).

As a simple example, you have a table that more than one user
may edit at the same time. It contains an int field ConcurrencyID
that starts out as 1 in a new record.

To edit a record, a cmd button (say on a snaphot main form) opens an
unbound form for the table, passing pk of record you want to edit.

Using the passed pk arg, you fill the unbound text boxes in form open event.

The user makes changes then clicks on cmd button to
save changes to record by passing textbox values as
parameters through to a stored proc. As part
of the proc you check whether ConcurrencyID is same
as you started with.

If same, then save changes to table (while incrementing
ConcurrencyID in stored proc). Tell user changes successful.

If not the same, the proc aborts and sends back msg
where you now decide whether to react "in general"
or in a specific sense...some alternatives:

-- similar to what is happening now (what you don't want),
    you tell user and ask whether to overwrite or start over.

-- you go out and get new values in a recordset, then field-by-field
    show user "new value" where different and ask user if want
    to keep or change (for sure change ConcurrencyID), then resubmit
    to proc

-- you assume if Hold field is different ("assume" always bites you
    somewhere down the road) that need only change Hold and
    ConcurrencyID and resubmit changes via parameters to proc.

Like I said it is probably more complicated, but I would probably
use a similar method if possible.

good luck,

gary

Yeah, I've been confused a few moments: I made some checks after my first 
post, read back later the OP to make sure that I didn't forget anything 
important and saw that there were no mention of Record Lock in it, so I 
thought that I've mixed two posts from two different threads.

This things happens quite frequently, especially when it's about some 
popular concepts that come often in the newsgroups like the absence of 
pessimistic locking for ODBC linked tables against SQL-Server.

-- 
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Phil Reynolds"  wrote in message 
news:%3nKi.808$4V6.339@newssvr14.news.prodigy.net...
> Actually that was for this thread. So the only post that was confusing was 
> this one saying to ignore the first sentence... :-)
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
> wrote in message news:um6vP86$HHA.5164@TK2MSFTNGP05.phx.gbl...
>> Please disregard the first sentence (« I might be wrong but changing the 
>> Record Lock setting should have no effect when working against linked 
>> SQL-Server tables. ») in my previous post.  This was for another post and 
>> has nothing to do with this thread.
>>
>> Sorry for the confusion.
>>
>> -- 
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>>
>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
>> wrote in message news:uS3wFz6$HHA.1204@TK2MSFTNGP03.phx.gbl...
>>>I might be wrong but changing the Record Lock setting should have no 
>>>effect when working against linked SQL-Server tables.
>>>
>>> In some case, lowering the ODBC refresh interval might help but an 
>>> analysis of why this error is occurring should provide a better answer. 
>>> In most - but not all - well designed system, this error shouldn't occur 
>>> because two different peoples shouldn't work on the same data.  For 
>>> example, there is probably no reason why someone would change the 
>>> telephone number of a client while another would change its address at 
>>> the same time.  However, the fact that the telephone number of a client 
>>> is in the process of beeing changed should have no effect at all on the 
>>> mecanism of, for example, completing an order for him.  This is why the 
>>> design should be reviewed in order to determine why this kind of error 
>>> is occurring at all.
>>>
>>> Using the SQL-Server Profiler would be a tool of choice for helping in 
>>> the analysis of this problem.
>>>
>>> Finally, the newsgroup m.p.access.adp.sqlserver has nothing to do with 
>>> ODBC linked tables.
>>>
>>> -- 
>>> Sylvain Lafontaine, ing.
>>> MVP - Technologies Virtual-PC
>>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>>
>>>
>>> "SteveM"  wrote in message 
>>> news:F27A646D-89D8-4CB3-8070-02830A1713EE@microsoft.com...
>>>>I would take a look at your Record Locking settings. What is it 
>>>>currently set
>>>> to?
>>>>
>>>> Changing the ODBC Refresh Interval to a lower number would cause more
>>>> network traffic...
>>>>
>>>> Steve
>>>>
>>>> "Phil Reynolds" wrote:
>>>>
>>>>> We are using SQL Server as a back end to an Access front end on a LAN 
>>>>> using
>>>>> ODBC linked tables. Users are periodically getting the "data has been
>>>>> changed by another user" error, and it's causing them to have to 
>>>>> re-enter a
>>>>> lot of data.
>>>>>
>>>>> The ODBC refresh interval has been left at the default (1500 sec) on 
>>>>> all
>>>>> computers. I'm wondering if maybe changing that to a much smaller 
>>>>> number
>>>>> (150 seconds?) would help alleviate the problem. Also, I'm wondering 
>>>>> if
>>>>> there are other settings that could be tweaked to help with this 
>>>>> problem.
>>>>>
>>>>> Thank you for any assistance.
>>>>>
>>>>>
>>>>>
>>>
>>>
>>
>>
>
>

I don't know about using a transaction in SQL Server to lock the record. 
There might be a way; I don't know.

One thing that can be done is to create a lock table and manage it yourself. 
When one person begins to edit a record, add an entry to the lock table with 
that table, PK, and machine name, and then prevent anyone from saving to 
that record if that lock record exists. But that could get very messy.

Re. your other thread, I don't see it. Tell me the name of it and the NG 
it's in, and I'll look for it.

"SteveM"  wrote in message 
news:8F415448-650F-4736-BE77-87211C78A7C0@microsoft.com...
> Ah didn't know that. I've never used Access as a frontend to SQL Server. I
> would always create a .NET frontend.
>
> Can you not lock the record on Edit?
> Can you use record locking through a transaction when updating?
>
> Actually, this reminds me of another question about record locking in 
> Access
> that has bugged me for some time...I'll start a new thread.
>
> Steve
>
> "Phil Reynolds" wrote:
>
>> Record locking has no effect when using SQL Server as a back end. Record
>> locking works with Jet databases. Basically, when you use ODBC linked
>> tables, Jet manages the changes and then sends the changes to SQL Server
>> when the record is updated. Thus, SQL Server isn't "aware" of the record
>> being edited until it's updated. And since the data's not in the Jet 
>> engine,
>> Jet can't manage record locking for the table. So the record locking
>> settings have no effect.
>>
>>
>> "SteveM"  wrote in message
>> news:F27A646D-89D8-4CB3-8070-02830A1713EE@microsoft.com...
>> >I would take a look at your Record Locking settings. What is it 
>> >currently
>> >set
>> > to?
>> >
>> > Changing the ODBC Refresh Interval to a lower number would cause more
>> > network traffic...
>> >
>> > Steve
>> >
>> > "Phil Reynolds" wrote:
>> >
>> >> We are using SQL Server as a back end to an Access front end on a LAN
>> >> using
>> >> ODBC linked tables. Users are periodically getting the "data has been
>> >> changed by another user" error, and it's causing them to have to 
>> >> re-enter
>> >> a
>> >> lot of data.
>> >>
>> >> The ODBC refresh interval has been left at the default (1500 sec) on 
>> >> all
>> >> computers. I'm wondering if maybe changing that to a much smaller 
>> >> number
>> >> (150 seconds?) would help alleviate the problem. Also, I'm wondering 
>> >> if
>> >> there are other settings that could be tweaked to help with this 
>> >> problem.
>> >>
>> >> Thank you for any assistance.
>> >>
>> >>
>> >>
>>
>>
>>

I am a bit late on this one but I had a bit of problems with this error 
previously, especially when the user updates the Text or NText Field (SQL 
Server 2000 BE / Access 2003 FE).

My fix was to add a timestamp Field to each of the Table.  This seems to 
help SQL Server tracks whether a row update being sent from Access FE has 
been changed since the row was fetched by the same Access FE.  In fact, this 
error has not recurred on my database for over 18 months since the timestamp 
Field was added.

-- 
HTH
Van T. Dinh
MVP (Access)




"Phil Reynolds"  wrote in message 
news:YecKi.1704$6p6.406@newssvr25.news.prodigy.net...
> We are using SQL Server as a back end to an Access front end on a LAN 
> using ODBC linked tables. Users are periodically getting the "data has 
> been changed by another user" error, and it's causing them to have to 
> re-enter a lot of data.
>
> The ODBC refresh interval has been left at the default (1500 sec) on all 
> computers. I'm wondering if maybe changing that to a much smaller number 
> (150 seconds?) would help alleviate the problem. Also, I'm wondering if 
> there are other settings that could be tweaked to help with this problem.
>
> Thank you for any assistance.
>

Van,

You mention the time stamp field.  I saw that when I was setting up my SQL 
backend (Access frontend) using the upsizing Wizard from within Access.  I 
did not add time stamps at that time.  How do I go back and add them after 
the fact?  

Based on previous posts in this thread, I should not run into two people 
opening the same record at the same time, but possibly a quality check 
process where it is opened nearly immediately by a new user.  But the first 
user would otherwise have close the record.



"Van T. Dinh" wrote:

> I am a bit late on this one but I had a bit of problems with this error 
> previously, especially when the user updates the Text or NText Field (SQL 
> Server 2000 BE / Access 2003 FE).
> 
> My fix was to add a timestamp Field to each of the Table.  This seems to 
> help SQL Server tracks whether a row update being sent from Access FE has 
> been changed since the row was fetched by the same Access FE.  In fact, this 
> error has not recurred on my database for over 18 months since the timestamp 
> Field was added.
> 
> -- 
> HTH
> Van T. Dinh
> MVP (Access)
> 
> 
> 
> 
> "Phil Reynolds"  wrote in message 
> news:YecKi.1704$6p6.406@newssvr25.news.prodigy.net...
> > We are using SQL Server as a back end to an Access front end on a LAN 
> > using ODBC linked tables. Users are periodically getting the "data has 
> > been changed by another user" error, and it's causing them to have to 
> > re-enter a lot of data.
> >
> > The ODBC refresh interval has been left at the default (1500 sec) on all 
> > computers. I'm wondering if maybe changing that to a much smaller number 
> > (150 seconds?) would help alleviate the problem. Also, I'm wondering if 
> > there are other settings that could be tweaked to help with this problem.
> >
> > Thank you for any assistance.
> > 
> 
> 
>

I did one recently that someone else upsized the database to SQL Server 2005 
Back-End.  I simply used brute force: go into the design of each Table (in 
SQL Server) and added a timestamp Field.  Luckily, it is a small database 
with only about 70 Tables.

I am sure an SP can be written but writing an SP for it probably takes me a 
lot longer than brute force.

If you do have 2 people open the same record and the second person gets the 
error message on update, then it is a genuine error that we can accept / 
handle.  What we want to avoid are those spurious errors that we get without 
the TimeStamp Field as per Phil Reynolds' original post..

-- 
HTH
Van T. Dinh
MVP (Access)




"Archidrb"  wrote in message 
news:7DC6E4D6-7B35-45B8-9A7B-88E34E530C0A@microsoft.com...
> Van,
>
> You mention the time stamp field.  I saw that when I was setting up my SQL
> backend (Access frontend) using the upsizing Wizard from within Access.  I
> did not add time stamps at that time.  How do I go back and add them after
> the fact?
>
> Based on previous posts in this thread, I should not run into two people
> opening the same record at the same time, but possibly a quality check
> process where it is opened nearly immediately by a new user.  But the 
> first
> user would otherwise have close the record.
>
>

Partial Code
I Use a passthrough query "insSQLDDL" for this kind of change


  thQ = "SELECT DISTINCT TABLE_NAME FROM USER_COLUMNS.COLUMNS A" & 
VBA.vbCrLf & _
        "WHERE NOT EXISTS (SELECT 'X' FROM USER_COLUMNS B" & VBA.vbCrLf & _
        "WHERE B.TABLE_NAME=A.TABLE_NAME" & VBA.vbCrLf & _
        "AND   B.COLUMN_NAME = '' & B.TABLE_NAME & '_TS')"
  Set Rs = Db.OpenRecordset(thQ, DAO.dbOpenSnapshot)
  Set Qdef = Db.QueryDefs("inssqlddl")
  Qdef.Connect = Connect
  While Not Rs.EOF
    thQ = "ALTER TABLE " & Rs.Fields(0).Value & " ADD " & Rs.Fields(0).Value 
& "_TS TIMESTAMP"
    Qdef.SQL = thQ
    Qdef.Execute DAO.dbSeeChanges
    RefreshTabs = RefreshTabs & ",'" & Rs.Fields(0).Value & "'"
    Rs.MoveNext
  Wend
  Rs.Close: Set Rs = Nothing

"USER_COLUMNS" is a Passthrough query with this SQL
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

HtH

Pieter


"Van T. Dinh"  wrote in message 
news:On5cvMfCIHA.4584@TK2MSFTNGP03.phx.gbl...
>I did one recently that someone else upsized the database to SQL Server 
>2005 Back-End.  I simply used brute force: go into the design of each Table 
>(in SQL Server) and added a timestamp Field.  Luckily, it is a small 
>database with only about 70 Tables.
>
> I am sure an SP can be written but writing an SP for it probably takes me 
> a lot longer than brute force.
>
> If you do have 2 people open the same record and the second person gets 
> the error message on update, then it is a genuine error that we can accept 
> / handle.  What we want to avoid are those spurious errors that we get 
> without the TimeStamp Field as per Phil Reynolds' original post..
>
> -- 
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
>
> "Archidrb"  wrote in message 
> news:7DC6E4D6-7B35-45B8-9A7B-88E34E530C0A@microsoft.com...
>> Van,
>>
>> You mention the time stamp field.  I saw that when I was setting up my 
>> SQL
>> backend (Access frontend) using the upsizing Wizard from within Access. 
>> I
>> did not add time stamps at that time.  How do I go back and add them 
>> after
>> the fact?
>>
>> Based on previous posts in this thread, I should not run into two people
>> opening the same record at the same time, but possibly a quality check
>> process where it is opened nearly immediately by a new user.  But the 
>> first
>> user would otherwise have close the record.
>>
>>
>
>

Pieter

Thanks for the partial code .. When (and If) I get a chance, I use this to 
write a small utility to add TimeStampField to each Table as I seem to get 
stuck with fixing this problem repeatedly.

-- 
Cheers
Van T. Dinh




"Pieter Wijnen" 
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway> 
wrote in message news:OGu8cwfCIHA.4228@TK2MSFTNGP02.phx.gbl...
> Partial Code
> I Use a passthrough query "insSQLDDL" for this kind of change
>
>
>  thQ = "SELECT DISTINCT TABLE_NAME FROM USER_COLUMNS.COLUMNS A" & 
> VBA.vbCrLf & _
>        "WHERE NOT EXISTS (SELECT 'X' FROM USER_COLUMNS B" & VBA.vbCrLf & _
>        "WHERE B.TABLE_NAME=A.TABLE_NAME" & VBA.vbCrLf & _
>        "AND   B.COLUMN_NAME = '' & B.TABLE_NAME & '_TS')"
>  Set Rs = Db.OpenRecordset(thQ, DAO.dbOpenSnapshot)
>  Set Qdef = Db.QueryDefs("inssqlddl")
>  Qdef.Connect = Connect
>  While Not Rs.EOF
>    thQ = "ALTER TABLE " & Rs.Fields(0).Value & " ADD " & 
> Rs.Fields(0).Value & "_TS TIMESTAMP"
>    Qdef.SQL = thQ
>    Qdef.Execute DAO.dbSeeChanges
>    RefreshTabs = RefreshTabs & ",'" & Rs.Fields(0).Value & "'"
>    Rs.MoveNext
>  Wend
>  Rs.Close: Set Rs = Nothing
>
> "USER_COLUMNS" is a Passthrough query with this SQL
> SELECT * FROM INFORMATION_SCHEMA.COLUMNS
>
> HtH
>
> Pieter
>
>