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
