[AccessD] Create A Table

Good approach I think.  Thanks.

R 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Schapel
Sent: Tuesday, January 05, 2010 7:13 PM
To: Access Developers discussion and problem solving
Subject: Re: [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)
>
 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com