By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 2,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

Access 2007: Creating a local relationship between two remote ODBCtables

P: n/a
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 programmatically (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_Children"
.Table = "Children"
.ForeignTable = "Parent"

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

r.Fields.Append ff

CurrentDb.Relations.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.
Jan 11 '08 #1
Share this Question
Share on Google+
10 Replies


P: n/a
On Jan 11, 5:10 pm, "Larry Linson" <boun...@localhost.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 :-)
Jan 11 '08 #2

P: n/a

"Richard@Home" <ri***********@gmail.comwrote in message
news:7b**********************************@k39g2000 hsf.googlegroups.com...
On Jan 11, 5:10 pm, "Larry Linson" <boun...@localhost.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 :-)
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

Jan 11 '08 #3

P: n/a
"Richard@Home" <ri***********@gmail.comwrote in
news:7b**********************************@k39g2000 hsf.googlegroups.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 "relationships" 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/
Jan 11 '08 #4

P: n/a
"Richard@Home" <ri***********@gmail.comwrote in
news:f4**********************************@l1g2000h sa.googlegroups.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/
Jan 22 '08 #5

P: n/a
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 :-)
Jan 23 '08 #6

P: n/a
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_Children"
.Table = "Children"
.ForeignTable = "Parent"

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

r.Fields.Append ff

CurrentDb.Relations.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 :-)
Jan 24 '08 #7

P: n/a
"Richard@Home" <ri***********@gmail.comwrote in news:e4324d0b-559b-
4a********************@e10g2000prf.googlegroups.co m:
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_Children"
.Table = "Children"
.ForeignTable = "Parent"

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

r.Fields.Append ff

CurrentDb.Relations.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.

Jan 24 '08 #8

P: n/a
"Richard@Home" <ri***********@gmail.comwrote in
news:e4**********************************@e10g2000 prf.googlegroups.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/
Jan 25 '08 #9

P: n/a
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 :-)
Jan 25 '08 #10

P: n/a
"Richard@Home" <ri***********@gmail.comwrote in
news:96**********************************@x69g2000 hsx.googlegroups.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/
Jan 25 '08 #11

This discussion thread is closed

Replies have been disabled for this discussion.