 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / December 2006How to Set Primary Key
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. 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 [table] DROP CONSTRAINT [NameOfConstraint] mfG --> stefan <-- > hi Steve, > [quoted text clipped - 8 lines] > ALTER TABLE [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) > hi Steve, > [quoted text clipped - 8 lines] > ALTER TABLE [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) > hi Steve, > [quoted text clipped - 8 lines] > ALTER TABLE [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) > hi Steve, > [quoted text clipped - 8 lines] > ALTER TABLE [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) 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 <-- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.