[AccessD] Alter Table

Paula Wright paulawright at boddienoell.com
Tue Sep 2 15:31:52 CDT 2003


I did something similar once in Access 2.0 using a macro and send keys.  I
get around this problem by running delete and append queries rather than a
make-table.

-----Original Message-----
From: Randall Anthony [mailto:ranthony at wrsystems.com] 
Sent: Tuesday, September 02, 2003 4:19 PM
To: 'Access Developers discussion and problem solving'
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



More information about the AccessD mailing list