Access 2007: Creating a local relationship between two remote ODBCtables

[Richard]

Hi folks, thanks for taking the time to read this (and hopefully point
our where I'm going wrong).

The scenario:

I have a local Access2007 database which links in several read only
mySql tables via ODBC.

The problem:

I need to programmaticall y (in a VBA module) create the relationship
diagram in the access database between the linked tables, just like
you can do in the Relationship editor. (Drag the foreign key of the
child to the primary key in the parent).

For example, say I have two tables: Parents hasMany Children

Parents
id (PK)
Children
id (PK)
parent_id (FK to Parents.id)

So far, I have the following:

Dim r As Relation
Dim ff As Field

Set r = New Relation

With r
.Name = "Parent_Childre n"
.Table = "Children"
.ForeignTable = "Parent"

Set ff = New Field
ff.Name = "parent_id"
ff.ForeignName = "id"

r.Fields.Append ff

CurrentDb.Relat ions.Append r ' *** fails on this line

End With

The error I'm getting is:

Run-time error '3613':

Cannot create a relationship on linked ODBC tables.

Which makes me think access is trying to pass the relationship off to
the (read only) ODBC datasource and not adding it to the local
relations collection.

How do I add it to the local collection (ie, the one used by the
Relationship diagram)?

Thanks in advance
Richard.

[David W. Fenton]

"Richard@Ho me" <ri***********@ gmail.comwrote in
news:96******** *************** ***********@x69 g2000hsx.google groups.co
m:

David Wrote:

>Why are you using a generic library to create a structure that is
proprietary to Access?

Because it was the first example of code that creates relations
that I found, most of the other example I found used the same
libraries (or referred to the original code I found). I'm a PHP
coder by profession and only use odd bits of Microsoft
technologies thrown in when requested by the client.

Ah. Then you're a victim of Microsoft's ill-fated promotion of ADO
over native methods for working with Jet data. MS has changed their
mind, but way too many people never got the memo.

[Well, some of us didn't need a memo from MS, since we knew ADO
never made sense for Jet data]

>I don't know for a fact that DAO can create
the non-enforced relationship, but it would be the first choice,
since you're altering something in a Jet MDB (i.e., the front
end).

Ok, no problem. I'm always happy to learn. What technology would
you use in this case? A small snippet of example code would be
very useful too :-)

I'd use DAO. I don't do this kind of thing in code, so wouldn't know
how to do it, but the code Lyle gave you looks about right.

Lyle wrote:

>Set r = DbEngine(0)(0). CreateRelation( "NameofRelation ")

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/