Form Fields Sometimes Change To Another Record On Enter; Backend Is MS SQL Server

Question

Hi everyone.

I've got an Access 2000 frontend running in the Access 2010 runtime that I recently migrated the backend to MS SQL Server 2010.

Shortly after the migration the few staff that heavily use the database started notifying me that records would "randomly change" while they were typing in new (or otherwise altering old) information into the main form. After weeks of not seeing the behavior myself I finally caught what is happening. Somewhat randomly, as far as I can tell, this form will exhibit an "On Enter" glitch—when a user clicks on a field data from _another record_ will show up. Very, very odd.

Any ideas?

The client computers exhibiting the issues are running Windows XP SP3 with the SQL 2008 Native Client installed (no Windows XP support in the 2012 client apparently).

Thanks,

Derek

Answer 1

I don't know what's going on and without a strong reproducible scenario I doubt we can make much headway, but here is a suggestion. Btw, I assume you have an MDB with ODBC-attached tables.

* Make sure each table you want to edit has at least a Primary Key, and if you can swing it also a TIMESTAMP (now called ROWVERSION) column.

* Make sure you are up to date on service packs, including Office service packs.

* If all these users have the A2010 runtime, why not upgrade your FE to A2010? As it stands, you are 4 versions behind.

---

-Tom. Microsoft Access MVP

Answer 2

Tom,

Thanks for taking the time to reply.

We are in the process of upgrading all databases to the Access 2010 format (part of this project), however this is the first database we're having a go at, so it'll be a bit before we can upgrade Access. One of the databases has some integration features that simply don't work in Access 2010 and need to be changed/rewritten. Trust me when I say Office 2010/2012 can't happen quick enough. :-)

I'm in the process of removing antivirus and tracking software from the computers in order to rule out (or confirm) external hooks as an issue.

The main table being updated does have both a primary key and a timestamp field, but I will review the other tables as well today/tomorrow.

I'll respond with an update shortly.

Thanks,

Derek

Answer 3

Hi Derek,

Now do you have any updates on the situation?

Have a good day.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

Answer 4

Yoyo,

I had removed antivirus and the tracking software from the two workstations late Tuesday. As of this morning the problems were still occurring, although Wednesday passed with no issue.

So as not to further aggravate the staff my hand was forced and I reinstalled Access 2000.

I'm now waiting to see what happens, but it may be a day or two between responses. The issue often doesn't occur right away or with any apparent regularity.

Thank you for taking the time to respond. I'll update shortly.

-Derek

Answer 5

Well, it's happening with Access 2000 as well.

Answer 6

Update:

I've been running SQL Server Profiler all day to see what I could track down. I had a staff member attempt to update a record (ID 32221) and I saw an UPDATE query with all the new information come in with a completely incorrect ID, 4324.

exec sp_executesql N'UPDATE "dbo"."tbl_ReferralInfo" SET "Notes"=@P1  WHERE "ReferralNumber" = @P2 AND "SSMA_TimeStamp" = @P3',N'@P1 ntext,@P2 int,@P3 binary(8)',N'REDACTED',4324,0x0000000000076083

What gives?

-Derek