unable to append a feild to a backend table

[markjkubicki]

I got some code to add a field to a backend table, and (although I've got the impression that sometimes it does work?) it does not; and I'm wondering if I've simply got something switched off than needs to be on...

everything seems to work fine, except the very last snippet (marked with '**** for an easy find)
the unedited code was sourced from: http://www.aislebyaisle.com/access/vba_backend_code.htm

-----------------------------------


AddFieldToTable("tbeProjectInfo", "CSIFormat", dbBoolean, , , , "use CSI Master Spec Format at schedule and cuts")
-----------------------------------
Function AddFieldToTable(ByVal TblName As String, FldName As String, FldType As Integer, Optional FldPos As Integer, _
Optional FldSize, Optional DefaultValue, Optional FldDes, Optional IsAutoNumber) As Boolean
Dim db As Database
Dim DbPath As Variant
Dim Td As TableDef
Dim Fd As Field
Dim p As Property
On Error Resume Next

'get back end path of linked table
DbPath = DLookup("Database", "MSysObjects", "Name='" & TblName & "' And Type=6")
Set db = OpenDatabase(DbPath) 'if linked table
' <...>

'get table
Set Td = db.TableDefs(TblName)
' <...>

'add field and properties
'MsgBox IsObjectOpen(TblName, 0) & " : " & TblName & " : " & FldName
With Td
'create field
If FldType = dbText And Not IsMissing(FldSize) Then
Set Fd = .CreateField(FldName, FldType, FldSize)
Else
Set Fd = .CreateField(FldName, FldType)
End If

'position (0 is first position)
If Not IsMissing(FldPos) Then
Dim Num As Integer
For Num = 0 To FldPos - 1
Td.Fields(Num).OrdinalPosition = Num
Next
For Num = FldPos To .Fields.Count - 1
Td.Fields(Num).OrdinalPosition = Num + 1
Next
End If

'if IsAutoNumber
If Not IsMissing(IsAutoNumber) Then
If IsAutoNumber Then
Fd.Attributes = 17
End If
End If

' *****************
' this is where the error occurs

'add field to table
.Fields.Append Fd
If Err <> 0 Then
'failed to add field
GoTo Done
End If
' *****************

'<...>

End With
AddFieldToTable = True 'defaults to false if it fails to get here

'clean up
Done:
Set Fd = Nothing
Set Td = Nothing
If Not db Is Nothing Then db.Close
Set db = Nothing

End Function


any thought... with many thanks in advance,
Mark

[John_G]

Hi -

What's the error message you get?

[markjkubicki]

3211 - could not lock the table because it is already in use by...

my code is in the onopen event of a form that will use that table as its data source (no other forms are yet open)
Should I be adding a line of code to close the table?