Access / SQL Server Data Changed Error?

[Dave Corun]

I've searched Google's archive of this group, and tried the following
things, to no avail. Everytime a form comes up that displays data we get
the "Data Changed by Another User, Copy to Clipboard | Drop Changes".

The background:
* SQL Server 2000 backend, Access 2000 frontend. The Access 2000 backend
was recently upsized to SQL Server 2000.

* My bit fields don't allow Nulls, and have default values of zero. There
are no records anywhere in the system that have a Null value in a bit field.
* Tried changing my bit fields to tinyint.
* Created a new Access database and imported all of the tables, forms, etc.
* Created a relationship in the Access front end, as well as the SQL Server
database.
* Verified that the SQL Server tables have PKs and correct relationships.
* Added timestamps to all my tables, and deleted and relinked those tables
to my Access frontend.
* Installed the latest MDAC (2.8) and Windows 2000 Service Pack 4
* Installed SQL Server Service Pack 2.
* Installed Office 2000 Service Pack 3.
* Checked the network cards against the HCL list, and they're good...
* Watched the hubs and switches for collisions, which there were few.

What am I missing? My client is very upset at this, and it's making my
small programming firm look very bad.

Any more ideas?

Thanks!

// Dave

(Was previously posted as "Data Changed (I know, a million people have
talked about it)")

 

[Kevin3NF]

* Installed SQL Server Service Pack 2.

Any compelling reason not to move to SP3?

 

[Dave Corun]

I appreciate the offer, but a lot of the data in this system is pretty
sensitive.

I've tried removing the relationships, and now I've gone through and changed
Allow Nulls on the sql Server for each Real and Integer field as well. Then
I relinked the tables to Access 2000 and I'm still getting the error.

I also tried removing the reference to DAO and re-added it. ADO isn't in
use at all throughout the system. It's not even referenced.

I've run Windows Update about 1,000,000 more times and verified that I have
the latest version of DAO on both the server and the client (3.6 SP7)

I have to be in their office first thing tomorrow morning to meet w/ the
owners. It's not going to go well unless I get this nailed out tonight!

Can you think of anything else to help me?

Thanks!

// Dave


No, but I'm willing to take a look at it on my machines....SQL 2K, SP3,
Access XP.

If you want to send me the files (SQL backup and Access FE), contact me
offline. If confidentiality is an issue, hire me for $1 to take a look, see
if I can duplicate the issue.

I understand clients getting antsy...we're in the same business.

 

[Kevin3NF]

Call Microsoft? $245 or whatever the incident fee is is well worth saving
the customer.

 

[Alex Dybenko]

try to open table itself and edit there - if error gone - then something
wrong with form, else look at table (maybe some trigger?)

 

[Dave Corun]

No errors when editing the table directly from Access.

No triggers on any table in the SQL Server.

Any more ideas?

Thanks!

// Dave


try to open table itself and edit there - if error gone - then something
wrong with form, else look at table (maybe some trigger?)

 

[Alex Dybenko]

then something wrong with your form (in case it use same table as
recordsource)
try to recreate form, at every step check how it works, first form
properties, then copy controls, then copy code

 

[Dave Corun]

I spent from midnight to 5am on the phone w/ Microsoft and we finally got
the bottom of it.

Recordsets in Access are no more than glorified cursors. What I had was a
form updating a recordset while the other form was opening w/ that same
record.

In my case, I had a Choose Service Order dialog, and it was setting a field
in my table to show which user had the record open. This was while the
Service Order dialog was coming up. Hence the data changed error.

My fix was to remove any recordset updates and create
DBEngine(0)(0).Execute() statements on the actual form that is accessing the
data.

Good stuff, and now that it appears to be fixed for my users, totally worth
the support call.

// Dave


then something wrong with your form (in case it use same table as
recordsource)
try to recreate form, at every step check how it works, first form
properties, then copy controls, then copy code

 

[Dave Corun]

I spent from midnight to 5am on the phone w/ Microsoft and we finally got
the bottom of it.

Recordsets in Access are no more than glorified cursors. What I had was a
form updating a recordset while the other form was opening w/ that same
record.

In my case, I had a Choose Service Order dialog, and it was setting a field
in my table to show which user had the record open. This was while the
Service Order dialog was coming up. Hence the data changed error.

My fix was to remove any recordset updates and create
DBEngine(0)(0).Execute() statements on the actual form that is accessing the
data.

Good stuff, and now that it appears to be fixed for my users, totally worth
the support call.

// Dave



Getting a little OT here, but I spent days looking for an Exchange bug, and
MS PSS found my problem in 7 minutes, and for my money they helped me tweak
4 or 5 other things as well.

I'm not sure they watch this or any other NG that close, especially since
the responses thus far have been on the right track, just not the right
result yet.

 

[Joe Fallon]

I am very impressed with Alex's comments!!
You have done all the standard stuff.
I figured there was no way you would get this resolved w/o PSS.
But Alex called it and PSS just confirmed it.

Glad to hear it works.
PS - consider upgrading to A2002 or 2003 when it comes out.
(2000 was pretty buggy and many developers skipped it.)

 

[Larry Stinson]

If the person who has the "Administrator" permissions for your server (i.e.
the administrator of your server) is using Access on that same server and
another user tries to use Access on that same server (e.g. connecting to an
Access front-end through Citrix on that same server), he will receive the
error you described.

This happened to a client of mine, and the Access programs were not secured
with Access security. So it is strictly a Windows permission issue.

Hope this clarifies things.