"Ted McNeal" <tmcneal@mhsinc.org> wrote:
>Establishing a relationship is really not that bad. It is just very procedural.
> Since, you are using visdata, I am going to assume that you are using DAO
>as your database access method.

Yup... That's it.

>You must first use the construct 'CreateRelation' of the database object
>to create a relationship object. Then you will identify the foreign key
>table. The foreign key table is the table it which the primary table will
>be related. The primary table is the table with the primary key that will
>control constraint of the relationship.

Yeah, I'm a whiz with db theory... Just not with this version of VisData!
I played with it for HOURS. About 12, to be exact. I brought in sample databases
and looked at their properties, structures, indexes, etc, etc, thinking maybe
I was missing something. I don't know what's wrong with it, but in the add
index window you can see your indexes listed along with disabled checkboxes
indicating their properties... Primary, Required, Unique, Foreign... you
know the drill. When you click Add Index, the only three checkboxes you have
available are Primary, Unique, and Ignore Nulls. No Foreign. That is so nuts!!!
Why can't they just let you check Foreign when you create the index and have
a nice little listbox of tables.fields where you can tell it what its primary
field is??? *grumble*

>For each field that is part of a relationship, you must create a field object
>and assign the name of the field in the foreign key table where the relationship
>will be established. You will use the foreign name of the field object
>to accomplish this. Once, your field object is defined you will append

>to the fields collection of the previously created relationship. You must
>repeat the process of creating and appending the field object for each field
>that will be involved in the relationship.

Thank God I only have two relationships to define...

>Once you have completed the construction of the relationship, it must be
>appended to the relations collection of the database object.
>You can think of this process like building a manufactured home. You build
>the walls (the relationship) then create each wall (field). You will bring
>each wall into the home and install it (append the field to the fields collection
>of the relation). When the home is done, you will take it to the site and
>place it on the foundation (append the relationship to the database).

Wish I could hire a contractor. *SNICKER*

>Okay...here is a code sample that will accomplish this for you.
>Dim wsp as DAO.Workspace
>Dim dbs as DAO.Database
>Dim rel as DAO.Relation
>Dim fld as DAO.Field
>'I will assume that you will declare your workspace and database connection

Oh, yeah. The front end works beautifully. It'll write, add, delete, and
update records all day... It just doesn't realize some of its fields need
to play nice together.

As far as the workspace, I dunno... That's something I'll have to research.
I learned to do this stuff in VB 4.0, and I don't remember anything about
workspaces. I should know, I guess. Probably need to refresh my memory...
That was 3 years ago. The last course I took Fall '99 just said "Look, this
is a data control. Hook a mdb up to it, hook it up to some text boxes, and
voila!" There was a lot of concentration on sequentials, which I hate. Do
people in the real world REALLY edit txt files and display results with a

>Set rel = dbs.CreateRelation("fk_Exhibits_ArtInventory", "ArtInventory","Exhibits")'The
>last argument is for declaring if you want cascading operations on this

> If this argument is left empty then you will have referential integrity
>established and no cascading operations (cascade updates/deletes)
>Set fld = rel.CreateField("ID") 'name of the field that will be linked in
>the primary table (ArtInventory) You don't to define the data type. This
>is for creating field to a tabledef
>fld.ForeignName = "ID" 'Name of the related field in the foreign key table
>rel.Fields.Append fld 'Append the newly create field and its definitions
>to the fields collection of the relation object
>dbs.Relations.Append rel 'append the relationship defined above to the relations
>collection of the database object. This is the point where the relation
>is created in the database.

Oh, geez! That was SOOO much easier!!! It might even work!!! I'm laying out
the code right now... Putting it in a module. I'm not sure if that will work,
but I'm hoping.

>If you are very frustrated, buy a copy of Access.

Must be nice not to be poor. *SNICKER*

You can create a database
>in Access 2000 (Jet 4.0) then convert it to an earlier version (Access 97/
>Jet 3.5).
>Ted McNeal

