473,700 Members | 2,459 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Recreating relationships in code

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
Nov 12 '05 #1
4 5080
-----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

Nov 12 '05 #2
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)
Nov 12 '05 #3
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

Nov 12 '05 #4
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
Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
1971
by: Andante.in.Blue | last post by:
Hello everyone! I've been working with a problematic legacy database for a while. While I am still fairly new to Access, the more I work with the database, the more problems I've uncovered. Unfortunately, most of these problems lie in the ways of architecture. A lot of the tables are designed with no primary keys, and a number of them using the text names in place of the PK-FK relationships. For instances, I have one table called ...
49
2237
by: Mal | last post by:
Hi, As I gain knowledge through a lot of trial, error, and usenet posts.. I have a potentially odd question. I am using a commercial access application. It is a front-end / back end....multi-user app. with a lot of complex functionality (as you would expect from a commercial app). It doesn't have any relationships though.....is this odd???
2
4142
by: Max | last post by:
Hi. I really hope someone can help me. Going slowly insane with this problem. I have a two Access 2000 databases. One is the backend containing tables and some admin queries. The other is the front end with forms / queries and links to the tables in the back end. From the Relationships window I selected File / Print Relationships. The resulting report shows relationships that are not displayed in the relationships window. Some of...
5
4699
by: Mike Turco | last post by:
What is the difference between creating relationships in the front-end vs. the back-end database? I was trying to create a relationship in a database front-end and noticed that I could not check the referential integrity box. What gives? Continuing on with that line of thinking, I understand what do the relationships do for you in a database, but what do they do physically to the tables? Thanks,
3
1860
by: Tom van Stiphout | last post by:
I have written some code to export all objects to text files, and to import those files back into a new database. Hopefully this will eliminate some forms of corruption. The icing on the cake would be to copy the relationships window as well. So far, I was trying this code below, but it doesn't work. First off, I'm surprised that on several MDBs the count of objects is 2. I was expecting 1 because an MDB can have only 1 relationship...
7
2124
by: davegb | last post by:
I'm totally new to relational database design. My boss has asked me to create a database of information on the employees in our group. Seemed to me like a simple application to learn the ropes. A couple of things are confusing me. The first is "Relationships" and "Joins". Are they different names for the same thing? If not, what is the difference? I have a copy of the Access 2000 Bible, and it says, "When you create a relationship between...
10
7022
by: Dixie | last post by:
I need to delete some relationships in code. How do I know what the names of those relationships are?
45
3397
by: salad | last post by:
I'm curious about your opinion on setting relationships. When I designed my first app in Access I'd go to Tools/Relationships and set the relationships. Over time I'd go into the window and see relationship spaghetti....tables/queries all overthe place with lots of relationship lines between here and there. After that first app I didn't do relationships. If I had a query, I defined the relationship. Many of the times when I create a...
13
2075
by: ARC | last post by:
Hello all, Prior to going live with my app, I have questions on relationships theory. My prior app was done in Access 97, and I did NOT use relationships at all. I have 65 tables in my back-end database, and with Access 97, if you added relationships, it would add multiple copies of the same relationships over and over, so I decided not to use any relationships at all. Additionally, when I needed to add or remove fields via code, it...
0
8726
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9075
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
7810
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6561
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5903
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4404
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3089
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2392
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2028
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.