[AccessD] Alter Table

Thanks for pointing me in the right direction Michael and Susan.  I was able
to simplify what I needed with the following:

Private Function CreatePk(objDB As DAO.Database, tdfname As String, pk1 As
String, pk2 As String, pk3 As String)
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index
    Dim fld As DAO.Field
    
    Set tdf = objDB.TableDefs(tdfname)
    Set idx = tdf.CreateIndex("primarykey")
    idx.Primary = True
    idx.Required = True
    idx.IgnoreNulls = False
    
    'this method uses DAO to create primary keys for an existing table and
existing
    'fields within that table
    'case statements are used to pass the primary key parameters
    'multiple primary keys can be assigned
    
    Set fld = idx.CreateField(pk1)
    idx.Fields.Append fld
    
    Set fld = idx.CreateField(pk2)
    idx.Fields.Append fld
    
    If pk3 <> "" Then
        Set fld = idx.CreateField(pk3)
        idx.Fields.Append fld
    End If
    tdf.Indexes.Append idx
    
    
End Function

-----Original Message-----
From: Michael R Mattys [mailto:michael.mattys at adelphia.net] 
Sent: Tuesday, September 02, 2003 4:39 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Alter Table

Hi Anthony,

I usually use DAO for this:

Usage:
Call CreatePKAutoIncr(CurrentDB, "Table","Field",True)

Private Function CreatePKAutoIncr(objDB As DAO.Database,  _
tdfName As String, fldName As String, PK As Boolean)

Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

    objDB.TableDefs.Refresh
    Set tdf = objDB.TableDefs(tdfName)
    Set fld = tdf.CreateField(fldName, dbLong)
        With fld
          .OrdinalPosition = 0
          .Attributes = dbAutoIncrField
        End With
    tdf.Fields.Append fld

    If PK = True Then
        Set idx = tdf.CreateIndex("PrimaryKey")
        With idx
          .Primary = True
          .Required = True
          .Unique = True
        End With

        Set fld = idx.CreateField(fldName)
        idx.Fields.Append fld
        tdf.Indexes.Append idx
    End If
    RefreshDatabaseWindow

Exit_CreatePKAutoIncr:

On Error Resume Next
    Set idx = Nothing
    Set fld = Nothing
    Set tdf = Nothing
Exit Function

Err_CreatePKAutoIncr:

        MsgBox Err.Description, , "Error in Function mTbl.CreatePKAutoIncr"
        Resume Exit_CreatePKAutoIncr

End Function

Michael R. Mattys
Try MattysMapLib for MapPoint at
www.mattysconsulting.com



----- Original Message -----
From: "Randall Anthony" <ranthony at wrsystems.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Tuesday, September 02, 2003 4:18 PM
Subject: RE: [AccessD] Alter Table


> Thanks for that Susan, but I should have been more specific.  I've got a
> table that is already populated with data, and three of the 6 fields need
to
> be turned into a PK.  I used to do this manually, but I'm automating this
> feature so users can do it themselves.  Basically, a remote dbase
> imports/links tables from sql, a make table query creates a new table, the
> link table is deleted, and the PK needs to be set for the new table.
>
> -----Original Message-----
> From: Susan Harkins [mailto:harkins at iglou.com]
> Sent: Tuesday, September 02, 2003 3:57 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Alter Table
>
> More than one way to do it --
>
> SQL:
> ALTER TABLE tbl
> ADD COLUMN fld AUTOINCREMENT
> CONSTRAINT pk PRIMARY KEY
>
> ADOX:
> Function AddAutoNumberField(fldname As String, seedvalue As Long, _
>       incvalue As Long, tblname As String)
> Dim cat As New ADOX.Catalog
> Dim col As New ADOX.Column
> cat.ActiveConnection = CurrentProject.Connection
> With col
>   .Name = fldname
>   .Type = adInteger
>   Set .ParentCatalog = cat
>   .Properties("AutoIncrement") = True
>   .Properties("Seed") = CLng(seedvalue)
>   .Properties("Increment") = CLng(incvalue)
> End With
> cat.Tables(tblname).Columns.Append col
> Set cat = Nothing
> Set col = Nothing
> End Function
>
>
>
>
>
>
> ----- Original Message -----
> From: "Randall Anthony" <ranthony at wrsystems.com>
> To: <accessd at databaseadvisors.com>
> Sent: Tuesday, September 02, 2003 3:23 PM
> Subject: [AccessD] Alter Table
>
>
> > Hi all,
> > I know this has got to be an easy one, but help in XP and 2K is driving
me
> > nuts!!  I just want to alter an A2K table with code and create a primary
> key
> > in it.  Any suggestions?  Thanks a bunch.
> >
> > Randy @ ext. 473
> >
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> >
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

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