Re: MS Access97 Postgres ODBC problems

The table we're having trouble with is a very important one that most other
tables in the database are either directly or indirectly linked to. I'm using
a sequence (set up as emp_id SERIAL) when the table is created. I then set the
sequence starting point as follows:

CREATE FUNCTION employees_id_max()
RETURNS INT4 AS '
SELECT max(emp_id)
FROM employees
WHERE emp_id < 1000000'
LANGUAGE 'sql';
BEGIN;
COPY employees FROM '/home/postgreSQL/infosystems/copy/employees';

-- Reset sequence:
SELECT setval('employees_emp_id_seq', employees_id_max());
END;

as outlined in Momjian's excellent reference. The table can be updated from
the web, psql, or access97. This may not be the best way to set up a table
used in this way. Maybe somebody can straighten me out on primary keys.

We have no problem when updating from the first two methods, but access 97
keeps inserting the '#deleted's. We checked and the emp_id field does appear
on the form. For that matter, we edit the table directly without the form and
still get the error messages.

I've turned on logging and checked the output. It can generate a ton of output
in a short amount of time. I noted that the first call to postgres is from
Access97's admin id, which indicates authentication fails. The next entry
shows the users id attempting to connect to an Access97 system table, MSysConf
with postgres complaining that the table doesn't exist.

Then follows a series of selects in 10 row increments, sometimes with where
clauses that don't make much sense like repeated emp_id = 1 or emp_id = 1 or
.....

Something's definitely awry.

Michael Davis wrote:

> Is the primary key of the table you are updating in the data displayed on
> the form? Access has interesting issues when the primary key is not a part
> of data being inserted and/or updated.
>
> -----Original Message-----
> From: Tom.Bakken [SMTP:Tom(dot)Bakken(at)tx(dot)usda(dot)gov]
> Sent: Thursday, May 03, 2001 4:09 PM
> To: pgsql-odbc(at)postgresql(dot)org
> Subject: [ODBC] MS Access97 Postgres ODBC problems
>
> I'm using postgres 6.5.2 on Red Hat Linux 6.1 When updating tables in
> Access97 on a Windows NT 4.0 we've been getting '#deleted's showing up
> on records we're adding to a table.
>
> I've run vacuum on the tables in question and the system tables. The
> problem won't go away.
>
> Otherwise the system works great.
>
> --
> Tom Bakken
> Information Resource Manager
> USDA, Rural Development
> 101 South Main, Suite 102
> Temple, TX 76501
>
> Phone: 254-742-9726 Fax: 254-742-9709
> Email: tom(dot)bakken(at)tx(dot)usda(dot)gov
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Tom Bakken
Information Resource Manager
USDA, Rural Development
101 South Main, Suite 102
Temple, TX 76501

Phone: 254-742-9726 Fax: 254-742-9709
Email: tom(dot)bakken(at)tx(dot)usda(dot)gov