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.

[Richard]

On Jan 11, 5:10 pm, "Larry Linson" <boun...@localh ost.notwrote:

Relationships define how the database engine deals with the data in the
tables. What would be the purpose in defining relationships in the Access
front end, since they would not be used by your ODBC data source, MySQL?
(For example, if you have a split Access - Jet DB Engine database, you must
define the relationships in the back-end .MDB where the tables actually
reside.)

Yes, the mySql back end also has these relationships defined.

Access is being used as a custom querying/reporting tool by our
client.

To make it easier for them to create their custom queries and reports
I need to populate the access front end relationship diagram so that
when they create new queries, the tables they add will be linked
together correctly in the query gui.

The reason I must do this in code (as opposed to me building the
diagram by hand) is

a) Its quite a complicated schema and I'm bound to miss/mess
something ;-)
b) The schema is under development and quite likely to change before
it's finalised.

So, back to my question: How do I create a relationship diagram in
code :-)

[Larry Linson]

"Richard@Ho me" <ri***********@ gmail.comwrote in message
news:7b******** *************** ***********@k39 g2000hsf.google groups.com...

On Jan 11, 5:10 pm, "Larry Linson" <boun...@localh ost.notwrote:

>Relationship s define how the database engine deals with the data in the
tables. What would be the purpose in defining relationships in the
Access
front end, since they would not be used by your ODBC data source, MySQL?
(For example, if you have a split Access - Jet DB Engine database, you
must
define the relationships in the back-end .MDB where the tables actually
reside.)

Yes, the mySql back end also has these relationships defined.

Access is being used as a custom querying/reporting tool by our
client.

To make it easier for them to create their custom queries and reports
I need to populate the access front end relationship diagram so that
when they create new queries, the tables they add will be linked
together correctly in the query gui.

The reason I must do this in code (as opposed to me building the
diagram by hand) is

a) Its quite a complicated schema and I'm bound to miss/mess
something ;-)
b) The schema is under development and quite likely to change before
it's finalised.

So, back to my question: How do I create a relationship diagram in
code :-)

I have never created a relationship diagram in code and I don't have
available a client-server testbed, but I do have a test database that links
to tables in the example Northwind database.

For previous testing, I created relationships in the front-end between the
pertinent tables in Northwind, and the relationships defined do create join
lines in new Queries. What would happen if you have a difference between
your local relationships (as you might during development, with changes
going on) and the remote relationships, I couldn't say.

And, as I said, I have not created relationships in code, for any type of
tables, so cannot offer worthwhile guidance on that subject.

If I were faced with your problem, I'd create the relationships manually,
and just try to be very careful to "keep on top" of changes that might
affect the relationships; or, if I felt the need was compelling, I would
research at the Microsoft Knowledge Base, http://support.microsoft.com, the
Microsoft Developer Network site,
http://msdn2.microsoft.com/en-us/default.aspx, and do a search using your
favorite search engine.

Good luck with your project,

Larry Linson
Microsoft Access MVP

[David W. Fenton]

"Richard@Ho me" <ri***********@ gmail.comwrote in
news:7b******** *************** ***********@k39 g2000hsf.google groups.co
m:

To make it easier for them to create their custom queries and
reports I need to populate the access front end relationship
diagram so that when they create new queries, the tables they add
will be linked together correctly in the query gui.

Two points:

1. you can add non-enforced "relationsh ips" in the relationship
designer that serve no purpose other than defining default joins
between tables.

2. in the QBE, any two tables that have the same field name in both
and appropriate indexes will be joined automatically. My suspicion
is that the MySQL db you're using doesn't use the same name for the
FK as it does for the PK it's related to.

In any event, just define the relationships in your front end and
they'll make building queries easier, which, it seems to me, is what
you're looking for.

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

[David W. Fenton]

"Richard@Ho me" <ri***********@ gmail.comwrote in
news:f4******** *************** ***********@l1g 2000hsa.googleg roups.com
:

In a nutshell, I'm after the code that will create the local
relationship diagram, which I can do manually without generating
the error "Cannot create a relationship on linked ODBC tables."

What is producing that error? I can quite easily create
non-enforeced relationships on ODBC tables (that happen to be MySQL,
in fact) using the user interface for doing so.

Are you sure you're doing it correctly?

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

[Richard]

Again it may not be eitehr right or wrong but its a lot cleaner and

intuitive.

Naming conventions are a matter of personal preference (in this case,
every CakePHP application follows these conventions), and not really
relevant to my problem :-)

[Richard]

Here's the code again (from my original post).

It's using Microsoft ADO Ext. 2.8 for DDL and Security, I'm quite
happy to change this to another library if that will get the job
done :-)

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.

Thank for taking the time to help out with this :-)

[lyle fairfield]

"Richard@Ho me" <ri***********@ gmail.comwrote in news:e4324d0b-559b-
4a************* *******@e10g200 0p...legrou ps.com:

Here's the code again (from my original post).

It's using Microsoft ADO Ext. 2.8 for DDL and Security, I'm quite
happy to change this to another library if that will get the job
done :-)

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.

Thank for taking the time to help out with this :-)

My recollection from the olden days is that one had to create the
relationship with something like

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

then to set the value of its parameters

and then to append it.

Perhaps this is no longer a requirement.

[David W. Fenton]

"Richard@Ho me" <ri***********@ gmail.comwrote in
news:e4******** *************** ***********@e10 g2000prf.google groups.co
m:

Here's the code again (from my original post).

[snipped]

It's using Microsoft ADO Ext. 2.8 for DDL and Security, I'm quite
happy to change this to another library if that will get the job
done :-)

Why are you using a generic library to create a structure that is
proprietary to Access? 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).

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

[Richard]

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.

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 :-)

Lyle wrote:

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

Thank's, I'll take a look at that this morning :-)