Re: [INTERFACES] Foreign Keys - Mailing list pgsql-interfaces

I'm experiencing your exactly same problem.
I'm using MSAccess97 to manage data contained in a PostgreSQL database and when
I add a new record through a form containing two sub-reports linked to the main
one through foreign keys I get two ODBC errors caused by MSAccess97 passing NULL
as ID for the two sub-reports.
I haven't found a way around it yet.. :(

Michael Davis wrote:

> I have narrowed the problem down a little.  Here is what I have discovered
> so far:
>
> -       sql.log (odbc trace facility) has no references to primary or
> foreign keys
>
> -       if I open the form on an exising record, it works great
>
> -       if I open the form on a new record, an error occurs in a select
> statement that basically locks up the subform
>
> -       the error occurs on "select orderlineid from orderlines where
> orderid = NULL"  the null is the result of being on a new or blank record.
> Access gets the orderid of the current record, it is null.  Access gets all
> orderlineids associated with the orderid.  This fails on the new record.
> When the form is on a valid record, Access then gets the orderline record
> for each orderlineid returned by the previous statement.  This seems to be a
> long way around getting data, but I don't have any control over this
> interaction.
>
> It appears that I cant add new records using Access97 with PostgreSQL as my
> database engine.  Has anyone used Access97 or other similar front end tools
> to interface with a PostgreSQL database?  Any suggestions on what I might
> try next to work around this problem?
>
>         -----Original Message-----
>         From:   Byron Nikolaidis [SMTP:byronn@insightdist.com]
>         Sent:   Thursday, February 25, 1999 2:12 PM
>         To:     Michael Davis
>         Cc:     'pgsql-interfaces@postgreSQL.org'
>         Subject:        Re: [INTERFACES] Foreign Keys
>
>         Michael Davis wrote:
>
>         > I have created the following trigger on the parent/master table
> (orders):
>         >
>         > CREATE TRIGGER Orderlines_fkey
>         >     BEFORE DELETE OR UPDATE ON Orders FOR EACH ROW
>         >     EXECUTE PROCEDURE check_foreign_key ();
>         >
>         > check_foreign_key() actually deletes rows from the orderlines
> table when
>         > every a row is deleted from orders.  This triggers works great.
> Access,
>         > however, is still not recognizing the foreign key relationship
> between
>         > orders and orderlines.  Any other suggestions?
>         >
>         > Thanks, Michael
>         >
>
>         Yeah, there is more we can try.  First of all, we need to verify
> that Access
>         is actually calling SQLForeignKeys.   Can you get a "trace" log of
> the
>         session?  This would be the "sql.log" file which contains all api
> calls to
>         odbc.  Make sure the file is clear beforehand.  Then run the test.
> Quit
>         Access, turn tracing off and there's your logfile.  You can send it
> to me.
>
>         It might also be helpful to have the commlog file (produced by the
>         driver)...usually "psqlodbc.log".   It may have a process id number
> appended
>         to the filename so make sure you get the right one.
>
>         Byron
>
>

--

C'ya!

Valerio Santinelli a.k.a. TANiS
[tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]