Обсуждение: 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.bakken@tx.usda.gov
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.Bakken@tx.usda.gov]
> Sent: Thursday, May 03, 2001 4:09 PM
> To: pgsql-odbc@postgresql.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.bakken@tx.usda.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.bakken@tx.usda.gov
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.Bakken@tx.usda.gov] Sent: Thursday, May 03, 2001 4:09 PM To: pgsql-odbc@postgresql.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.bakken@tx.usda.gov ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
"Tom.Bakken" wrote: > > 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. > Which version of psqlodbc driver are you using ? Do you see the '#deleted's after insertion or updation ? regards, Hiroshi Inoue
The driver version is 06.50.00.00 Insight Distribution Systems PSQLODBC.DLL 4/01/99 The #Deleted's appear after insert. Hiroshi Inoue wrote: > "Tom.Bakken" wrote: > > > > 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. > > > > Which version of psqlodbc driver are you using ? > Do you see the '#deleted's after insertion or updation ? > > regards, > Hiroshi Inoue -- Tom Bakken
After trying several unsuccessful fixes, we created a time stamp field, and had access provide it using the now() function. Viola, success. It's not an elegant fix, but it works. For those who responded, much thanks. Cedar Cox wrote: > As you probably know, this is an Access problem/limitation/call it what > you want... Read again what Michael said. I guess you are updating the > data from a form? If so, make sure the primary key is included in the > data set used for the form, _even if it's not used_. The primary key is > used when doing updates, etc. Also, make sure Access knows that the > primary key is the primary key. You may find this FAQ helpful: > http://www.scw.org/pgaccess > > -Cedar > > On Fri, 4 May 2001, Tom.Bakken wrote: > > > 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: > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > -- 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.bakken@tx.usda.gov