VBA, Creating and deleting referential table links programmatically

Creating and deleting referential table links programmatically

Author	Message
Jeff Simcoc #1 / 4	Creating and deleting referential table links programmatically I have also posted this in tablesdbdesign.... I have an app for a client whereby tables are created programmatically and referential links established. But i also wish to delete these tables later and need to remove the relation first. How does one know the name of the relation to delete..... Public Function CreateTableRelation(lstrRelation As String, lstrPrimary As String, lstrSecondary As String, lstrField As String) Dim dbs As Database, rel As Relation, fld As Field Set dbs = CurrentDb Set rel = dbs.CreateRelation(lstrRelation, lstrPrimary, lstrSecondary) rel.Attributes = dbRelationUpdateCascade And dbRelationDeleteCascade Set fld = rel.CreateField(lstrField) fld.ForeignName = lstrField rel.Fields.Append fld dbs.Relations.Append rel dbs.Relations.Refresh Set dbs = Nothing End Function Public Function DeleteTableRelation(lstrRelation As String) Dim dbs As Database Set dbs = CurrentDb dbs.Relations.Delete (lstrRelation) <<===== what name do i use here?? I realise that the relation is named by combining the two table names but when they are very long names they would exceed the 64 character relation name?? Is it because its late here (11:00pm on tues) and i am falling asleep and missing something??? like my brain has ceased functioning!! dbs.Relations.Refresh Set dbs = Nothing End Function -- Jeff Simcock - Software n Database Consultant Odyssey Solutions Pty Ltd - Perth WA Australia * +61 8 9443 3793 * +61 0419 042 180 * +61 8 9201 0379
Sat, 30 Aug 2003 22:53:44 GMT

Steffen, Wilhelm Dr #2 / 4	Creating and deleting referential table links programmatically The name 'lstrRelation' of the relation is not neccessarily a combination of both table names. It can be any string but must be unique (no other constraint may have the same name). You may combine prefix + tablename + counter like this: "Fk_" & tablename & "_" & counter where 'counter' is incremented with every foreign key which your application creates in this table. <snip> Quote: > I have an app for a client whereby tables are created programmatically and > referential links established. > But i also wish to delete these tables later and need to remove the relation > first. How does one know the name of the relation to delete..... > Public Function CreateTableRelation(lstrRelation As String, lstrPrimary As > String, lstrSecondary As String, lstrField As String) <snip> > Public Function DeleteTableRelation(lstrRelation As String) > Dim dbs As Database > Set dbs = CurrentDb > dbs.Relations.Delete (lstrRelation) <<===== what name do i use here?? Define a naming rule for constraints that makes it easy for you to construct the name needed here. Dr. Wilhelm Steffen SCHENCK PEGASUS GmbH Darmstadt / Germany
Sat, 30 Aug 2003 23:45:29 GMT

Dirk Goldga #3 / 4	Creating and deleting referential table links programmatically I have only dabbled in this, but it may be that you need to loop through the Relations collection examining the .Table and .ForeignTable properties (and possibly the field names in the relation's .Fields collection) until you find the one you want. I have experimentally used code that backs up all the relations, deletes them all, empties and reloads tables, and then recreates the relations. However, my code only allows one pair of related fields, and it hasn't been tested enough to make me want to rely on it. -- Dirk Goldgar (remove NOSPAM from reply address) Quote: > I have also posted this in tablesdbdesign.... > I have an app for a client whereby tables are created programmatically and > referential links established. > But i also wish to delete these tables later and need to remove the relation > first. How does one know the name of the relation to delete..... > Public Function CreateTableRelation(lstrRelation As String, lstrPrimary As > String, lstrSecondary As String, lstrField As String) > Dim dbs As Database, rel As Relation, fld As Field > Set dbs = CurrentDb > Set rel = dbs.CreateRelation(lstrRelation, lstrPrimary, lstrSecondary) > rel.Attributes = dbRelationUpdateCascade And dbRelationDeleteCascade > Set fld = rel.CreateField(lstrField) > fld.ForeignName = lstrField > rel.Fields.Append fld > dbs.Relations.Append rel > dbs.Relations.Refresh > Set dbs = Nothing > End Function > Public Function DeleteTableRelation(lstrRelation As String) > Dim dbs As Database > Set dbs = CurrentDb > dbs.Relations.Delete (lstrRelation) <<===== what name do i use here?? > I realise that the relation is named by combining the two table names but > when they are very long names they would exceed the 64 character relation > name?? > Is it because its late here (11:00pm on tues) and i am falling asleep and > missing something??? like my brain has ceased functioning!! > dbs.Relations.Refresh > Set dbs = Nothing > End Function > -- > Jeff Simcock - Software n Database Consultant > Odyssey Solutions Pty Ltd - Perth WA Australia > * +61 8 9443 3793 * +61 0419 042 180 * +61 8 9201 0379
Sun, 31 Aug 2003 01:48:06 GMT

Jeff Simcoc #4 / 4	Creating and deleting referential table links programmatically Thx dirk Yes i think you are right. Steffen posted thinking that i am interested in the CREATION name...but I'm more concerned about locating the the relation later..like days later... I tried seting the name to something of my own but found that it just used the table names anyway... Regards jeff Quote: > I have only dabbled in this, but it may be that you need to loop through the > Relations collection examining the .Table and .ForeignTable properties (and > possibly the field names in the relation's .Fields collection) until you > find the one you want. > I have experimentally used code that backs up all the relations, deletes > them all, empties and reloads tables, and then recreates the relations. > However, my code only allows one pair of related fields, and it hasn't been > tested enough to make me want to rely on it. > -- > Dirk Goldgar > (remove NOSPAM from reply address) > > I have also posted this in tablesdbdesign.... > > I have an app for a client whereby tables are created programmatically and > > referential links established. > > But i also wish to delete these tables later and need to remove the > relation > > first. How does one know the name of the relation to delete..... > > Public Function CreateTableRelation(lstrRelation As String, lstrPrimary As > > String, lstrSecondary As String, lstrField As String) > > Dim dbs As Database, rel As Relation, fld As Field > > Set dbs = CurrentDb > > Set rel = dbs.CreateRelation(lstrRelation, lstrPrimary, lstrSecondary) > > rel.Attributes = dbRelationUpdateCascade And dbRelationDeleteCascade > > Set fld = rel.CreateField(lstrField) > > fld.ForeignName = lstrField > > rel.Fields.Append fld > > dbs.Relations.Append rel > > dbs.Relations.Refresh > > Set dbs = Nothing > > End Function > > Public Function DeleteTableRelation(lstrRelation As String) > > Dim dbs As Database > > Set dbs = CurrentDb > > dbs.Relations.Delete (lstrRelation) <<===== what name do i use here?? > > I realise that the relation is named by combining the two table names but > > when they are very long names they would exceed the 64 character relation > > name?? > > Is it because its late here (11:00pm on tues) and i am falling asleep and > > missing something??? like my brain has ceased functioning!! > > dbs.Relations.Refresh > > Set dbs = Nothing > > End Function > > -- > > Jeff Simcock - Software n Database Consultant > > Odyssey Solutions Pty Ltd - Perth WA Australia > > * +61 8 9443 3793 * +61 0419 042 180 * +61 8 9201 0379
Sun, 31 Aug 2003 06:34:40 GMT

Page 1 of 1

[ 4 post ]