A Jedi
Board Regular
- Joined
- Mar 23, 2004
- Messages
- 86
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
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