create a new table in a backend file

[markjkubicki]

occasionally, as a project evolves, I have a need to add a new table to the back end file; and because each user has their own BE file (don't ask... they just do), I need to create the table with code

all of the other tables in the BE file are already linked.. just not this one that is "missing" in the BE, although a reference to it exists in the front end, that once the table is created will need to be relinked (and with that I'm OK); it's with creating the table that I'm having issues...

here's my code:

Function CreateBEtable_tbeSubmittal(ByVal FilePath As String) As Boolean
' file path incudes the file path of the BE table, including the table name: ( C:/myFile/db_MyDataBE.mdb)

Dim dbs As dao.Database
Dim tdf As dao.TableDef
Dim fld As dao.Field
Dim DbPath As String
Dim TdName As String
On Error Resume Next
    DbPath = FilePath
    TdName = "tbeSubmittal"
    
    'Initialize the table.
    'Set dbs.Connect = ";DATABASE=" & DbPath
    Set dbs = OpenDatabase(DbPath)
    Set tdf = dbs.CreateTableDef(TdName)
    'Specify the fields.
    With tdf
        'AutoNumber: Long with the attribute set.
        Set fld = .CreateField("SubmittalID", dbLong)
        fld.Attributes = dbAutoIncrField + dbFixedField
        .Fields.Append fld
        'Text field
        Set fld = .CreateField("SubmittalDescript", dbText, 255)
        .Fields.Append fld
        'Number field.
        .Fields.Append .CreateField("SubmittalRefNo", dbLong)
        'Date/Time field with validation rule.
        Set fld = .CreateField("SubmittalDate", dbDate)
        .Fields.Append fld
        'Memo field.
        .Fields.Append .CreateField("SubmittalNotes", dbMemo)
        'Hyperlink field: memo with the attribute set.
        Set fld = .CreateField("SubmittalAttachment", dbMemo)
        .Fields.Append fld
   End With
   'Save the table.
    dbs.TableDefs.Append tdf
    'Clean up
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
End Function

totally lost
(but with many thnx in advance),
Mark

[rpeare]

why not something simple like:

Dim db As Database
Dim sSQL As String

Set db = OpenDatabase(CurrentProject.Path & "\backend.accdb")
sSQL = "CREATE TABLE tblTest (FirstName CHAR, LastName CHAR)"
db.Execute sSQL
db.Close

[markjkubicki]

it may be that the problem is that I changed the sequence of the code events, and I am now trying to update a file that is already open (?)

...running with a misc. comment found at the end of this thread: "Although typically not a recommended approach (updating back-end structure from the front-end), it can be done. You could inspire yourself with Allen's code from his site http://allenbrowne.com/func-dao.html#CreateTableDAO and instead of setting db = currentdb(), you could use it to open an instance of your back-end to work with/edit. You have to ensure the BE isn't being used by any other process when you do this."

I wonder if I need to update / add the table to the BE *BEFORE* it gets linked (or break the link; update/add; then relink....)
thoughts?