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.

[David W. Fenton]

Rich P <rp*****@aol.comwrote in
news:46**********************@news.qwest.net:

If you have major plans with your sql server, I would steer away
from ODBC and start focusing on ADO (ADO.Net being the best).

I don't quite understand your advice for two reasons:

1. ADO.NET cannot be used with an Access front end (that's what the
OP said he was using).

2. Microsoft is recommending MDB + ODBC over ADP + ADO. You seem to
be recommending MDB + ADO, but that causes a number of problems
(e.g,. no linked tables).

Why is MS not recommending MDB + ADO if it's better than what they
*are* recommending? Seems to me that MDB + ODBC is the future of
Access development, at least for now.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Phil Reynolds" <ph*******@msn.comwrote in
news:hW***************@newssvr14.news.prodigy.net:

I had heard that ADO was
supposed to be better than ODBC in theory, but that Microsoft
never really brought it to maturity, and that it's still full of
bugs. Not having ever used it, I don't know. But that's what I
heard.

ADO is dead, of course.

MS is now recommending MDB + ODBC for SQL Server back ends.

That brings with it certain problems with Jet which ADP + ADO did
not have (ADPs are now deprecated by MS, though still supported, but
the main selling point was that they bypassed Jet), but ADO brings
its own set of similar problems (it often guesses wrong about what
you want to do, just like Jet does). Usually, Jet + ODBC problems
can be overcome by balancing your work between Jet and the SQL
Server, whereas it's often not so easy to work around ADO's wrong
guesses (from what I've heard -- I never "drank the Kool Aid"
myself, but the people who had the problems were pretty vocal about
them).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Rich P]

Not to dispute anything, but let me establish my position: I am an
enterprise programmer by trade, although I started out with Access but
have moved on (well, expanded my horizons). Access is not an Enterprise
tool. Visual Studio 2005 Enterprise is an enterprise tool. Sql Server
is an enterprise tool. I am responding to users who try to use Access
in the enterprise environment. If you are going to be doing enterprise
level programming - you should use an enterprise level tool like VS 2005
enterprise (or VS professional). But as long as people are going to try
to use Access in an Enterprise environment, I am just sharing my
experiences with that.

I spent a few years with ADP's - which are ODBC based - continuous
connections. I had more problems with that than I did using an MDB with
ADO and sql server.

For non enterprise operations and a sql server back end, ODBC is fine
with Access. But once you get more than one user using the application
at the same time - you are migrating into enterprise country and will
have the issues that is being posted about here.

Access is a great tool when used in its element (non enterprise). But
with time, everything is migrating to enterprise operations for which
Access was not really designed. Thus, enterprise tools like VS 2005
have been developed (by the same company - Microsoft).

For a non enterprise tool, Access has remarkable capabilities. But for
hard core enterprise operations, there just isn't any getting around
using an enterprise tool like .Net or Java.

Example: this week I had to create a custom graphical search program to
search harddisks for various files and open them (to validate records on
a sql server...interface with the sql server...). The program uses
delegates, interfaces, and .Net 2.0 framework GUI controls, ADO.Net. I
probably could write the code part in Access with a bunch of API's, but
I would not be able to use the GUI controls from the .Net framework
because Access is not .Net compatible. Even so, it would have been much
harder to implement the API's in Access (and a ton more code) than to
create this thing in .Net.

I share my com experience with the Access crowd because people shared
their experience with me when I was starting out, and it also keeps me
up to speed on new developments in com/Access. But for the folks who
need to do more than Access was designed for, I share my experience
their also. Obviously you can't use ADO.Net with Access, and I really
don't see why people say that ADO is dead. It may not have the
capabilties of ADO.Net, but it has way more capabilities than ODBC for
those people who need to use Access in an Enterprise setting.

If this thread is not about enterprise operations and Access, then my
discussion is pretty moot.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Phil Reynolds]

