Solved See relationships from PostgresSQL in Access (1 Viewer)

[jaryszek]

Hi,

i want to somehow see relationships in Access from Postgres.

Access can be linked with Postgres and i have for example DDL in Postgres like here:

CREATE DATABASE database;

CREATE TYPE t_name AS
(      first    VARCHAR(30),
       last     VARCHAR(60)
);

CREATE TABLE telephone_m
(   tnumber  VARCHAR(15) NOT NULL UNIQUE
);

CREATE TABLE people
(   curp        CHAR(18)    NOT NULL PRIMARY KEY,
    pname       t_name      NOT NULL,
    birth_date  DATE        NOT NULL,
    telephone_m VARCHAR(15) REFERENCES telephone_m
);

CREATE TABLE clients
(   curp        CHAR(18)    NOT NULL PRIMARY KEY,
    cid         SERIAL      NOT NULL REFERENCES cards, 
    clocation   VARCHAR(29)
)   INHERITS (people);

CREATE TABLE cards
(   cid         BIGSERIAL   NOT NULL PRIMARY KEY,
    curp        CHAR(18)    NOT NULL REFERENCES clients,
    trips       SMALLINT,
    distance    NUMERIC,
    points      NUMERIC
);

CREATE TABLE drivers
(   curp        CHAR(18)    NOT NULL PRIMARY KEY,
    rfc         CHAR(22)    NOT NULL UNIQUE,
    adress      t_adress    NOT NULL
)   INHERITS (people);

Can Access somehow convert postgresql to Access SQL and make relationships between tables?

Jacek

 

[Minty]

No, the relationships are always defined in the BE system, Access simply becomes a viewing tool.
I suspect if you pull related tables into a query Access might be clever enough to join anything with referential settings, but I don't know with PostgreSql?

 

[jaryszek]

thank you Minty,

I suspect if you pull related tables into a query Access

what do you mean ?
Just rewrite postgres sql into access sql ?

Jacek

 

[Minty]

No sorry, if you created a query in Access, it might pick up the relationships set up in the BE RDBMS.

 

[jaryszek]

ok thanks but what kind of query?

I used odbc connection to link tables with postgres (what is annoying i am getting public_tableName as table name).
created foreign key in postgres and tested it in access:

and this is perfect. So i know tjat i have RI (relationships integrity in BE) but in Access i am not seeing this relationship:

question is it is possible to do this somehow automatically?

Jacek

 

[Minty]

No - The relationships window in Access is for only for Access tables, it has no effect or use outside of that.
If you want to make a pretty picture for your boss, simply create a query in Access with all the tables and join them accordingly if the joins don't appear magically.

 

[jaryszek]

thank you Minty very much,

ok i understand. So there is no other kind of tools or methods to do this? I have to make rels manually in Access ?

Jacek

 

[isladogs]

Whilst you can make 'relationships' with BE tables in the FE. it is effectively pointless doing so as they won't be enforced in the FE.
As already stated, relationships are made and enforced in the BE

 

[jaryszek]

thank you Colin!

ah in postgres there is no relationships/diagram viewer unfortunately i thought it will be easier to see relationships in Access way...

Best Wishes,
Jacek

 

[Pat Hartman]

Your original question was about viewing relationships. Now you want to make them. Which is it?

Using a pass-through query, you can send DDL to the RDBMS to alter the schema provided you have sufficient permissions to do so. You could also do it using ADO.

As to viewing, You have to figure out which system table holds the relationships and query that table. Someone more familiar with Postgre may be able to tell you which system table to use. OR, just open them until you find the right one.

 

[jaryszek]

Thank you Pat.

 

[Galaxiom]

Access SQL can create relationships. It can also be done in VBA.

In theory one could parse the PostGres commands and translate them to Access queries or VBA code. But I can't think of a good reason to bother.

 

[jaryszek]

Thank you Galaxiom, yes i have to resign from this idea. Seems to be without sense.

Yes i am tring to create relationships using Access SQL in this topic:
https://www.access-programmers.co.u...to-access-database.315491/page-2#post-1739950

Thank you,
Best Wishes,
Jacek

 

[Minty]

I doubt anyone has tried a great deal, as relationships are a one-off set up part of the design of an access database, and achieved with ease using the relationship UI.

In SQL Server or other DBMS this is much more prevalent as scripts to update production schemas from development are commonplace.