Change table field attributes using vba

  • Thread starter Thread starter Paul Martin
  • Start date Start date
P

Paul Martin

Hi All,
Not sure if this is the right group for this one, apologies if not...
I need to be able to change an autonumber (key) field in a table to a
straight (still key) number field. Unfortunately, I need to do this using
vba which will be run once when the application is started.
Anybody able to assist in this?
Thanks,
Paul
 
Hi,


Sure ( by the way, your have the same name than the Canadian Prime
Minister... I you want some political advice too ... ;-) )

You have to remove the CONSTRAINT "primary key" of the table:


CurrentDb.Execute "ALTER TABLE tableNameHere DROP CONSTRAINT
PrimaryKey"

You can then add a new UNIQUE constraint, if you wish so:


CurrentDb.Execute "ALTER TABLE tableNameHere ADD CONSTRAINT
gotSomeNameForYourConstraint UNIQUE( list_of_fields )"




Unfortunately, I do not know how to get the constraint name for the
PRIMARY KEY, through SQL only, with JET. It is generally PrimaryKey, but a
table developer can modify it. So, to be sure of that name, in cases you are
not the developer of the db, you have to use DAO. In that case, loop over
all the indexes, here "j" is the running index, and test for Primary
returning true :


If CurrentDb.TableDefs("TableNameHere").Indexes(j).Primary then
' indexes(j) of that table is the primary key
End If


and once spotted, try using the Delete method on the collection, for that
index-name:

Set db = CurrentDb
With db.TableDefs("TableNameHere").Indexes
.Indexes.Delete .Indexes(j).Name ' NOTE: a dot start this
line
End With



and so, in that case, you do not need to use any SQL statement... to remove
the primary key. I prefer the SQL statement to add the constraint, to the
Append method of indexes, on the other hand.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top