Programmatically set and read relationships in Access using ADO or oth

[sorken]

Hi,

I am interested in using a program to modify relationships that are defined in an access database, for a database update script.

Example:

I have 2 tables:

ValidNumbers:
fields: ID, Num

and

Numbers: ID, Number

ValidNumber.Num has a one to many relationship to Numbers.Number meaning that any number in Numbers.Number must also exist in ValidNumber.Num

Can I remove this relationship using ADO or other data interface?

 

[RoyVidar]

The challenge with removing relationships through ADO, is that you need to find it's name. When you know the name, you can simply execute a smallish DDL to drop the relationship.

I think that if you use the OpenSchema method with adSchemaForeignKeys, you can return that. Check out the following:

[tt]dim rs as adodb.recordset
dim strTable as string
' cn - ADO connection to your db
strTable = <YourTableOnThe_M_Side -> Numbers?>
set rs = cn.openschema(adschemaforeignkeys, _
array(empty, empty, empty, empty, empty, strTable))
if not rs.eof then
cn.execute "ALTER TABLE " & strTable & _
"DROP CONSTRAINT " & rs.fields("FK_NAME").value,, _
adcmdtext + adexecutenorecords
end if[/tt]

Note - I've just played with these methods, not really used them, you might want to check out more of the SchemaEnums.

Similarly, you can create relationships also with DDL, for instance reimplementing the above relationship.

[tt]cn.execute "ALTER TABLE " & strTable & _
"ADD CONSTRAINT FK_Numbers_ValidNumbers " & _
"FOREIGN KEY (Number) " & _
"REFERENCES ValidNumber (Num)",, _
adcmdtext + adexecutenorecords[/tt]

No code is tested, typos are entirely mine ;-)

More on DDL, you can find in for instance

http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx

(there are links to further articles within)

This way, you can even do updates through VBScript.

Welcome to the Tek-Tips - to get the most out your membership it, here's a faq faq222-2244.

Good luck!

Roy-Vidar

 

[sorken]

Hi Roy,

Thanks for the tips. They got me started, and I was able to remove the Access relationships. I used C#.NET and ADO 2.0 so there are some differences to the code. In case anyone is interested here is the way I retrieved the names of the database constraints:

OleDbConnection Connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + dbpath + ";");
Connection.Open();
DataTable SchemaTable = Connection.GetSchema("Indexes");

Now the DataTable "SchemaTable" will contain all relationship names in the DataColumn "INDEX_NAME" (as well as a lot of other data) , you may need to find which values to drop or modify experimentally.

To drop a relationship use the owner table's name and the constraint name in a query:

ALTER TABLE [$table] DROP CONSTRAINT [$constraint_name]