Access Linked To SQL Server (Lock Problems)

Last Post 20 Sep 2006 10:43 AM by lmf232s. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
lmf232s
New Member
New Member

--
20 Sep 2006 07:39 AM
We have a web site and the database for this web site resides in sql server.
The marketing dept is allowed to maintain this database but instead of giving them EM we installed
Access on there machine and allow them to hit the tables through linked tables.

The problem is that the user can make an initial change and it will take and accept the changes but if the user then tries to edit this row again, they recieve the message that the record is being edited by another user. That other uses is the same user trying to make the change again.

Im not sure how to proceed or even what else to do. We cant seem to get this row unlocked and the only thing that works is rebooting the server which were not going to do. (personally i dont think they should have db access any ways)

Any way if anyone has any ideas of what to look at to try to correct this problem that would be great.

Thanks.
whynot
New Member
New Member

--
20 Sep 2006 08:19 AM
This is the beauty of Access. When Access application access linked table, it does not always share the same connection. Two forms access the same table may have more connection sessions, therefore, one can lock oneself. To avoid this, try to limit the table access in same place. Sometimes, limit the use of bound data field.
lmf232s
New Member
New Member

--
20 Sep 2006 10:43 AM
Well i found a fix that so far is working correctly.

I read somewhere that (and i may mix this up and not word it correctly) sql server and access save data differently. Say for a float or a decimal, they round them slightly different wich causes different values.

Because of this inconsistency of the values, sql server or access thinks that the values have changed since you last accessed that row. This is because it looks at each row compares them and if they are the same the it accepts the update else you get a lock error that another uses has this record opened.

The remedy was to add a column to the sql server db of datatype TIMESTAMP. Apparently if there is a TIMESTAMP datafield in the table, sql server and access will only compare the TIMESTAMP and ignore the rest of the fields. As long as the TIMESTAMP is the same between the 2 it will accept the changes with no problem.

I gave this a shot and so far so good. It fixed the currently problem that we were having as soon as we updated the table in access and i can not create the problem yet.

I tested this out on a few more tables and as i tried to change the data i would receive the lock error. I then added the TIMESTAMP datatype field to that table in sql and then it removed the lock from access and allowed me to update the data.

What do you think about this method????

Whats up russellb!!!! Long time!!!! Been very busy!!!!
akempfDOL
New Member
New Member

--
17 Apr 2007 04:47 PM
are you kidding me?

Access causes various unpredictable locking problems

much much better to use Access Data Projects instead of silly little MDB files


-Aaron
ADP Uber Alles
lmf232s
New Member
New Member

--
01 Jun 2007 09:53 AM
No were not kidding you!

I will agree that the Access Data Project is the way to go as it has completely eliminated all locking problems that we were experiencing.
You are not authorized to post a reply.

Acceptable Use Policy