Record Locking Sequel 7/Access

[Nancy2]

Help please -- must be simple. Upsized an Access 97 database to SQL 7 - all seems OK (so far) except when we add or update a record in a FORM, it locks the record - and I cannot get it unlocked.

When I add a record directly through enterprise manager, it also locks.

When I update the record through the linked Access table it DOES NOT lock.

And, finally, there is a tabular subform of the continuous form which I am getting the record locking -- adding a record to the sub form DOES NOT Lock.

Please help, really need to get this addressed this weekend.

Thank you,

Nancy

 

[MeanGreen]

You might want to post this in the ACCESS forum as well. On your form, is there any code behind the form? When you edit the table in MS SQL, you say you get a lock. What kind of lock? Were their any triggers added to the table? What was the statement you ran that put the lock on and what do you do to unlock the record? I am not an expert in this area, but will try to see if I can help.

Thank you.

 

[Nancy2]

1) There is quite of bit of code behind the forms - (at least continuous forms are causing the problem) But no ADO or DAO

2) I am getting the error - ..."you and another user are attempting to edit the data at the same time" - #3197 - stating the table is optimistically locked

3) I have not created any triggers -- but will check it on my next visit (I can remotely run the application, but cannot remotely run Enterprise Manager)

4) Adding a record through a table in Access does not lock it, adding through the forms AND adding directly through Enterprise Manager does cause the locks.

Thank you in advance for any assistance.(I will also post on Access forum)

 

[MeanGreen]

I wish I could help you, but I cannot reproduce the problem. Within your old ACCESS 97 tables, did you use a lot of "look up" values within the tables? Maybe the "look up" is where you are crossed up when you upsized. This is just a guess. I say this because you say it locks up with your form and when you are in enterprise manager. Have you tried to insert within Query Analyzer to see if you get back an error? Also, you might start Query Profiler and capture the run to see what is being sent back and forth between your Access and the SQL Server. I would test both the "GOOD" insert from Access, and the "LOCK" from the form while capturing the information. I would also capture the data when I try to insert in Enterprise Manager. These are all just suggestions, but I cannot reproduce the problem so I am guessing at a solution.

Hope this helps.