I tried! I never could figure out how you do that... Won't do a "save as".
Guess I need to dig in the help. I can get at Access 2000 all I need to.

Well, keep your fingers crossed! And thanks again SO MUCH!!!


P.S. Look at this crap...

//CREATEDB.TXT - Sample code for creating a database

Dim MyDB As Database, MyWs As Workspace
Dim T1, T2 As TableDef
Dim T1Flds(1 To 2), T2Flds(1 To 3) As Field
Dim TempFld As Field
Dim T1Idx, T2Idx As Index
Dim Rel As Relation
Dim MyRec As Recordset
'Create Database
Set MyWs = DBEngine.Workspaces(0)
Set MyDB = MyWs.CreateDatabase("C:\DBNAME.MDB", dbLangGeneral)
Rem Create first Table, TABLE1
Set T1 = MyDB.CreateTableDef("TABLE1")
'Specify fields for TABLE1
'Note the use of the optional parameter 50 for field size
'If 50 is omitted, the size will default to 20
Set T1Flds(1) = T1.CreateField("FIELD1A", dbText, 50)
Set T1Flds(2) = T1.CreateField("FIELD1B", dbSingle)
'Add the New fields to the field list in the Table
T1.Fields.Append T1Flds(1)
T1.Fields.Append T1Flds(2)
'Specify a primary field for TABLE1
Set T1Idx = T1.CreateIndex("FIELD1A")
T1Idx.Primary = True
T1Idx.Unique = True
T1Idx.Required = True
Set T1Flds(1) = T1Idx.CreateField("FIELD1A")
'Add this field to the field list of the Index
T1Idx.Fields.Append T1Flds(1)
'Add this Index to the index list of the Table
T1.Indexes.Append T1Idx
'Add the Table to the Database
MyDB.TableDefs.Append T1
'Create TABLE2
Set T2 = MyDB.CreateTableDef("TABLE2")
'Specify fields for TABLE2
Set T2Flds(1) = T2.CreateField("FIELD2A", dbText, 50)
Set T2Flds(2) = T2.CreateField("FIELD2B", dbSingle)
Set T2Flds(3) = T2.CreateField("FIELD2C", dbInteger)
'Add the new fields to the field list of the Table
T2.Fields.Append T2Flds(1)
T2.Fields.Append T2Flds(2)
T2.Fields.Append T2Flds(3)
'Set the primary field for TABLE2
Set T2Idx = T2.CreateIndex("FIELD2C")
T2Idx.Primary = True
T2Idx.Unique = True
T2Idx.Required = True
Set T2Flds(3) = T2Idx.CreateField("FIELD2C")
'Add this field to the field list of the Index
T2Idx.Fields.Append T2Flds(3)
'Add this index to the index list of TABLE2
T2.Indexes.Append T2Idx
'Add TABLE2 to the Database
MyDB.TableDefs.Append T2
'Set up the relation between the tables
Set Rel = MyDB.CreateRelation("foreign", "TABLE1", "TABLE2")
Rel.Attributes = 0
'Mark the primary field in TABLE1
Set T2Flds(1) = Rel.CreateField("FIELD1A")
'Mark the foreign key field in TABLE2
T2Flds(1).ForeignName = "FIELD2A"
'Add the field to the field list of the relation
Rel.Fields.Append T2Flds(1)
'Add the relation to the database
MyDB.Relations.Append Rel
'Add a record to each table
'Open a recordset referring to TABLE1
Set MyRec = T1.OpenRecordset
'Create a record
MyRec("FIELD1A") = "alpha"
MyRec("FIELD1B") = 1997
'Update the recordset
'Close the recordset referring to TABLE1
'Open a recordset referring to TABLE2
Set MyRec = T2.OpenRecordset
'Create a record
MyRec("FIELD2A") = "alpha"
MyRec("FIELD2B") = 2000
MyRec("FIELD2C") = 1
'Update the recordset
'Close the recordset
'Close the database