<div>
Are you updating a record that is open on the form? &nbsp;The code you posted is unnecessary if this is a bound form. You would just use:<br />
Me.itemamt = 33<br />
<br />
to update the current record. &nbsp;You would never use an update query.<br />
<br />
&nbsp;It is also showing hardcoded values so it isn't particularly useful. &nbsp;If that isn't the actual code, you might have removed something relevant.
</div>


<div>
no the form is unbound and i am getting values from the form but they are pretty much what i did. &nbsp;If i change this to ADO instead of DAO then everything seems to work. &nbsp;Its almost like there is a bug in DAO that is keeping the lock on the records even though i have closed the recordset and set it to nothing.
</div>


<div>
I'm surprised you are not getting an error that says something about adding the dbSeeChanges option as a value in the Options argument of the recordset.<br />
<br />
1. &nbsp;Does the table contain a primary key?<br />
2. &nbsp;Does the table contain a column with a RowVersion (Timestamp) data type? &nbsp;(this is not a date/time field).<br />
<br />
I would strongly encourage you to modify all tables in SQL Server which do not have both a PK and a Timestamp to ensure that they have both.<br />
<br />
Dale
</div>


<div>
You left out all the code that would allow us to identify whether you are using DAO or ADO.<br />
<br />
Access is a RAD (Rapid Application Development) Tool. &nbsp;If you are not using the RAD features of Access such as bound forms, there is absolutely no reason to use Access as a FE at all. &nbsp;You are much better off using some other development platform that doesn't come with the baggage that Access carries in order to be RAD. &nbsp;You have saddled yourself with a tool that certain &quot;real&quot; programmers consider inferior because it can't twiddle bits or let you build graphic games and have gotten nothing for it.
</div>


<div>
@PatHartman:
<blockquote>
You left out all the code that would allow us to identify whether you are using DAO or ADO.
</blockquote>
<blockquote>
set db = currentdb
</blockquote>
OP is using DAO.<br />
<br />
@mitai:<br />
What about an update query instead of building a recordset ?
<pre><code class="code-10 nolinks" id="code-20-42715899-1"><span>Dim sql As String</span>
<span>sql = vbNullString</span>
<span></span>
<span>sql = sql &amp; &quot;UPDATE inventory&quot; &amp; vbCrLf</span>
<span>sql = sql &amp; &quot;Set itemamt = 33&quot; &amp; vbCrLf</span>
<span>sql = sql &amp; &quot;WHERE customerid = 1&quot; &amp; vbcrlf</span>
<span>sql = sql &amp; &quot;  AND productid  = 10;&quot;</span>
<span></span>
<span>Dim db As DAO.Database</span>
<span>Set db = CurrentDb</span>
<span></span>
<span>db.Execute sql, dbFailOnError</span>
</code></pre>
</div>


<div>
<blockquote>
this code works fine except when it does the update, sql server adds a key lock on the database and doesn't release it 
</blockquote>
<br />
&nbsp;What do you mean by a &quot;key lock&quot;, and what is the issue your running into?<br />
<br />
&nbsp;There is nothing wrong with the code you posted, although as Dale suggested, I would have written it as an SQL Update rather than opening a recordset, which is extra overhead. <br />
<br />
&nbsp;Still, it should work without issue.<br />
<br />
Jim.
</div>


<div>
Pat, i agree that it is a rapid tool and that in a perfect world that would be the case. &nbsp;I have inherited this app and am tasked with moving it to sql server and i don't have the time for a complete rewrite otherwise i would do it in .net or something else. &nbsp;I have converted all the tables to sql using the sql server tools and if i change that same code to use ADO it works well and lets go of the lock on the sql table.<br />
<br />
Jim, when i do the .update statement sql server creates multiple locks on the inventory table.<br />
<br />
it creates a page lock and it creates a key lock as well, i know sql server pretty well but i am not sure why it needs both but it does create both. &nbsp;if i use ADO then once i do the .close of the recordset both of the locks disappear and everything is back to normal. &nbsp;If i use dao, the locks persist even after i have closed the recordset and set the recordset to nothing.<br />
<br />
It's almost like this is a bug in dao or there is something different that i am not doing to allow those locks to release.<br />
<br />
Pat all the tables have a PK and i do believe they have the timestamp as well but i will check into that.<br />
<br />
Again this is a large application, i would just recode it to use ADO and this may be my best option but i would hate to do this work to find out there was something i missed and it would work using the existing DAO code. &nbsp;Any other suggestions?<br />
<br />
thanks
</div>


mitai

<div>
My understanding is that Access does not influence locking on the server regardless of your form settings so whether you are using DAO or ADO should not matter. &nbsp;Are you sure the form is not actually bound? &nbsp;That could cause a conflict if you are sitting on a bound record and then update it via DAO or an update query.<br />
<br />
If the application is currently using Jet/ACE, is it not also using bound forms? &nbsp;It isn't unheard of but it would be extremely rare to see Jet/ACE as the BE and unbound forms as the FE. &nbsp;If you already have bound forms, use them.<br />
<br />
PS, DAO is optimized for Jet/ACE. &nbsp;If you are going to the trouble of rewriting the app to use unbound forms, you should also convert to ADO. &nbsp;The only reason for sticking with DAO when you use bound forms is because bound forms use DAO recordsets and you can get into trouble mixing and matching DAO and ADO if you are not careful.
</div>


<div>
<div class="content wysiwyg-content">
I have a Microsoft access application that i am trying to port to use the sqlserver express database in the back end instead of tables within the application. &nbsp;I changed all of the tables in the access database to attached tables and for the most part everything works tickity boo.<br />
<br />
I do have an issue on some of my code where i am trying to update a field.<br />
<br />

<pre><code class="code-1 nolinks" id="code-10-29123229-1"><span>set db = currentdb</span>
<span>set rst = db.openrecordset(&quot;select * from  inventory where customerid = 1 and productid = 10&quot;,dbopendynaset, dbseechanges)</span>
<span>with rst</span>
<span>      .edit</span>
<span>     .fields(&quot;itemamt&quot;) = 33</span>
<span>    .update</span>
<span>    .close</span>
<span>end with</span>
<span>set rst = nothing</span>
</code></pre>
<br />
this code works fine except when it does the update, sql server adds a key lock on the database and doesn't release it <br />
<br />
later in my code i go to update something else and whey i go to open the recordset (querying on the inventory table again) it locks and eventually returns an error<br />
<br />
<br />
Has anyone else seen this behavour with linked sql server tables? &nbsp;is there a way to force the lock to disappear when i close the recordset?
</div>
</div>


I have a Microsoft access application that i am trying to port to use the sqlserver express database in the back end instead of tables within the application.  I changed all of the tables in the access database to attached tables and for the most part everything works tickity boo.

I do have an issue on some of my code where i am trying to update a field.

(CODE)

this code works fine except when it does the update, sql server adds a key lock on the database and doesn't release it 

later in my code i go to update something else and whey i go to open the recordset (querying on the inventory table again) it locks and eventually returns an error


Has anyone else seen this behavour with linked sql server tables?  is there a way to force the lock to disappear when i close the recordset?

issue with access application port to sql server

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.