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.CreateRelat ion("Emp No", "Upload", "FTE Table")
rel1.Attributes = dbRelationDelet eCascade +
dbRelationUpdat eCascade
Set fld = rel1.CreateFiel d("Emp No")
fld.ForeignName = "Emp No"
rel1.Fields.App end fld
dbs.Relations.A ppend rel1
MsgBox "Relation '" & rel1.Name & "' created."
Set dbs = Nothing

Set dbs = CurrentDb
Set rel2 = dbs.CreateRelat ion("Cost Centre", "Department ",
"FTE Table")
rel2.Attributes = dbRelationDelet eCascade +
dbRelationUpdat eCascade
Set fld = rel2.CreateFiel d("DepartmentCo stCentre")
fld.ForeignName = "Cost Centre"
rel2.Fields.App end fld
dbs.Relations.A ppend rel2
MsgBox "Relation '" & rel2.Name & "' created."
Set dbs = Nothing

End Sub

The code gets as far as dbs.Relations.A ppend 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, " & _
"EmployeeTy pe INTEGER NOT NULL " & _
" REFERENCES EmpTypes ON UPDATE CASCADE ON DELETE CASCADE)"

CurrentDB.Execu te 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/AwUBP8ZRwIechKq OuFEgEQIDBgCfbL 0UU1tltOunQ7/jo4fkfU40/MUAoJmx
xV6+Q01OeFvA75k pzzxKPoa+
=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.CreateRelat ion("Emp No", "Upload", "FTE Table")
rel1.Attributes = dbRelationDelet eCascade +
dbRelationUpdat eCascade
Set fld = rel1.CreateFiel d("Emp No")
fld.ForeignName = "Emp No"
rel1.Fields.App end fld
dbs.Relations.A ppend rel1
MsgBox "Relation '" & rel1.Name & "' created."
Set dbs = Nothing

Set dbs = CurrentDb
Set rel2 = dbs.CreateRelat ion("Cost Centre", "Department ",
"FTE Table")
rel2.Attributes = dbRelationDelet eCascade +
dbRelationUpdat eCascade
Set fld = rel2.CreateFiel d("DepartmentCo stCentre")
fld.ForeignName = "Cost Centre"
rel2.Fields.App end fld
dbs.Relations.A ppend rel2
MsgBox "Relation '" & rel2.Name & "' created."
Set dbs = Nothing

End Sub

The code gets as far as dbs.Relations.A ppend 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******@stroud andswindon.co.u k (Jenni) wrote in
news:9f******** *************** ***@posting.goo gle.com:

Set fld = rel2.CreateFiel d("DepartmentCo stCentre")
fld.ForeignName = "Cost Centre"
rel2.Fields.App end fld
dbs.Relations.A ppend rel2
MsgBox "Relation '" & rel2.Name & "' created."
Set dbs = Nothing

Maybe

Set fld = rel2.CreateFiel d("DepartmentCo stCentre")
rel2.Fields.App end fld
rel2.Fields("De partmentCostCen tre").ForeignNa me = "Cost Centre"
dbs.Relations.A ppend 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******@strou dandswindon.co. uk> wrote in message
news:9f******** *************** ***@posting.goo gle.com...

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.CreateRelat ion("Emp No", "Upload", "FTE Table")
rel1.Attributes = dbRelationDelet eCascade +
dbRelationUpdat eCascade
Set fld = rel1.CreateFiel d("Emp No")
fld.ForeignName = "Emp No"
rel1.Fields.App end fld
dbs.Relations.A ppend rel1
MsgBox "Relation '" & rel1.Name & "' created."
Set dbs = Nothing

Set dbs = CurrentDb
Set rel2 = dbs.CreateRelat ion("Cost Centre", "Department ",
"FTE Table")
rel2.Attributes = dbRelationDelet eCascade +
dbRelationUpdat eCascade
Set fld = rel2.CreateFiel d("DepartmentCo stCentre")
fld.ForeignName = "Cost Centre"
rel2.Fields.App end fld
dbs.Relations.A ppend rel2
MsgBox "Relation '" & rel2.Name & "' created."
Set dbs = Nothing

End Sub

The code gets as far as dbs.Relations.A ppend 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