Re: Access 2007: Creating a local relationship between two remote ODBC tables

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

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" <richardathome@xxxxxxxxx> wrote in message
news:14c3ae3a-9c58-4070-9b2e-27eb63b3259d@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.


.

Relevant Pages

  • WxWindows, MySQL and Windows98
    ... I'm using WxWindows to access a MySQL database on Windows98. ... I get an "ODBC parameter settings" window. ...
    (comp.programming)
  • Re: Nameless CREATE
    ... The starting point is either an existing database or one that is ... "Describe" involves issuing an SQLDescribeCol function to ODBC ... essentially much the same as character data type apart from all 8 bits ... ALTER TABLE tablename ADD COLUMN BinaryColumn BINARY ...
    (comp.lang.forth)
  • Re: How can I connect to MySQL?
    ... Thank you so much for your recomendation of this ODBC tool. ... I'm using MySQL API to connect database under C++. ... > Click on the 'Machine Data Sources' tab of the 'Select Data Source' ...
    (microsoft.public.vc.mfc)
  • Re: fields queries and utter disaster
    ... Unfortunately the \c ODBC DATABASE field switch is not supported in Word ... of course supports ODBC connections but Word ... Mac Word Test ... suggests that it should be possible to use query files (cf. .qry/.dqy ...
    (microsoft.public.mac.office.word)
  • Re: [VW 7.3.1] ODBCConnection
    ... I played around with a few combinations on the connection string and ... with a trusted SQL Server connection. ... there are differences for database connects. ... > any database specific odbc drivers. ...
    (comp.lang.smalltalk)