Creating relationships using VBA

[mohd21uk via AccessMonster.com]

I have a table called tbl Status which has a field called Status and a table
called tbl CN43 with a field called Status. I would like to link both the
fields together using VBA code. I would be grateful if you could provide me
the code to do this.

Many Thanks,

 

[dbahooker]

why do you 'need' to 'link them'

do they have the same datatype; etc?

if you had a real database- like Access Data Projects-- then you could
simply run a sql statement.

you could have a simple TSQL statement to create these relationships.


ALTER TABLE dbo.OrderLineItems ADD CONSTRAINT
FK_OrderLineItems_Orders FOREIGN KEY
(
OrderID
) REFERENCES dbo.Orders
(
OrderID
) ON UPDATE NO ACTION
ON DELETE NO ACTION


you can't do that kinda thing in mdb can you??

 

[Allen Browne]

The code would be something like this:

Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

'Initialize
Set db = CurrentDb()

'Create a new relation.
Set rel = db.CreateRelation("StatusCN43")

'Define its properties.
With rel
'Specify the primary table.
.Table = "tbl Status"
'Specify the related table.
.ForeignTable = "tbl CN43"
'Specify attributes for cascading updates and deletes.
.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade

'Add the fields to the relation.
'Field name in primary table.
Set fld = .CreateField("Status")
'Field name in related table.
fld.ForeignName = "Status"
'Append the field.
.Fields.Append fld
End With

'Save the newly defined relation to the Relations collection.
db.Relations.Append rel

 

[dbahooker]

HAHAHAHAHAHAHAHA

you have to use CODE to do that?

I can do it with a simple SQL Statement; the difference is that it's
FASTER and it's hella easy to automate

you see-- stored procedures have this functionality where a single
sproc and run mulitple SQL statements.

Your baby database is a joke.

I just can't believe you idiots use DAO crap in the year 2006. TSQL is
so much more powerful; it makes me sick!!!

-Aaron

 

[SusanV]

Go play in traffic - no none here cares.

Note to self - PLEASE don't feed the trolls!