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,

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200606/1

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??



mohd21uk via AccessMonster.com wrote:
> 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,
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...ccess/200606/1

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mohd21uk via AccessMonster.com" <u20517@uwe> wrote in message
news:621b308278ce2@uwe...
>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.

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


Allen Browne wrote:
> 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
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "mohd21uk via AccessMonster.com" <u20517@uwe> wrote in message
> news:621b308278ce2@uwe...
> >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.

Go play in traffic - no none here cares.

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
> Allen Browne wrote:
>> 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
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "mohd21uk via AccessMonster.com" <u20517@uwe> wrote in message
>> news:621b308278ce2@uwe...
>> >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.
>