ADOX and SQL Server... 2

[lachie]

I'm currently attempting to modify an existing table's schema. I've created a table, copied data into it, set a bunch of indexes and am in the process of wanting to set a primary key and set one of the fields to use the identity property.

VB code example...

Set pk = New Key
pk.Name = "PrimaryKey"
pk.Type = adKeyPrimary

For Each SourceColumn In SourceIndex.Columns
pk.Columns.Append SourceColumn.Name
Next SourceColumn

TargetTable.Keys.append pk


Can anybody help with this?

Thanks,

Lachlan.

 

[ThomasEU]

Another way could be simply to execute the equivalent TSQL code against your connection to the database, i.e. something like this:

ALTER TABLE

WITH NOCHECK ADD
CONSTRAINT [PK_CONSTRAINTNAME] PRIMARY KEY NONCLUSTERED
(
[FIELD1],
[FIELD2]
) ON [PRIMARY]
GO

Thomas

 

[lachie]

Thanks for the info - I think this may be the way to do it. I've had some other problems with the ADOX / SQL Server OLE DB combination.

I'm not entirely convinced that ADOX works as well as microsoft says it does.

Thanks for your help,

lachlna