[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