Hi,

I am trying to run some code that adds a new field to a table, does some testng, updates the new field, deletes some records, and deletes the appended field.

The problem I have is when I try to delete the new field at the end I get a "run time 3211 The table is locked". Here is the code:

Dim db As Database
Dim rs As Recordset
Dim tdf As TableDef, fld As Field

Set db = CurrentDb

Set tdf = db.TableDefs("COPY OF TARN")

    With tdf
         'Create new fileds in the recordset and assign field types
        .Fields.Append .CreateField("Delete this record", dbBoolean, 1)
    End With
       ' Append field and refresh collection.
         tdf.Fields.Refresh


Set rs = db.OpenRecordset("COPY OF TARN")
    rs.MoveFirst

    Do Until rs.EOF
       If rs.Fields("Diagnosis1").Value = "MEDICAL CONDITION" Then
          'Test Departure Methd
           If Right(rs.Fields("DepartureMethod").Value, 7) = "MA UNIT" Then
              rs.Edit
              rs.Fields("Delete this record").Value = True
              rs.Update
             Else
               If Right(rs.Fields("DepartureMethod").Value, 8) = "(WARD V)" Then
                  rs.Edit
                  rs.Fields("Delete this record").Value = True
                  rs.Update
               End If
           End If
       End If
    rs.MoveNext
    Loop

  'DoCmd.RunSQL "DELETE [COPY OF TARN].*, [COPY OF TARN].[Delete this record]FROM [COPY OF TARN]WHERE ((([COPY OF TARN].[Delete this record])=True));"

  tdf.Fields.Delete ("Delete this record") ' error is on this line!!
  tdf.Fields.Refresh

  Set tdf = Nothing
  Set db = Nothing
  Set rs = Nothing




Any help is appreciated.

Many thanks

Tim

Tim

__________________
Tim

You may be doing this the hard way. You are opening a connection to a database and a table. Once you have opened the table and you have a recordset, the table is locked. I do not know DAO, but I know that you could create a calculated field within the recordset and then use that in a where clause to delete the values that you want. Creating a field can cause too many problems for you.

Or even easier, why not delete the record here

rs.Fields("Delete this record").Value = True

or here

rs.Fields("Delete this record").Value = True



Sal