To: accessd@egroups.com
Subject: RE: [accessd] Code to create relationships?
There are two ways to create relationships in code.  You can do it by executing an SQL data definition query to create constraints, for example:
 
CREATE TABLE ReferenceData
([ReferenceKey] integer,
[ReferenceData] text,
CONSTRAINT [Index1] PRIMARY KEY ([ReferenceKey]));
 
CREATE TABLE ReferencingData
([ID] integer,
[ReferenceKey]  integer,
[ReferencingData] text,
CONSTRAINT [FK1] FOREIGN KEY (ReferenceKey) REFERENCES ReferenceData (ReferenceKey),
CONSTRAINT [Index1] PRIMARY KEY ([ID]));
 
creates two tables with primary keys and a foreign key relationship between them.  You can also add relationships after the create operation using the ALTER TABLE query, as follows:
 
ALTER TABLE ReferencingData2
ADD CONSTRAINT [FK2] FOREIGN KEY (ReferenceKey) REFERENCES ReferenceData (ReferenceKey)
;
Or you can create relationships using code, as follows:
 
Sub makeRel(zTxt As Control)
    Dim newRel As Relation, newFld As Field
    Call killRel(zTxt)
    Set newRel = CurrentDb.CreateRelation("newRel", "t1", "t2")
    Set newFld = newRel.CreateField("T1Key", dbLong)
    newFld.ForeignName = "T1Key"
    newRel.Fields.Append newFld
    Call addToText(zTxt, "Adding newRel")
    CurrentDb.Relations.Append newRel
End Sub
Sub killRel(zTxt As Control)
On Error Resume Next
Call addToText(zTxt, "Removing newRel")
CurrentDb.Relations.Delete ("newRel")
End Sub
These examples are available from my web site as the first item on the Useful Software page.

Steve Goodhall
mailto:SGoodhall@home.com
http://members.home.net/sgoodhall/

Sooner or later, all this planning has to degenerate into work.  - Peter Drucker

-----Original Message-----
From: Arthur Fuller [mailto:artful@cgocable.net]
Sent: Tuesday, December 26, 2000 8:08 AM
To: accessd@egroups.com
Subject: [accessd] Code to create relationships?

Does anyone have any examples of creating relationships in code? The
following snippet enumerates them nicely, but I'm not sure how to create
one, nor what the constants might be for the attributes. Ideally, I want to
know how to copy all the relationships from one database to another.

Sub EnumRels()
    Dim db as Database
    Dim rel as Relation
    Set db = CurrentDb
    For each rel in db.Relations
        Debug.Print rel.Table & " Related to: " & rel.ForeignTable
    Next
    Set rel = Nothing
    Set db = Nothing
End Sub

TIA,
Arthur


----- Original Message -----
From: "William Hindman" <dejpolsys@hotmail.com>
To: <AccessD@databaseadvisors.com>; <accessd@egroups.com>;
<accessd@mtgroup.com>; "Access Developers List"
<access_list@list.simworld.com>
Sent: Monday, December 25, 2000 11:03 PM
Subject: [accessd] Major List Changes: 3rd Update


> Groups ...both accessd and access_list
>
> Hope your holidays are as joyful as ours and that
> all the best attains to you and yours during the
> coming year!!! :-)))))
>
> In the meantime, its necessary to remind you that
> there are major changes afoot in these lists that may
> affect any and all of you ...so once more I post :
> >
> > As you are probably aware by now, the owner
> > of the accessd list server, Dave Scott, has
> > experienced substantial career changes and
> > can no longer continue to support the list in the
> > manner he knows it needs.  Dave has posted a
> > msg to that effect and asked if someone would
> > take over the list.
> >
> > At the same time, the access_list list server has
> > reached a major milestone in our own growth.
> > Until now we have been graciously hosted by
> > Haley DiMarino (nee Rochester) and her
> > employers at simworld. We have now formed
> > a list member owned corporation and gathered
> > the assets necessary to both host our own lists
> > as well as support a major web site dedicated to
> > Access, VB, and SQL databases. Our assets
> > and technical base are sufficient to guarantee
> > future round-the-clock support on one of the
> > very best ISPs available.
> >
> > Through the presence of John Colby, who is
> > well known to both list's memberships, we have
> > reached an agreement with Dave Scott to take
> > over all accessd assets including the current
> > membership lists.
> >
> > Therefore, effective immediately, it is our intent
> > to combine the membership of both accessd and
> > access_list into one list to be known as AccessD.
> > Both current lists will continue to work through the
> > Christmas and New Years holidays as members
> > transfer to the new list ...but will be closed shortly
> > thereafter.
> >
> > The new AccessD list server is available now. We
> > ask that you register with us by sending a message
> > to imailsrv@databaseadvisors.com and in the body
> > say 'subscribe accessd 'your name'' ...replacing
> > 'your name' with the User Name you want to be
> > known by on the new list. Do not use quotes.
> >
> > Example:
> >
> > subscribe accessd William
> >
> > You will get a quick response if your registration is
> > successful.
> >
> > If you get an error, try using a different User Name
> > else e me and I'll put you in touch with one of our
> > staff to resolve the problem.
> >
> > >From that point on address your list messages to:
> >
> > accessd@databaseadvisors.com
> >
> > We apologize for making you reregister but the
> > differences between the previous list software
> > being used and that we are going to use makes
> > importing the member lists a task that we can't
> > afford to wait on.
> >
> > Once you have registered with AccessD I would
> > suggest that you would want to unregister at both
> > accessd and access_list in order to avoid duplication
> > of msg traffic.
> >
> > To unsubscribe from accessd send an e-mail to
> >  listserv@mtgroup.com (note: NOT accessd)
> > with the following in the body of the message:
> >
> > unsubscribe listname your_full_name
> >
> > and you *must* send it from the same e-mail
> > account from which you signed in. If you have
> > problems unsubing from accessd, we will take
> > the list down shortly after the New Year and
> > that will permanently eliminate the problem. Do
> > try the unsub since it will substantially reduce
> > traffic for those of us monitoring all the lists.
> >
> > We will also have lists available shortly for both
> > long OT (off-topic) threads and list maintenance
> > threads. In the meantime, the netiquette common
> > to accessd is expected of all.
> >
> > We will post more information shortly on the new
> > AccessD list concerning our member ownership
> > and plans for a wealth of Access support projects
> > on our own web site. In the meantime, you will
> > continue to receive the best that accessd and/or
> > access_list has provided over the past months.
> >
> > If you note any shortcomings or errors in this msg
> > or have pertinent suggestions for improving it, please
> > contact me via e and I will respond ASAP.
> >
> > William Hindman
> > dejpolsys@hotmail.com
> > Royal Knight Systems
> >
> >
>
>
> To unsubscribe from this group, send an email to:
> accessd-unsubscribe@egroups.com
>
>
>
>
To unsubscribe from this group, send an email to:
accessd-unsubscribe@egroups.com