Thread: ms-access tables relationships question
I'm new to ODBC world, and I need to use it for an application in MS Access where I need to view/manage PG data sources. I've tried to google but I haven't been able to find a clear answer to a question: can the Access relationship tools visualize the original table relationships in PG? I mean, simply, foreign key constraints. If not, I can set them up in Access but I suppose they don't affect the PG table structures, right? Thanks for any clarification, Giovanni
Hi Giovanni, MS Access does not import anything to Relationships. Only the basic table schema is visible in MS Access, such as ODBC-interpreted data types and primary key(s) (visible with the key logos in read-only Design view). When you link a table into Access, you can only do basic INSERT, UPDATE, and DELETE operations. If you change your source table schema (e.g., change a data type, rename/add/delete a column), then you need to update the linked table using the Linked Table Manager tool. You can add/edit your Relationships without any modification to the source database. -Mike On 15 September 2010 01:33, G. Allegri <giohappy@gmail.com> wrote: > I'm new to ODBC world, and I need to use it for an application in MS > Access where I need to view/manage PG data sources. I've tried to > google but I haven't been able to find a clear answer to a question: > can the Access relationship tools visualize the original table > relationships in PG? I mean, simply, foreign key constraints. If not, > I can set them up in Access but I suppose they don't affect the PG > table structures, right? > > Thanks for any clarification, > Giovanni > > -- > Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-odbc >
I supposed that, but I wanted to be sure. Thanks very much, Giovanni 2010/9/15 Mike Toews <mwtoews@gmail.com>: > Hi Giovanni, > > MS Access does not import anything to Relationships. Only the basic > table schema is visible in MS Access, such as ODBC-interpreted data > types and primary key(s) (visible with the key logos in read-only > Design view). > > When you link a table into Access, you can only do basic INSERT, > UPDATE, and DELETE operations. If you change your source table schema > (e.g., change a data type, rename/add/delete a column), then you need > to update the linked table using the Linked Table Manager tool. You > can add/edit your Relationships without any modification to the source > database. > > -Mike > > On 15 September 2010 01:33, G. Allegri <giohappy@gmail.com> wrote: >> I'm new to ODBC world, and I need to use it for an application in MS >> Access where I need to view/manage PG data sources. I've tried to >> google but I haven't been able to find a clear answer to a question: >> can the Access relationship tools visualize the original table >> relationships in PG? I mean, simply, foreign key constraints. If not, >> I can set them up in Access but I suppose they don't affect the PG >> table structures, right? >> >> Thanks for any clarification, >> Giovanni >> >> -- >> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-odbc >> >
Hi, do you know that there are 2 types of relationships in Access? Access can only manage strict relational integrity of tables within the same Access-File. Even there enforcement is optional. You can draw nice lines between tables that are still only hints for the query design tool, so it connects certain columns which you had linked before. Access generally can't enforce integrity when a table in DB1 is linked to a table in DB2. Though integrity gets watched if you import tables from another file when the integrity relations are defined between tables in the remote file. When your tables are managed by postgresql and have relations then postgresql keeps the integrity. You don't have to bother about it in Access. Am 15.09.2010 16:48, schrieb G. Allegri: > I supposed that, but I wanted to be sure. > Thanks very much, > Giovanni > > 2010/9/15 Mike Toews<mwtoews@gmail.com>: >> Hi Giovanni, >> >> MS Access does not import anything to Relationships. Only the basic >> table schema is visible in MS Access, such as ODBC-interpreted data >> types and primary key(s) (visible with the key logos in read-only >> Design view). >> >> When you link a table into Access, you can only do basic INSERT, >> UPDATE, and DELETE operations. If you change your source table schema >> (e.g., change a data type, rename/add/delete a column), then you need >> to update the linked table using the Linked Table Manager tool. You >> can add/edit your Relationships without any modification to the source >> database. >> >> -Mike >> >> On 15 September 2010 01:33, G. Allegri<giohappy@gmail.com> wrote: >>> I'm new to ODBC world, and I need to use it for an application in MS >>> Access where I need to view/manage PG data sources. I've tried to >>> google but I haven't been able to find a clear answer to a question: >>> can the Access relationship tools visualize the original table >>> relationships in PG? I mean, simply, foreign key constraints. If not, >>> I can set them up in Access but I suppose they don't affect the PG >>> table structures, right? >>> >>> Thanks for any clarification, >>> Giovanni >>> >>> -- >>> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-odbc >>>
Hi, > When your tables are managed by postgresql and have relations then > postgresql keeps the integrity. > You don't have to bother about it in Access. Yes, you're right. My problem is that my customer has a complex application built in Access (with lots of queries, masks, macros, etc.) and he asked me to port only the data to Postgresql (for other requirements), keeping the 'engine' inside Access. So I have to deal with their actual structure and constraints in Access, and switch the data integrity management to a hybrid system. Of course I will design the various constraints inside Postgresql, but I have to decide how to manage the Access side... Giovanni