How to create composite key in a table using VB.NET?

[sivakumar mariappan]

Hi, I have created a table in ms-access using VB.NET like below. ADOXtable.Name = "ACN_CHARX_INSTANCE" ADOXtable.Columns.Append("LOGICAL_DB_NAME", ADOX.DataTypeEnum.adVarWChar, 32) ADOXtable.Columns.Append("DIM_NAME", ADOX.DataTypeEnum.adVarWChar, 32) ADOXtable.Columns.Append("CHARX_NAME", ADOX.DataTypeEnum.adVarWChar, 128) ADOXtable.Columns.Append("CHARX_TABLE", ADOX.DataTypeEnum.adVarWChar, 128) ADOXtable.Columns.Append("CHARX_VALUE_COL", ADOX.DataTypeEnum.adVarWChar, 128) ADOXtable.Columns.Append("VALUES_UNIQUE", ADOX.DataTypeEnum.adInteger) ADOXtable.Columns.Append("CHARX_ORDER_COL", ADOX.DataTypeEnum.adVarWChar, 128) ADOXtable.Columns.Append("CHARX_ORDER", ADOX.DataTypeEnum.adInteger) ADOXtable.Columns.Append("CHARX_NUMERIC_COL", ADOX.DataTypeEnum.adVarWChar, 128) ADOXtable.Columns.Append("BITMAP_COL", ADOX.DataTypeEnum.adVarWChar, 128) cat.Tables.Append(ADOXtable) After that i gave a primary key like below, ADOXtable.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "LOGICAL_DB_NAME") But now i want to add composite key for this table..I added the code like the below.. ADOXtable.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "LOGICAL_DB_NAME", "DIM_NAME", "CHARX_NAME") But after executing this code, the key is allocated for Logical_Db_name field only. Please share your ideas about how to create composite key using VB.NET... Thanks, Sivakumar.M.

[Dave Kreskowiak]

sivakumar.mariappan wrote:

ADOXtable.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "LOGICAL_DB_NAME", "DIM_NAME", "CHARX_NAME")

Instead of putting all the column names in one line, I think you have to call Append again with the 2nd column, and again for the 3rd that you want to add as part of the key.

ADOXtable.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "LOGICAL_DB_NAME")
ADOXtable.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "DIM_NAME")
ADOXtable.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "CHARX_NAME")

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007, 2008

[sivakumar mariappan]

Thanks for your reply...if i give like below as u advised, ADOXtable.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "LOGICAL_DB_NAME") ADOXtable.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "DIM_NAME") ADOXtable.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "CHARX_NAME") ADOXtable.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "CHARX_TABLE") Exception raised... {"Exception from HRESULT: 0x80040E99"} Data: {System.Collections.ListDictionaryInternal} ErrorCode: -2147217767 HelpLink: Nothing InnerException: Nothing Message: "Exception from HRESULT: 0x80040E99" Source: "Interop.ADOX" StackTrace: " at ADOX.Keys.Append(Object Item, KeyTypeEnum Type, Object Column, String RelatedTable, String RelatedColumn) at IWSSample.IWSSample.CreateAccessDatabaseandtables(String DatabaseFullPath) in C:\LEGO\June19\IWSSample.vb:line 1021" TargetSite: {System.Reflection.RuntimeMethodInfo} Thanks, Sivakumar.M.

[Dave Kreskowiak]

Well, I didn't say I tested it. I said I think you may have to do something like that. Try reading the documentation on ADOX's Key.Append method. You may have to supply other information to the Append method, like foreign table names and columns.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007, 2008

[riced]

Something like this?

key1 = New ADOX.key
key1.Name = "PrimaryKey"
key1.Type = ADOX.KeyTypeEnum.adKeyPrimary
key1.Columns.Append "LOGICAL_DB_NAME"
key1.Columns.Append "DIM_NAME"
key1.Columns.Append "CHARX_NAME"

ADOXtable.Keys.Append key1

Note the similarity with yesterdays index question - have a look at the ADOX object library. :)

Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis

[sivakumar mariappan]

Superb.....It is working fine now...Thanks a lot...