Thanks, David. One of the biggest (and most frustrating) problems I've had
with the ODBC approach (apart from the inability to lock a record, which
isn't a huge deal most of the time), is that I can't rely on FindFirst! With
a form bound to an ODBC linked table, I'll use FindFirst on the
RecordsetClone object to go to a particular record, and, even though I
always check the NoMatch property, oftentimes my user finds themselves at
the wrong record. MS has said in KB articles that this problem was fixed
with Jet x.y, or Jet y.z, or whatever. But I found that it hasn't gone away.

To work around this problem I implemented a routine which does the
following:

1) Perform a MoveLast on the recordsetclone (which creates a significant
delay, of course; but it seems to help the situation greatly).

2) After executing DoEvents, perform the FindFirst on the RecordsetClone
object.

3) If not NoMatch, then go to the recordsetclone record.

4) Explicitly check the value of the record you're at, to make sure it
actually is the right one.

5) If not in the right record, then requery the form, and go back to step 1.

This seems to have resolved the problem. But it sure is a pain to have to do
this for a simple Go To (and it presents delays in the process).

Have you ever had this problem with FindFirst bringing you to the wrong
record? Any tips/ideas/suggestions?

Thanks.
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"Phil Reynolds" <ph*******@msn.comwrote in
news:hW***************@newssvr14.news.prodigy.net:

>I had heard that ADO was
supposed to be better than ODBC in theory, but that Microsoft
never really brought it to maturity, and that it's still full of
bugs. Not having ever used it, I don't know. But that's what I
heard.

ADO is dead, of course.

MS is now recommending MDB + ODBC for SQL Server back ends.

That brings with it certain problems with Jet which ADP + ADO did
not have (ADPs are now deprecated by MS, though still supported, but
the main selling point was that they bypassed Jet), but ADO brings
its own set of similar problems (it often guesses wrong about what
you want to do, just like Jet does). Usually, Jet + ODBC problems
can be overcome by balancing your work between Jet and the SQL
Server, whereas it's often not so easy to work around ADO's wrong
guesses (from what I've heard -- I never "drank the Kool Aid"
myself, but the people who had the problems were pretty vocal about
them).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Phil Reynolds" <ph*******@msn.comwrote in
news:g8*****************@newssvr19.news.prodigy.ne t:

Have you ever had this problem with FindFirst bringing you to the
wrong record? Any tips/ideas/suggestions?

I mostly don't use FindFirst like that in any but the smallest
recordsets. Sounds like you're opening a whole table and doing finds
with FindFirst. I don't do that, but instead filter to the requested
record.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Phil Reynolds]

Yes, that's better. But the users like to scroll through records. Still,
it's encouraging to know that perhaps with smaller recordsets this problem
won't exist.

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"Phil Reynolds" <ph*******@msn.comwrote in
news:g8*****************@newssvr19.news.prodigy.ne t:

>Have you ever had this problem with FindFirst bringing you to the
wrong record? Any tips/ideas/suggestions?

I mostly don't use FindFirst like that in any but the smallest
recordsets. Sounds like you're opening a whole table and doing finds
with FindFirst. I don't do that, but instead filter to the requested
record.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Phil Reynolds]

Oh, one other thing I forgot to mention. When FindFirst would go to the
wrong record (which is why I implemented that loop), it would be off by
*exactly* 100 in the PK value. So it definitely was a bug in the ODBC
driver. But hopefully working with smaller recordsets will resolve the
issue.

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"Phil Reynolds" <ph*******@msn.comwrote in
news:g8*****************@newssvr19.news.prodigy.ne t:

>Have you ever had this problem with FindFirst bringing you to the
wrong record? Any tips/ideas/suggestions?

I mostly don't use FindFirst like that in any but the smallest
recordsets. Sounds like you're opening a whole table and doing finds
with FindFirst. I don't do that, but instead filter to the requested
record.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

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

--
HTH
Van T. Dinh
MVP (Access)


"Phil Reynolds" <ph*******@msn.comwrote in message
news:Ye****************@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.