Thread: RE: [INTERFACES] Foreign Keys
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
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? > > Logs, logs, logs (see my last response). I don't know how else to put it! Its next to impossible to figure things out without the logs. Byron
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]