Error 3211

[Rjc8513]

I'm attempting to delete a column from a table using "ALTER TABLE...DROP COLUMN..." but I'm getting the error message:

"Run-time error '3211': The database engine could not lock table "WKHR DATA" because it is already in use by another person or process."

Here is the offending code:

Set tdfPlan = Nothing

With rstPlan
If .RecordCount <> 0 Then
.MoveLast
NoRecords = .RecordCount
.MoveFirst
For I = 1 To NoRecords
WeekNo = .Fields("WEEK NO").Value
If Mid(WeekNo, 6, 2) <> "53" Then
dbs.Execute "UPDATE [WKHR DATA] " & _
"SET [WKHR DATA].[WEEK " & Mid(WeekNo, 6, 2) & "B]" & _
"=[" & WeekNo & "]+[WEEK " & Mid(WeekNo, 6, 2) & "B];"
dbs.Execute "ALTER TABLE [WKHR DATA] " & _
"DROP COLUMN [" & WeekNo & "];"
Else
...
End If
.MoveNext
Next I
End If
End With

Thanks.

 

[Crowley16]

well, it looks like something is holding the table locked, check if there are users or processes looking at that table...

--------------------
Procrastinate Now!

 

[Rjc8513]

There are no other users, and I was stepping through the code so there were no forms or reports open.

 

[PHV]

and on which table(s) is based rstPlan ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rjc8513]

Oops! I didn't make that clear.

Set rstPlan=dbs.OpenRecordset("tbl PLAN DATA FIELD NAMES")