Re: Access 2007: Creating a local relationship between two remote ODBC tables
- From: frogsteaks@xxxxxxxxx
- Date: Tue, 22 Jan 2008 09:27:17 -0800 (PST)
On Jan 22, 5:54 am, "Richard@Home" <richardath...@xxxxxxxxx> wrote:
On Jan 11, 9:50 pm, "David W. Fenton" <XXXuse...@xxxxxxxxxxxxxxxxxxx>
wrote:
"Richard@Home" <richardath...@xxxxxxxxx> wrote innews:7b8f1996-fce6-44ba-8c06-2f09c6193242@xxxxxxxxxxxxxxxxxxxxxxxxxxx
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/
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.- Hide quoted text -
- 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.
.
- Follow-Ups:
- References:
- Access 2007: Creating a local relationship between two remote ODBC tables
- From: Richard@Home
- Re: Access 2007: Creating a local relationship between two remote ODBC tables
- From: Larry Linson
- Re: Access 2007: Creating a local relationship between two remote ODBC tables
- From: Richard@Home
- Re: Access 2007: Creating a local relationship between two remote ODBC tables
- From: David W. Fenton
- Re: Access 2007: Creating a local relationship between two remote ODBC tables
- From: Richard@Home
- Access 2007: Creating a local relationship between two remote ODBC tables
- Prev by Date: Re: A2K - how to detect Vista?
- Next by Date: Re: Access 2003: Output to Excel corrupts one, just one, cell's value
- Previous by thread: Re: Access 2007: Creating a local relationship between two remote ODBC tables
- Next by thread: Re: Access 2007: Creating a local relationship between two remote ODBC tables
- Index(es):
Relevant Pages
|
