Access 2007: Creating a local relationship between two remote ODBC tables
Richard@Home
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.
Larry Linson
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.)
If you are doing this just to get a diagram or schema to print, you can use
one of many drawing software packages. Microsoft software particularly
appropriate for the purpose is Visio Professional. MySQL, or the add-in to
MySQL that supports relationships likely has some way to document the
relationships.
But, defining the relationships for the ODBC back-end in Access will not
affect operation of your front-end database.
Larry Linson
Microsoft Access MVP
"Richard@Home" <richar...@gmail.com> wrote in message
news:14c3ae3a-9c58-4070...@v67g2000hse.googlegroups.com...
Richard@Home
> 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
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,
David W. Fenton
news:QpNhj.16$na4.6@trnddc05:
> MySQL, or the add-in to
> MySQL that supports relationships likely has some way to document
> the relationships.
It's not an add-in, it's a different table format (InnoDB instead of
MyISAM). And it's rather differently implemented than we're
accustomed to with Jet or SQL Server. It is nothing more than a
constraint on a field that is used to enforce RI.
One of the drawbacks of the InnoDB table format is that you
sacrifice full-text indexing (which can be a real problem for
databases driving web sites). Of course, maybe they've finally
gotten 'round to fixing that one, but I am underwhelmed with MySQL
because of how slow they've been to add features to it that real
databases have had forever.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David W. Fenton
news:7b8f1996-fce6-44ba...@k39g2000hsf.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.
Richard@Home
wrote:
> "Richard@Home" <richardath...@gmail.com> wrote innews:7b8f1996-fce6-44ba...@k39g2000hsf.googlegroups.co
I've used the CakePHP naming convention which looks like this:
tables names are plural
primary key is 'id'
foreign key is singlular_table_name_id
for example:
"articles" table
id INT (PK)
name VARCHAR(128)
user_id INT (FK to users->id)
I can (and have) written code to read the fields and work out what
points to what, its the creating of the relationships that fails.
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."
The important thing here is that I'm not trying to create the
relationships in the MySQL backend (the backend is irrelevant tbh),
I'm trying to create it in the front end.
frogs...@yahoo.com
>
> - Show quoted text -
Wow that breaks EVERY naming convention rule I have ever seen. Im not
saying it is eitehr right or wrong but it is convoluted.
Table names should be singular. A Client table defines a Client not a
Clients.
Never repeat a field name across your database EXCEPT in PK/FK
situations. "ID" for all PKs is not very good. The PK in the client
table should be named ClientID. That should also be used for the FK
for all tables linked to the Client table.
Make thinsg a LOT easier to link and build relationships.
ClientTypeID in the ClientType table is linked to ClientTypeID in the
Client table.
Again it may not be eitehr right or wrong but its a lot cleaner and
intuitive.
David W. Fenton
news:f48381b4-867a-4312...@l1g2000hsa.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?
Richard@Home
> 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@Home
> non-enforeced relationships on ODBC tables (that happen to be MySQL,
> in fact) using the user interface for doing so.
>
I can create relationships between the tables in the relationship
designer GUI by hand no problem.
Maybe the key here is 'non-enforced'?
Maybe the code I posted in my original post is creating 'enforced'
relations by default? Any ideas how I can change it to make 'non-
enforced' relations.
> Are you sure you're doing it correctly?
No, I'm not! That's why I'm here :-D
David W. Fenton
news:a5363ada-8e49-4718...@1g2000hsl.googlegroups.com:
>> 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.
>>
>
> I can create relationships between the tables in the relationship
> designer GUI by hand no problem.
>
> Maybe the key here is 'non-enforced'?
Certainly you can't create an ODBC enforced relationship via code,
of course. I assumed you trying to create a non-enforced
relationship.
> Maybe the code I posted in my original post is creating 'enforced'
> relations by default? Any ideas how I can change it to make 'non-
> enforced' relations.
I don't see the code. I don't know whether you're using DDL or DAO.
I'm certain it can be done in DAO if not in DDL.
Richard@Home
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 :-)
lyle fairfield
4a9c-a731-1...@e10g2000prf.googlegroups.com:
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
news:e4324d0b-559b-4a9c...@e10g2000prf.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).
Richard@Home
> 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 :-)
David W. Fenton
news:966c0cef-72e3-4a43...@x69g2000hsx.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")
--