Unable to lock file error

[A Jedi]

When I try to execute the code below I get an error saying that the file is in use by another user and can not be locked. It fails where I try to append the new field into the table. I dont see how the file could be open by another process. No one else has access to the file. I rebooted my machine to make sure there was no phantom process running and still I get this error

The code reads a file (no one else has access to the database currently)
creates a collection with all the field names but adds LY to the begining of them (they will be for storage of Last Years data)

Then the new fields in the collection need to be added to the table.

The purpose is to add last years data to a temp file for graphing.

the code follows


Sub AddLYFields()

Dim FieldName As String
Dim I As Integer, NumberFields As Integer
Dim File As String
Dim DB As Database
Dim Target As Recordset
Dim NewField As Field
Dim tbf As TableDef
Dim NewFlds As Collection
Dim fld As Variant

Set NewFlds = New Collection


'Open This years file

Set DB = CurrentDb()
Set Target = DB.OpenRecordset("DR Data")
NumberFields = Target.Fields.Count - 1

' Set up collection with field names in it

For I = 1 To NumberFields
FieldName = ("LY" & Format(Target.Fields(I).Name))
NewFlds.Add FieldName
Next I

' Close data files

DoCmd.Close acTable, "DR Data", acSaveYes
DoCmd.Close acTable, "DR Data LY", acSaveYes
Set DB = Nothing

' Add fields to hold last years data start at 1 to skip date

Set DB = CurrentDb
Set tbf = DB.TableDefs![DR Data]
For Each fld In NewFlds
FieldName = fld
Set NewField = tbf.CreateField(FieldName, dbInteger)
tbf.Fields.Append NewField 'this causes error each time
tbf.Fields.Refresh
Next fld


End Sub

Code ends

 

[Oorang]

just open the database for exclusive access. (Hold down shift when you click on it's icon. If it needs a password hold down shift when you click OK)

 

[A Jedi]

That is how I opened it. I still get the error

 

[A Jedi]

I should probably have mentioned that I am using an XP workstation on a Novell network with office 97

 

[Oorang]

This problem used to plauge me at work. You can try a few different things:
Try deleting the .ldb file. (May not let you.) Another trick is to try "Whohasit", which is a program that tells you who is currently using a file. It's freely available here:
http://www.gadgetfactory.com/whohasit.zip

That will at least let you know who the system THINKS has it. Might aid in troubleshooting effort.

 

[A Jedi]

Well that helped in that I now know I am the only one looking at the database.

It still won't let me add the fields though.

It it posible that it is not closing properly when I open the file to get the fieldnames?

or can I open it differently to grab it exclusively?

 

[Oorang]

Hmm I would try adding in some debug lines or just stepping through to see what line it is halting on.

 

[A Jedi]

Yup did that I put a note in the original code posted above.

it gets hung up on this line of code

tbf.Fields.Append NewField 'this causes error each time

the exact error message is

The database engine couldn't lock table 'DR Data' because it's already in use by another person or process. (Error 3211)

 

[A Jedi]

Ok so this was a total pain to figure out. I still don't understand the why but here is what was causing the error

opening the file to get the fieldnames. I closed it before proceding but for some reason access decided that it was still open.

I split the part that creates the collection into a separage function and it works fine.


here are the routines I made

Sub AddLYFields()

Dim FieldName As String
Dim I As Integer, NumberFields As Integer
Dim File As String
Dim DB As Database
Dim Target As Recordset
Dim NewField As Field
Dim tbf As TableDef
Dim NewFlds As Collection
Dim fld As Variant

Set NewFlds = New Collection

' Get fields collection

Set NewFlds = GetFields("DR Data", "LY")

' Add fields to hold last years data

Set DB = CurrentDb
Set tbf = DB.TableDefs![DR Data]

For Each fld In NewFlds

FieldName = fld
If FieldName <> "LYDate" Then ' dont add a new field for the date

Set NewField = tbf.CreateField(FieldName, dbInteger)

tbf.Fields.Append NewField
tbf.Fields.Refresh

End If

Next fld


End Sub
Function GetFields(File As String, NewPart As String) As Collection

Dim FieldName As String
Dim I As Integer, NumberFields As Integer
Dim DB As Database
Dim Target As Recordset
Dim fld As Variant
Dim tempcol As Collection

'Open This years file
Set tempcol = New Collection
Set DB = CurrentDb()
Set Target = DB.OpenRecordset(File)
NumberFields = Target.Fields.Count - 1

' Set up collection with field names in it

For I = 0 To NumberFields
FieldName = (NewPart & Format(Target.Fields(I).Name))
tempcol.Add FieldName
Next I

' Close data files

DoCmd.Close acTable, "DR Data", acSaveYes
DoCmd.Close acTable, "DR Data LY", acSaveYes
Set DB = Nothing
Set GetFields = tempcol

End Function