Error when Updating a Table with Vb macro

[sarlo00]

I've tried to combine several macros in order to simplify the updating of a table in Access. When I run the macro, I get the following error message:

Run-time error '3052':
File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

I am very new with Vb code so I may have made some basic errors. Would someone please look at the code and find out where I went wrong?

Option Compare Database

Const MAIN_TABLE_NAME = "Shell"

Dim dbMain As DAO.Database
Dim rsMain As DAO.Recordset

Function UpdateShell()

Dim strTemp As String
Dim dblTemp As Double
Dim boolTemp As Boolean

Set dbMain = CurrentDb
Set rsMain = dbMain.OpenRecordset(MAIN_TABLE_NAME, dbOpenDynaset)

rsMain.MoveFirst
Do While Not rsMain.EOF
rsMain.Edit

SignFix "DAY_SUP_PD"
SignFix "QTY_PD"

AllFix "paid", 2
AllFix "copay", 2
AllFix "deduct", 2
AllFix "ingcost", 2
AllFix "ingpaid", 2
AllFix "dispfee", 2
AllFix "OVR_MAX", 2
AllFix "MAC_PEN", 2
AllFix "U&C_AMT", 2

rsMain.Update

rsMain.MoveNext
Loop

rsMain.Close

End Function
Sub AllFix(fieldName As String, intFactor As Integer)

SignFix fieldName
DecimalFix fieldName, intFactor

End Sub
Sub SignFix(fieldName As String)

Dim signFieldName As String

signFieldName = CLM_ST

If rsMain.Fields("CLM_ST") = "S" Then
rsMain.Fields(fieldName) = -1 * rsMain.Fields(fieldName)
End If
End Sub
Sub DecimalFix(fieldName As String, intFactor As Integer)

rsMain.Fields(fieldName) = rsMain.Fields(fieldName) / (10 ^ intFactor)

End Sub


If there is anyone that can help, I would greatly appreciate it.

Brian

 

[sarlo00]

I forgot to add that I am running the UpdateShell() function and also that the error occurs at rsMain.Edit.

Thanks,
Brian

 

[sarlo00]

Is the code too hard to read? I can't download anything from this computer due to security issues so I can't use the html add-in. Or is this just not a solvable problem?

Brian

 

[mdmilner]

I saw this searching on yahoo:

http://www.intersoft.co.nz/support_technicalbulletins/tsb1021.htm

It's basically saying go to the Microsoft Technet here:

http://support.microsoft.com/support/kb/articles/Q173/0/06.ASP
-
About your file though.
Have you ever thought about running a loop through your fields? And explictly referencing columns rather than fieldnames? Using your section here:

rsMain.MoveFirst 
Do While Not rsMain.EOF 
  rsMain.Edit 

  SignFix "DAY_SUP_PD" 
  SignFix "QTY_PD" 

  AllFix "paid", 2 
  AllFix "copay", 2 
  AllFix "deduct", 2 
  AllFix "ingcost", 2 
  AllFix "ingpaid", 2 
  AllFix "dispfee", 2 
  AllFix "OVR_MAX", 2 
  AllFix "MAC_PEN", 2 
  AllFix "U&C_AMT", 2 

  rsMain.Update 
  rsMain.MoveNext 
Loop

How about. This assumes only that your first two fields to edit are the first two columns in the table, and the next batch happen to be 2-10. Most of the time, when I have to perform repetitive operations on multiple fields, I deliberately organize my tables to allow logic like the below to simplify things.

With rsMain
  .MoveFirst
  Do Until rsMain.EOF
    .Edit
    For x = 0 to 1
      SignFix(.fields(x).Name)
    Next x
    For x = 2 to 10
      AllFix(.fields(x).Name,2)
    Next x
    .Update
    .MoveNext
  Loop
End With

Also, I was thinking about your approach. A global dbs/recordset declaration. Why not slide those inside UpdateShell() and put the record updates inside the loop like the below. You'd need to change your subs into functions that return Variables to UpdateShell()

With rsMain
  .MoveFirst
  Do Until rsMain.EOF
    .Edit
    For x = 0 to 1
      .fields(x).Value = SignFix(.fields(x).Name)
    Next x
    For x = 2 to 10
      .fields(x).Value = AllFix(.fields(x).Name,2)
    Next x
    .Update
    .MoveNext
  Loop
End With

Select Case is also a good option to use to control what operation (SignFix or AllFix) is performed based on column numbers. I know I made this answer a lot more complex, but I really don't have much experience with global recordsets and whether using Subroutines to do the field updates is a problem. My gut feeling is that *might* be the problem. It might be that you're throwing up a recordset .Edit and then doing work in the Subroutine...then again, that might be 100% fine. I don't know.

Mike