How to Set Primary Key

[steve]

I mostly use macros because my knowledge in vba / sql is very small.
I need to remove the primary key from one field and put it on another.

After picking around on the net I cobbled the following code together
that adds the key to the right field. However this only will add the
key if there is no key already in the table. The table im trying to
change the key on already has one. Can someone show me how to remove
the key first so that I can then run the below code to add it to the
right field. I have found several examples on the net but I cant seem
to get it to work properly.

strSql = "ALTER TABLE [tmptblStructureOnlytblLogfile] " & _
"ADD Constraint PK_tmpTblStructureonlytblLogfile PRIMARY KEY
(ComputerName)"
CurrentDb.Execute strSql, dbFailOnError

Thanks.

 

[Stefan Hoffmann]

hi Steve,

I mostly use macros because my knowledge in vba / sql is very small.
I need to remove the primary key from one field and put it on another.

Why can't you do it manually?

strSql = "ALTER TABLE [tmptblStructureOnlytblLogfile] " & _
"ADD Constraint PK_tmpTblStructureonlytblLogfile PRIMARY KEY
(ComputerName)"

You need to know the constraint name:
ALTER TABLE

DROP CONSTRAINT [NameOfConstraint]



mfG
--> stefan <--

 

[steve]

hi Steve,

I mostly use macros because my knowledge in vba / sql is very small.
I need to remove the primary key from one field and put it on another.

Why can't you do it manually?

strSql = "ALTER TABLE [tmptblStructureOnlytblLogfile] " & _
"ADD Constraint PK_tmpTblStructureonlytblLogfile PRIMARY KEY
(ComputerName)"

You need to know the constraint name:
ALTER TABLE

DROP CONSTRAINT [NameOfConstraint]

Thanks for responding Steve.

I want to do this in a little more automated way. Others are going to
have to do this as well as me.
I'm sorry I dont know what you mean by I need to know the [contraint
name]. The only thing I have seen on the web which looks similar to
yours is where you have [contraint name] I put the name of the field
that Im removing the key from ? I tryed this but then when I try to run
it as follows I get the error Run-time error '3755' CHECK contraint
'UserName' does not exist . What am I doing wrong ?

strSql = "ALTER TABLE [tmptblStructureOnlytblLogfile] DROP CONSTRAINT
[UserName]"
CurrentDb.Execute strSql, dbFailOnError

Regards

(7willie)

 

[steve]

hi Steve,

I mostly use macros because my knowledge in vba / sql is very small.
I need to remove the primary key from one field and put it on another.

Why can't you do it manually?

strSql = "ALTER TABLE [tmptblStructureOnlytblLogfile] " & _
"ADD Constraint PK_tmpTblStructureonlytblLogfile PRIMARY KEY
(ComputerName)"

You need to know the constraint name:
ALTER TABLE

DROP CONSTRAINT [NameOfConstraint]

Thanks for responding Steve.

I want to do this in a little more automated way. Others are going to
have to do this as well as me.
I'm sorry I dont know what you mean by I need to know the [contraint
name]. The only thing I have seen on the web which looks similar to
yours is where you have [contraint name] I put the name of the field
that Im removing the key from ? I tryed this but then when I try to run
it as follows I get the error Run-time error '3755' CHECK contraint
'UserName' does not exist . What am I doing wrong ?

strSql = "ALTER TABLE [tmptblStructureOnlytblLogfile] DROP CONSTRAINT
[UserName]"
CurrentDb.Execute strSql, dbFailOnError

Regards

(7willie)

 

[steve]

hi Steve,

I mostly use macros because my knowledge in vba / sql is very small.
I need to remove the primary key from one field and put it on another.

Why can't you do it manually?

strSql = "ALTER TABLE [tmptblStructureOnlytblLogfile] " & _
"ADD Constraint PK_tmpTblStructureonlytblLogfile PRIMARY KEY
(ComputerName)"

You need to know the constraint name:
ALTER TABLE

DROP CONSTRAINT [NameOfConstraint]

Thanks for responding Steve.

I want to do this in a little more automated way. Others are going to
have to do this as well as me.
I'm sorry I dont know what you mean by I need to know the [contraint
name]. The only thing I have seen on the web which looks similar to
yours is where you have [contraint name] I put the name of the field
that Im removing the key from ? I tryed this but then when I try to run
it as follows I get the error Run-time error '3755' CHECK contraint
'UserName' does not exist . What am I doing wrong ?

strSql = "ALTER TABLE [tmptblStructureOnlytblLogfile] DROP CONSTRAINT
[UserName]"
CurrentDb.Execute strSql, dbFailOnError

Regards

(7willie)

 

[steve]

hi Steve,

I mostly use macros because my knowledge in vba / sql is very small.
I need to remove the primary key from one field and put it on another.

Why can't you do it manually?

strSql = "ALTER TABLE [tmptblStructureOnlytblLogfile] " & _
"ADD Constraint PK_tmpTblStructureonlytblLogfile PRIMARY KEY
(ComputerName)"

You need to know the constraint name:
ALTER TABLE

DROP CONSTRAINT [NameOfConstraint]

Thanks for responding Steve.

I want to do this in a little more automated way. Others are going to
have to do this as well as me.
I'm sorry I dont know what you mean by I need to know the [contraint
name]. The only thing I have seen on the web which looks similar to
yours is where you have [contraint name] I put the name of the field
that Im removing the key from ? I tryed this but then when I try to run
it as follows I get the error Run-time error '3755' CHECK contraint
'UserName' does not exist . What am I doing wrong ?

strSql = "ALTER TABLE [tmptblStructureOnlytblLogfile] DROP CONSTRAINT
[UserName]"
CurrentDb.Execute strSql, dbFailOnError

Regards

(7willie)

 

[Stefan Hoffmann]

hi,

I'm sorry I dont know what you mean by I need to know the [contraint
name].

When using a SQL statement to remove the primary key, you need to know
the name of the constraint who represents the primary key.

Take look at

CurrentDb.TableDefs.Item("YourTable").Indexes

You can use the DAO object model to change your keys.

mfG
--> stefan <--