ODBC Refresh Interval

[Phil Reynolds]

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.

 

[Guest]

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

 

[Sylvain Lafontaine]

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]

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.

 

[Pieter Wijnen]

Good recovery <g>

Pieter

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.

 

[Guest]

<smirk>

Steve

Good recovery <g>

Pieter

 

[Sylvain Lafontaine]

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

 

[Phil Reynolds]

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.

 

[Phil Reynolds]

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.

 

[Phil Reynolds]

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

 

[Guest]

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

 

[Sylvain Lafontaine]

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.

 

[Phil Reynolds]

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.

 

[Van T. Dinh]

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.

 

[Guest]

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]

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

 

[Pieter Wijnen]

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]

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"