[AccessD] Create A Table

Rocky,

I would make the table temporarily in the Frontend file.  Then your code can 
export this table to the backend, and delete it from the frontend.  Some 
sample "air" code:

' ----------------
    Dim dbsBE As DAO.Database
    Dim dbs as DAO.Database
    Dim BEPath As String
    Dim AlreadyLinked As Boolean
    Dim AlreadyInBE As Boolean

    Set dbs = DBEngine(0)(0)
    BEPath = Mid(dbs.TableDefs("OneOfMyTables").Connect, 11)

    For Each tdf In dbs.TableDefs
        If tdf.Name = "MyNewTable" Then
            AlreadyLinked = True
            Exit For
        End If
    Next tdf

    If AlreadyLinked Then
        ' nothing to do
    Else
        Set dbsBE = OpenDatabase(BEPath)
        For Each tdf In dbsBE.TableDefs
            If tdf.Name = "MyNewTable" Then
                AlreadyInBE = True
                Exit For
            End If
        Next tdf

        If AlreadyInBE Then
            ' do nothing
        Else
            DoCmd.TransferDatabase acExport, "Microsoft Access", BEPath, 
acTable, "MyNewTable_Local", "MyNewTable", True

            Set rln = dbsBE.CreateRelation("AnotherMyNewTable")
            With rln
                .Table = "AnotherTable"
                .ForeignTable = "MyNewTable"
                .Attributes = dbRelationUpdateCascade + 
dbRelationDeleteCascade
                Set fld = .CreateField("FieldForRelationship")
                fld.ForeignName = "FieldForRelationship"
                .Fields.Append fld
            End With

            dbsBE.Relations.Append rln

        End If
        DoCmd.TransferDatabase acLink, "Microsoft Access", BEPath, acTable, 
"MyNewTable", "MyNewTable"
        DoCmd.DeleteObject acTable, "MyNewTable_Local"

        dbsBE.Close
    End If

    dbs.Close
    Set dbsBE = Nothing
    Set dbs = Nothing

' --------------------

Regards
Steve


--------------------------------------------------
From: "Rocky Smolin" <rockysmolin at bchacc.com>
Sent: Wednesday, January 06, 2010 2:12 PM
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Subject: [AccessD] Create A Table

> Dear List:
>
> I need to create a table in the back end, populate it with a couple of
> fields, set the primary index, set one of the fields as the FK to a PK in
> another table, and link the table to the front end.  I need to do this 
> from
> code behind a form IN the front end.
>
> I could cobble this together from bits of stuff I have, but, among all the
> Access sites that are out there, maybe someone knows of a page with this
> very thing on it, or a close approximation.
>
> Yes?
>
> Or I could go on site to do this which would cost the user an hour of my
> travel time but that might be cheaper than doing the code unless there's
> something that I can put together quickly. (Also hate to go out driving
> around if I can avoid it)
>