Recreating relationships in code

[Jenni]

Hopefully someone out there can help. I am currently trying to write
some code to allow me to delete a table, then recreate it and
re-establish the relationships. I seem to have hit a snag in the
recreation of the relationships. Code is:
Sub Woof()
Dim dbs As Database
Dim fld As Field, rel1 As Relation, rel2 As Relation
Set dbs = CurrentDb

Set rel1 = dbs.CreateRelation("Emp No", "Upload", "FTE Table")
rel1.Attributes = dbRelationDeleteCascade +
dbRelationUpdateCascade
Set fld = rel1.CreateField("Emp No")
fld.ForeignName = "Emp No"
rel1.Fields.Append fld
dbs.Relations.Append rel1
MsgBox "Relation '" & rel1.Name & "' created."
Set dbs = Nothing

Set dbs = CurrentDb
Set rel2 = dbs.CreateRelation("Cost Centre", "Department",
"FTE Table")
rel2.Attributes = dbRelationDeleteCascade +
dbRelationUpdateCascade
Set fld = rel2.CreateField("DepartmentCostCentre")
fld.ForeignName = "Cost Centre"
rel2.Fields.Append fld
dbs.Relations.Append rel2
MsgBox "Relation '" & rel2.Name & "' created."
Set dbs = Nothing

End Sub

The code gets as far as dbs.Relations.Append rel1 and then errors out
with a message "Can't append a relation without fields defined". I
have tried a few different configurations of the field definition
section but i seem to be missing something. As this is the first time
I have tried this piece of code i'm not one hundred percent sure what
is causing the error.

Can anyone help?

Thanks

Jenni

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In Access 2K & >, I use the JET DDL (Data Definition Language) to
create tables & relationships at the same time. This seems easier to
me than using the DAO/ADO methods. E.g.:

dim strSQL as string

strSQL = "CREATE TABLE myTable ( " & _
"FirstName VARCHAR(25) NOT NULL , " & _
"SSN VARCHAR(9) PRIMARY KEY, " & _
"EmployeeType INTEGER NOT NULL " & _
" REFERENCES EmpTypes ON UPDATE CASCADE ON DELETE CASCADE)"

CurrentDB.Execute strSQL, dbFailOnError

This will create a table "myTable" with a Primary Key on the SSN
column and a relationship (Foreign Key) between column EmployeeType in
table myTable and column EmployeeType in table EmpTypes (table
EmpTypes must exist before this CREATE TABLE statement is run). What
happens on the Delete and Update actions is defined by the "ON DELETE"
AND "ON UPDATE" clauses. See the Access Help articles "CONSTRAINT
clause" and "CREATE TABLE" for more info.

The REFERENCES statement (a CONSTRAINT) will only work in Access 2000
& greater when the Tools > Options > Tables/Query [Tab] : SQL Server
Compatible Syntax (ANSI-92) - "This database" Check Box is checked.

HTH,

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP8ZRwIechKqOuFEgEQIDBgCfbL0UU1tltOunQ7/jo4fkfU40/MUAoJmx
xV6+Q01OeFvA75kpzzxKPoa+
=OEho
-----END PGP SIGNATURE-----

Jenni wrote:

Hopefully someone out there can help. I am currently trying to write
some code to allow me to delete a table, then recreate it and
re-establish the relationships. I seem to have hit a snag in the
recreation of the relationships. Code is:
Sub Woof()
Dim dbs As Database
Dim fld As Field, rel1 As Relation, rel2 As Relation
Set dbs = CurrentDb

Set rel1 = dbs.CreateRelation("Emp No", "Upload", "FTE Table")
rel1.Attributes = dbRelationDeleteCascade +
dbRelationUpdateCascade
Set fld = rel1.CreateField("Emp No")
fld.ForeignName = "Emp No"
rel1.Fields.Append fld
dbs.Relations.Append rel1
MsgBox "Relation '" & rel1.Name & "' created."
Set dbs = Nothing

Set dbs = CurrentDb
Set rel2 = dbs.CreateRelation("Cost Centre", "Department",
"FTE Table")
rel2.Attributes = dbRelationDeleteCascade +
dbRelationUpdateCascade
Set fld = rel2.CreateField("DepartmentCostCentre")
fld.ForeignName = "Cost Centre"
rel2.Fields.Append fld
dbs.Relations.Append rel2
MsgBox "Relation '" & rel2.Name & "' created."
Set dbs = Nothing

End Sub

The code gets as far as dbs.Relations.Append rel1 and then errors out
with a message "Can't append a relation without fields defined". I
have tried a few different configurations of the field definition
section but i seem to be missing something. As this is the first time
I have tried this piece of code i'm not one hundred percent sure what
is causing the error.

Can anyone help?

Thanks

Jenni

[Lyle Fairfield]

jc******@stroudandswindon.co.uk (Jenni) wrote in
news:9f**************************@posting.google.c om:

Set fld = rel2.CreateField("DepartmentCostCentre")
fld.ForeignName = "Cost Centre"
rel2.Fields.Append fld
dbs.Relations.Append rel2
MsgBox "Relation '" & rel2.Name & "' created."
Set dbs = Nothing

Maybe

Set fld = rel2.CreateField("DepartmentCostCentre")
rel2.Fields.Append fld
rel2.Fields("DepartmentCostCentre").ForeignName = "Cost Centre"
dbs.Relations.Append rel2
MsgBox "Relation '" & rel2.Name & "' created."
Set dbs = Nothing
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[TC]

You will need to append >two< fields to the relation before you can append
the relation?

HTH,
TC
"Jenni" <jc******@stroudandswindon.co.uk> wrote in message
news:9f**************************@posting.google.c om...

Hopefully someone out there can help. I am currently trying to write
some code to allow me to delete a table, then recreate it and
re-establish the relationships. I seem to have hit a snag in the
recreation of the relationships. Code is:
Sub Woof()
Dim dbs As Database
Dim fld As Field, rel1 As Relation, rel2 As Relation
Set dbs = CurrentDb

Set rel1 = dbs.CreateRelation("Emp No", "Upload", "FTE Table")
rel1.Attributes = dbRelationDeleteCascade +
dbRelationUpdateCascade
Set fld = rel1.CreateField("Emp No")
fld.ForeignName = "Emp No"
rel1.Fields.Append fld
dbs.Relations.Append rel1
MsgBox "Relation '" & rel1.Name & "' created."
Set dbs = Nothing

Set dbs = CurrentDb
Set rel2 = dbs.CreateRelation("Cost Centre", "Department",
"FTE Table")
rel2.Attributes = dbRelationDeleteCascade +
dbRelationUpdateCascade
Set fld = rel2.CreateField("DepartmentCostCentre")
fld.ForeignName = "Cost Centre"
rel2.Fields.Append fld
dbs.Relations.Append rel2
MsgBox "Relation '" & rel2.Name & "' created."
Set dbs = Nothing

End Sub

The code gets as far as dbs.Relations.Append rel1 and then errors out
with a message "Can't append a relation without fields defined". I
have tried a few different configurations of the field definition
section but i seem to be missing something. As this is the first time
I have tried this piece of code i'm not one hundred percent sure what
is causing the error.

Can anyone help?

Thanks

Jenni

[Jenni]

Thanks for the suggestions, I have taken them all on board and will
try them out. At the moment I'm on a bit of a time constraint and I
have managed to get them going by using them in an Even Proceedure
rather than calling them from a function - who knows why!

Version 2 will definately contain your suggestions.

Thanks

Jenni