Problem with simple Recordset Update and possibly Novell Network. 1

[Phooey]

I have a split Access2000 database, and it would appear, as serious problem.&nbsp;&nbsp;The Data is held on a Novell Server.&nbsp;&nbsp;After an import from an ODBC source, which works well, I've had to revert to using code to calculate the discount on the order.&nbsp;&nbsp;The following code works well enough, up to a point, the code then exclaims that <font color=red> Couldn't Update; Currently Locked</font>. <br>I know for a fact that the record is not locked, as there is nothing locking it except for the code below when it's running.&nbsp;&nbsp;The code runs directly after a set of SQL commands which have finished running by the time it gets to this point.&nbsp;&nbsp;What I'm not sure about is if this error is down to the number of records in the recordset, or whether it's a problem caused by a slow server connection.<br><br>Any help would be greatly appreciated.<br><br>The code I'm using is:<br><font color=blue><br>Public Function UpdtDiscount(StrStatus As String)<br>On Error GoTo ErrorBitz<br><br>Dim cnnSEP As ADODB.Connection<br>Dim rstSEP_ALL As New ADODB.Recordset<br>Dim StrSQL As String<br>Dim i As Long<br>Dim RecCount As Long<br>Dim RecNumber As Long<br>Dim CrashTestNo As String<br><br>' Set the local connection<br>Set cnnSEP = CurrentProject.Connection<br>Set rstSEP_ALL = New ADODB.Recordset<br><br>StrSQL = &quot;SELECT * FROM [SEP ALL] WHERE (([Status] = '&quot; & StrStatus & &quot;'));&quot;<br>'now update calculated fields in SEP ALL<br>With rstSEP_ALL<br>&nbsp;&nbsp;&nbsp;&nbsp;RecNumber = 0<br>&nbsp;&nbsp;&nbsp;&nbsp;rstSEP_ALL.Open StrSQL, cnnSEP, adOpenDynamic, adLockOptimistic, adCmdText<br>&nbsp;&nbsp;&nbsp;&nbsp;rstSEP_ALL.MoveLast<br>&nbsp;&nbsp;&nbsp;&nbsp;rstSEP_ALL.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;CrashTestNo = rstSEP_ALL![Enquiry Number] & &quot; / &quot; & rstSEP_ALL![Enquiry Line Number]<br>&nbsp;&nbsp;&nbsp;&nbsp;RecCount = DCount(&quot;[Enquiry Number]&quot;, &quot;SEP ALL&quot;, &quot;[Status] Is Null or [Status] = ''&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;i = SysCmd(acSysCmdInitMeter, &quot;Calculate Discount&quot;, RecCount)<br>&nbsp;&nbsp;&nbsp;&nbsp;Do While Not rstSEP_ALL.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Select Case rstSEP_ALL![Selling Unit]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case &quot;E&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rstSEP_ALL![Discount] = Round(rstSEP_ALL![Amount] * (rstSEP_ALL![Actual Price per Unit] - rstSEP_ALL![System Rec Price per Unit]), 2)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case &quot;L&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rstSEP_ALL![Discount] = Round(rstSEP_ALL![Actual Price per Unit] - rstSEP_ALL![System Rec Price per Unit], 2)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case &quot;T&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rstSEP_ALL![Discount] = Round(rstSEP_ALL![Total Line Weight kg] * (rstSEP_ALL![Actual Price per Unit] - rstSEP_ALL![System Rec Price per Unit]), 2)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case &quot;M&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rstSEP_ALL![Discount] = Round(((rstSEP_ALL![dimension4] * rstSEP_ALL![Amount] * rstSEP_ALL![Actual Price per Unit]) \ 1000) - ((rstSEP_ALL![dimension4] * rstSEP_ALL![Amount] * rstSEP_ALL![System Rec Price per Unit]) \ 1000), 2)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rstSEP_ALL![Discount] = 0<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End Select<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RecNumber = RecNumber + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;i = SysCmd(acSysCmdUpdateMeter, RecNumber)<br>&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;rstSEP_ALL.Close<br>End With<br>cnnSEP.Close<br>Set rstSEP_ALL = Nothing<br>Set cnnSEP = Nothing<br><br>i = SysCmd(acSysCmdRemoveMeter)<br><br>Err_Continue:<br>Exit Function<br><br>ErrorBitz:<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;An error has occurred whilst calculating discount for Status = &quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;StrStatus & Chr(10) & Chr(10) & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;Please report the following message to Help Desk&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;Failed on Enquiry: &quot; & CrashTestNo<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox Err.Description<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.SetWarnings False<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.RunSQL (&quot;UPDATE Source SET Source.[Allow Imports] = True, Source.Username = '';&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.SetWarnings True<br>&nbsp;&nbsp;&nbsp;&nbsp;Resume Err_Continue<br><br>End Function<br></font><br>

 

[Amiel]

Nice code !<br><br>Take a look at the permissions for the folder/directory where the .MDB resides (on the server). <b>Users must have&nbsp;&nbsp;modify, and create permissions in that folder/directory</b>. It's been a while but I seem to recall adding 'Delete' permissions&nbsp;&nbsp;for users for the '.LDB' file as well.<br><br>It's probably simpler to give them all rights including delete permissions on the directory then remove the Delete permissions on the .MDB file. And if necessary, control who can do what with code, perhaps compiling the .MDB into an .MDE file.<br><br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>

 

[harryrich]

I've had a lot of bad luck updating Access databases over networks.&nbsp;&nbsp;When a disk I/O access fails due to a network problem, it is often difficult for an application to handle it properly, and applications in the MS Office suite appear to have some problems in this area.<br><br>When I'm doing mass updates to a database where a failure becomes probable, I make a practice of doing it on a local workstation and then copying the updated database to the network drive for sharing.<br><br>It also seems to be faster to do the updates and then copy up.&nbsp;&nbsp;By the way, I'm not doing this with trivial databases.&nbsp;&nbsp;One of my databases contains a single 440+ megabyte table.<br><br>Alternatively, you could perfect your network .<br><br>Luck,<br><br>Harry<br>