modify backend table

[markjkubicki]

going thru the code annotations (which obviously are not very good...) and the notes indicate that I had a problem here before... and now I'm there yet again... did "something" when I was cleaning something else up, and now this don't work... AURGHHH



I'm passing to the code values which all "seem" to be valid, yet when I add watches, the variables: db, td, and fd all have no value... (I suspect that fd is dependent on td, which is dependent on db, and since db is empty ... there must be the problem...



stripped down. the code is:
( and I'll send thanks in advance for any help on this! )

Function AddFieldToTable(ByVal vFilePath As Variant, TblName As String, FldName As String, FldType As Integer, Optional FldPos As Integer, _
Optional FldSize, Optional DefaultValue, Optional FldDes, Optional IsAutoNumber) As Boolean

' vFilePath is being sent as a string: "C:\...\somefile.mdb"

Dim db As Database
Dim Td As TableDef
Dim Fd As Field
Dim p As PropertyOn Error Resume Next

Set db = OpenDatabase(vFilePath)
If Err <> 0 Then
'failed to open back end database
Exit Function
End If

'get table
Set Td = db.TableDefs(TblName)
If Err <> 0 Then
'failed to get table
GoTo Done
End If

'add field and properties

'create field
Set Fd = td.CreateField(FldName, FldType)

'add field to table
td.Fields.Append Fd

********* right here I get err 3211, table in use
********* yet if just prior to that line I run: (SysCmd(acSysCmdGetObjectState, 0, tblName) <> 0), it results in: FALSE

If Err <> 0 Then
'failed to add field - probably already exists
GoTo Done
End If

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

[June7]

I do something similar. In my procedure I set a connection variable. I don't see that in yours.

Dim tdf As TableDef
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim db As DAO.Database

    Set cn = New ADODB.Connection
    'connect to the backend database
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & gstrBasePath & "Data\LabData.accdb'"
    'create the test table
    cn.Execute "CREATE TABLE " & Me.tbxTestNum & " (LabNum text(12) PRIMARY KEY Not Null, method text(30) Not Null);"    'set table link
    Set tdf = CurrentDb.CreateTableDef(Me.tbxTestNum)
    tdf.SourceTableName = Me.tbxTestNum
    tdf.Connect = "; DATABASE=" & gstrBasePath & "Data\LabData.accdb"
    CurrentDb.TableDefs.Append tdf

    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM BuildTable;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
    'must use DAO to set AllowZeroLength property, I don't allow zero length fields and Access defaults to Yes
    Set db = DBEngine.OpenDatabase(gstrBasePath & "Data\LabData.accdb")
    While Not rs.EOF
        If rs!DataField <> "LabNum" And rs!DataField <> "method" Then
            'create field in new table
            cn.Execute "ALTER TABLE " & Me.tbxTestNum & " ADD COLUMN " & _
                        rs!DataField & " " & IIf(rs!DataType = "Boolean", "Bit", rs!DataType) & _
                        IIf(rs!DataType = "Text", "(" & rs!FieldSize & ")", "") & ";"
        End If
        If rs!DataType = "Text" Then
            'change the AllowZeroLength default Yes to No
            db.TableDefs(Me.tbxTestNum).Fields(rs!DataField).AllowZeroLength = False
        End If
        rs.MoveNext
    Wend