Can't figure out this error.... (1 Viewer)

[Mendel]

I'm running an audit program which is analyzing 5 sets of data consequtively. In short I'm running one particular function in which on the second set it gives me an error:

"The database engine could not lock the table 'ADJSYSCorrections' because it is already in use by another person or process."

It appears that it is still opened somehow from when the audits ran through the 1st set. Here is how my code is written:

Function TestADJSYS(ADJSYSMaster As String, ADJSYS As String)

Dim dbs As Database
Dim recADJSYS As Recordset
Dim recADJSYSMaster As Recordset
Dim tdf As TableDef
Dim fld As Field
Dim FieldChanged As Boolean
Dim Check1, Check2, Check3, Check4, Check5, Check6, Check7, Check8, _
Check9, Check10, Check11, Check12 As Boolean
Dim Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, _
Field9, Field10, Field11, Field12 As String
Dim m As Integer

Set dbs = CurrentDb

Set recADJSYSMaster = dbs.OpenRecordset(ADJSYSMaster)
Set recADJSYS = dbs.OpenRecordset(ADJSYS)
Set tdf = dbs.TableDefs(ADJSYSMaster)

Set recADJSYSCorrections = Nothing

dbs.TableDefs.Delete "ADJSYSCorrections"
dbs.Execute ("CREATE TABLE ADJSYSCorrections(Field1 TEXT, Field2 TEXT, Field3 TEXT, Field4 TEXT, Field5 TEXT, Field6 TEXT, Field7 TEXT, Field8 TEXT, Field9 TEXT, Field10 TEXT, Field11 TEXT, Field12 TEXT);")

recADJSYSMaster.MoveFirst
recADJSYS.MoveFirst

Do Until recADJSYSMaster.EOF
m = 0
10 For Each fld In tdf.Fields
If m = 0 Then Field1 = recADJSYSMaster(fld.Name)
If recADJSYSMaster(fld.Name) <> recADJSYS(fld.Name) Then
If m = 0 Then
recADJSYS.MoveNext
If recADJSYS.EOF Then
dbs.Execute ("INSERT INTO ADJSYSCorrections(Field1)" & "VALUES('" & "*" & Field1 & "*" & "');")
recADJSYSMaster.MoveNext
recADJSYS.MoveFirst
End If
GoTo 10
ElseIf m = 1 Then
Check2 = True
Field2 = recADJSYSMaster(fld.Name)
ElseIf m = 2 Then
Check3 = True
Field3 = recADJSYSMaster(fld.Name)
ElseIf m = 3 Then
Check4 = True
Field4 = recADJSYSMaster(fld.Name)
ElseIf m = 4 Then
Check5 = True
Field5 = recADJSYSMaster(fld.Name)
ElseIf m = 5 Then
Check6 = True
Field6 = recADJSYSMaster(fld.Name)
ElseIf m = 6 Then
Check7 = True
Field7 = recADJSYSMaster(fld.Name)
ElseIf m = 7 Then
Check8 = True
Field8 = recADJSYSMaster(fld.Name)
ElseIf m = 8 Then
Check9 = True
Field9 = recADJSYSMaster(fld.Name)
ElseIf m = 9 Then
Check10 = True
Field10 = recADJSYSMaster(fld.Name)
ElseIf m = 10 Then
Check11 = True
Field11 = recADJSYSMaster(fld.Name)
ElseIf m = 11 Then
Check12 = True
Field12 = recADJSYSMaster(fld.Name)
End If
FieldChanged = True
End If
m = m + 1
If m = 12 Then GoTo 30
Next fld

30 If FieldChanged = True Then
dbs.Execute ("INSERT INTO ADJSYSCorrections(Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12) " & "VALUES('" & Field1 & "', '" & Field2 & "', '" & Field3 & "', '" & Field4 & "', '" & Field5 & "', '" & Field6 & "', '" & Field7 & "', '" & Field8 & "', '" & Field9 & "', '" & Field10 & "', '" & Field11 & "', '" & Field12 & "');")
End If

FieldChanged = False
Field1 = ""
Field2 = ""
Field3 = ""
Field4 = ""
Field5 = ""
Field6 = ""
Field7 = ""
Field8 = ""
Field9 = ""
Field10 = ""
Field11 = ""
Field12 = ""

recADJSYSMaster.MoveNext
recADJSYS.MoveFirst
Loop

20 Set recADJSYSMaster = Nothing
Set recADJSYS = Nothing
dbs.Close

End Function


-I apoligize for displaying code cause I know it's hard to look through someone else's work. I can't figure out why its not working. If I run the audit for each set seperatly then it works. But when I try to run the audits together back-to-back it gives me that error. I'd appreciate any thoughts on this thanks.

[This message has been edited by Mendel (edited 02-05-2002).]

 

[Emohawk]

Could it be these three lines?

Set recADJSYSMaster = Nothing
Set recADJSYS = Nothing
dbs.Close

I always do my clean up code the other way around.

recADJSYSMaster.Close
recADJSYS.Close
Set dbs = Nothing

My understanding is that the recADJSYSMaster and recADJSYS are just references to the recordset objects and by assigning them to Nothing you aren't actually closing just dereferncing them so the lock still exists. Just a thought, not 100% certain though.