pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedData showing up as #Deleted in Access
9+ messages / 6 participants
[nested] [flat]
* Data showing up as #Deleted in Access
@ 2005-12-01 15:35 Chris Moore <chrismoore@surewest.net>
2005-12-01 17:24 ` Re: Data showing up as #Deleted in Access Campbell, Greg <greg.campbell@us.michelin.com>
0 siblings, 1 reply; 9+ messages in thread
From: Chris Moore @ 2005-12-01 15:35 UTC (permalink / raw)
To: pgsql-odbc@postgresql.org
I'm trying to get ODBC working for the first time so that I can access
a Postgres database from Access. I'm running PostgrSQL 8.0.3, PostgreSQL
ODBC 8.01.01.01, and Access 2003 version 11.6355.6360 SP1. The database
is on a system running RedHat Linux 2.6.9-6.37-EL and the client is running
Windows XP SP2.
When I try to open the ODBC data source in Access I see the list of tables
correctly. If I pick one of the tables and open it I get the correct field
names and the correct number of rows, but every field in every row says
"#Deleted"
instead of having data.
Can anyone suggest what might be wrong?
Thanks,
Chris
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Data showing up as #Deleted in Access
2005-12-01 15:35 Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
@ 2005-12-01 17:24 ` Campbell, Greg <greg.campbell@us.michelin.com>
2005-12-01 18:40 ` Re: Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
0 siblings, 1 reply; 9+ messages in thread
From: Campbell, Greg @ 2005-12-01 17:24 UTC (permalink / raw)
To: Chris Moore <chrismoore@surewest.net>; +Cc: pgsql-odbc@postgresql.org
Access is trying to maintain an updateable recordset when you just open a table.
For an updateable table it need to be able to identify each record, in case you trying to change a value
on the record.
So it tries to determine if there is a primary key or by matching each field, can it determine a virtual
primary key.
If it knows the record "was/is" there but cannot determine a primary key, it says deleted -- This may not
be exact in the detail, but seems to be the principle.
The situation is usually aided by having tables with good primary keys. If you change the indexes or keys,
update the linked table in Access.
It is also helped sometimes by turning on row-versioning for the connection -- I usually use the ODBC
administrator and then use Access to refresh the linked tables, or drop and re-link the tables.
If these steps do not help, post a response for more help. You can also search this mailing lists website
archives for the "deleted" thing.
Chris Moore wrote:
> I'm trying to get ODBC working for the first time so that I can access
> a Postgres database from Access. I'm running PostgrSQL 8.0.3, PostgreSQL
> ODBC 8.01.01.01, and Access 2003 version 11.6355.6360 SP1. The database
> is on a system running RedHat Linux 2.6.9-6.37-EL and the client is running
> Windows XP SP2.
>
> When I try to open the ODBC data source in Access I see the list of tables
> correctly. If I pick one of the tables and open it I get the correct field
> names and the correct number of rows, but every field in every row says
> "#Deleted"
> instead of having data.
>
> Can anyone suggest what might be wrong?
>
> Thanks,
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
Attachments:
[text/x-vcard] greg.campbell.vcf (239B, ../../438F31C3.4020205@us.michelin.com/2-greg.campbell.vcf)
download | inline:
begin:vcard
fn:Greg Campbell
n:Campbell;Greg
org:Michelin North America - US5 Lexington;ENG-ASE
email;internet:greg.campbell@us.michelin.com
title:ASE Systems Engineer
tel;work:803-951-5561/x75561
x-mozilla-html:FALSE
version:2.1
end:vcard
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Data showing up as #Deleted in Access
2005-12-01 15:35 Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
2005-12-01 17:24 ` Re: Data showing up as #Deleted in Access Campbell, Greg <greg.campbell@us.michelin.com>
@ 2005-12-01 18:40 ` Chris Moore <chrismoore@surewest.net>
2005-12-01 19:59 ` Re: Data showing up as #Deleted in Access Ludek Finstrle <luf@pzkagis.cz>
2005-12-02 10:02 ` Re: Data showing up as #Deleted in Access Zlatko Matic <zlatko.matic1@sb.t-com.hr>
0 siblings, 2 replies; 9+ messages in thread
From: Chris Moore @ 2005-12-01 18:40 UTC (permalink / raw)
To: 'Campbell, Greg' <greg.campbell@us.michelin.com>; +Cc: pgsql-odbc@postgresql.org
I verified that the primary key was correct, and also turned on
row versioning, but still got the same thing.
Then I noticed that it was only one particular table that was having
problems. It occurred to me that this table had several fields of
type "text", one of which had rows in which this field had close to
a thousand characters in it. I changed all the "text" fields to
"varchar", making the one big one a varchar(2048). That seems to have
fixed the problem.
By the way, I did try searching for "#Deleted", but the search engine
tries to be helpful and also find instances of "delete", "deleting",
etc. There were just too many hits to be able to weed through them
all and find the relevant ones.
Thanks for your help.
Chris
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Data showing up as #Deleted in Access
2005-12-01 15:35 Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
2005-12-01 17:24 ` Re: Data showing up as #Deleted in Access Campbell, Greg <greg.campbell@us.michelin.com>
2005-12-01 18:40 ` Re: Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
@ 2005-12-01 19:59 ` Ludek Finstrle <luf@pzkagis.cz>
2005-12-02 10:19 ` Re: Data showing up as #Deleted in Access Andrus <eetasoft@online.ee>
1 sibling, 1 reply; 9+ messages in thread
From: Ludek Finstrle @ 2005-12-01 19:59 UTC (permalink / raw)
To: Chris Moore <chrismoore@surewest.net>; +Cc: pgsql-odbc@postgresql.org
> Then I noticed that it was only one particular table that was having
> problems. It occurred to me that this table had several fields of
> type "text", one of which had rows in which this field had close to
It seems current ODBC driver has problem with "text". Please could
you fill bug report with attached mylog output to psqlodbc bug
tracker at pgFoundry.org?
You could attach minimalistic exe file with table specification. It
helps us reproduce the problem by us.
Thanks
Luf
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Data showing up as #Deleted in Access
2005-12-01 15:35 Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
2005-12-01 17:24 ` Re: Data showing up as #Deleted in Access Campbell, Greg <greg.campbell@us.michelin.com>
2005-12-01 18:40 ` Re: Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
2005-12-01 19:59 ` Re: Data showing up as #Deleted in Access Ludek Finstrle <luf@pzkagis.cz>
@ 2005-12-02 10:19 ` Andrus <eetasoft@online.ee>
2005-12-02 10:35 ` Re: Data showing up as #Deleted in Access Miguel Juan <mjuan@cibal.es>
2005-12-02 10:38 ` Re: Data showing up as #Deleted in Access Ludek Finstrle <luf@pzkagis.cz>
0 siblings, 2 replies; 9+ messages in thread
From: Andrus @ 2005-12-02 10:19 UTC (permalink / raw)
To: pgsql-odbc@postgresql.org
"Ludek Finstrle" <luf@pzkagis.cz> wrote in message
news:20051201195932.GA16735@soptik.pzkagis.cz...
>> Then I noticed that it was only one particular table that was having
>> problems. It occurred to me that this table had several fields of
>> type "text", one of which had rows in which this field had close to
>
> It seems current ODBC driver has problem with "text". Please could
> you fill bug report with attached mylog output to psqlodbc bug
> tracker at pgFoundry.org?
> You could attach minimalistic exe file with table specification. It
> helps us reproduce the problem by us.
Luf,
my problem in thread "application crash after error" occurs also only with
text field.
So this may be the same problem .
Andrus
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Data showing up as #Deleted in Access
2005-12-01 15:35 Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
2005-12-01 17:24 ` Re: Data showing up as #Deleted in Access Campbell, Greg <greg.campbell@us.michelin.com>
2005-12-01 18:40 ` Re: Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
2005-12-01 19:59 ` Re: Data showing up as #Deleted in Access Ludek Finstrle <luf@pzkagis.cz>
2005-12-02 10:19 ` Re: Data showing up as #Deleted in Access Andrus <eetasoft@online.ee>
@ 2005-12-02 10:35 ` Miguel Juan <mjuan@cibal.es>
1 sibling, 0 replies; 9+ messages in thread
From: Miguel Juan @ 2005-12-02 10:35 UTC (permalink / raw)
To: pgsql-odbc@postgresql.org
Hello,
There is a workaround, just check the "Show OID column" and "Fake index" in
the ODBC configuration.
This used to work for me (some time ago, I must say).
Regards,
Miguel Juan
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Data showing up as #Deleted in Access
2005-12-01 15:35 Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
2005-12-01 17:24 ` Re: Data showing up as #Deleted in Access Campbell, Greg <greg.campbell@us.michelin.com>
2005-12-01 18:40 ` Re: Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
2005-12-01 19:59 ` Re: Data showing up as #Deleted in Access Ludek Finstrle <luf@pzkagis.cz>
2005-12-02 10:19 ` Re: Data showing up as #Deleted in Access Andrus <eetasoft@online.ee>
@ 2005-12-02 10:38 ` Ludek Finstrle <luf@pzkagis.cz>
2005-12-02 11:24 ` Re: Data showing up as #Deleted in Access Andrus <eetasoft@online.ee>
1 sibling, 1 reply; 9+ messages in thread
From: Ludek Finstrle @ 2005-12-02 10:38 UTC (permalink / raw)
To: Andrus <eetasoft@online.ee>; +Cc: pgsql-odbc@postgresql.org
> my problem in thread "application crash after error" occurs also only with
> text field.
> So this may be the same problem .
I don't think so. Your problem is specific. I try to describe it as
good as I can. I hope Dave or another guru can show me the right way.
I can correct your problem but I don't know if it doesn't break
another scenario ...
I don't give my hand off your problem. I only need advice from someone
who knows ODBC (not psqlODBC) better.
Hmmm. I get idea right now. I'll try it againist MySQL. It's ODBC is
open source so I can learn from it ...
Please could you change your example to support MySQL ODBC?
Maybe (or maybe not) it helps.
So please be patient I'm doing everything I can
Luf
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Data showing up as #Deleted in Access
2005-12-01 15:35 Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
2005-12-01 17:24 ` Re: Data showing up as #Deleted in Access Campbell, Greg <greg.campbell@us.michelin.com>
2005-12-01 18:40 ` Re: Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
2005-12-01 19:59 ` Re: Data showing up as #Deleted in Access Ludek Finstrle <luf@pzkagis.cz>
2005-12-02 10:19 ` Re: Data showing up as #Deleted in Access Andrus <eetasoft@online.ee>
2005-12-02 10:38 ` Re: Data showing up as #Deleted in Access Ludek Finstrle <luf@pzkagis.cz>
@ 2005-12-02 11:24 ` Andrus <eetasoft@online.ee>
0 siblings, 0 replies; 9+ messages in thread
From: Andrus @ 2005-12-02 11:24 UTC (permalink / raw)
To: pgsql-odbc@postgresql.org
Ludek,
> Hmmm. I get idea right now. I'll try it againist MySQL. It's ODBC is
> open source so I can learn from it ...
> Please could you change your example to support MySQL ODBC?
> Maybe (or maybe not) it helps.
If you change the connection string in testsql.prg and remove WITHOUT OIDS
clauses in CREATE TABLE commands I think this sample must work with other
odbc drivers also.
Also, I do'nt know will mysql support CREATE temp TABLE clause. If it does
not temp can be removed also. So the TEXT command in testsql.prg may be
TEXT TO csql TEXTMERGE noshow
CREATE TABLE klient (
kood char(12),
nimi char(70),
info text);
CREATE UNIQUE INDEX klient_nimi_unique_idx ON klient(nimi);
CREATE temp TABLE dok( doktyyp char(1) );
insert into klient (kood,nimi) values ('AKU', 'Akuexpert O');
endtext
also the line in former testsql.prg
insert into testk (kood,nimi,info) values ('AKU', 'Akuexpert OÜ','')
should me changed to
insert into testk (kood,nimi,info) values ('AKU', 'Akuexpert O','')
to avoid possible issues with Ü character. After making changes main.exe
compiles and runs testsql.prg automatically.
I do'nt have mysql server access. Please re-confirm if you need that I must
verify this code in mysql. In this
case I must install mysql in my development computer. Or maybe there is some
mysql test server accessible over internet?
> So please be patient I'm doing everything I can
Thank you. I have one unchecked thought. If null instead of empty string is
passed as value to text type column, maybe the error does not occur.
Andrus.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Data showing up as #Deleted in Access
2005-12-01 15:35 Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
2005-12-01 17:24 ` Re: Data showing up as #Deleted in Access Campbell, Greg <greg.campbell@us.michelin.com>
2005-12-01 18:40 ` Re: Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
@ 2005-12-02 10:02 ` Zlatko Matic <zlatko.matic1@sb.t-com.hr>
1 sibling, 0 replies; 9+ messages in thread
From: Zlatko Matic @ 2005-12-02 10:02 UTC (permalink / raw)
To: Chris Moore <chrismoore@surewest.net>; 'Campbell, Greg' <greg.campbell@us.michelin.com>; +Cc: pgsql-odbc@postgresql.org
Hello.
Just redesign your database. You need to have numeric primary key field in
every table. Don't use any other type as primary key, especially not any
kind of textual fields, because Access won't be able to refresh data
properly and you will face "#Deleted", which is very common problem with
Access/ODBC linked tables. You can find an article about "#Deleted" on
www.microsoft.com, too.
The best way to avoid "#Deleted" is to have a serial field (autonumber) as
primary key, in every table. That will solve your problem forever.
Zlatko
----- Original Message -----
From: "Chris Moore" <chrismoore@surewest.net>
To: "'Campbell, Greg'" <greg.campbell@us.michelin.com>
Cc: <pgsql-odbc@postgresql.org>
Sent: Thursday, December 01, 2005 7:40 PM
Subject: Re: [ODBC] Data showing up as #Deleted in Access
>I verified that the primary key was correct, and also turned on
> row versioning, but still got the same thing.
>
> Then I noticed that it was only one particular table that was having
> problems. It occurred to me that this table had several fields of
> type "text", one of which had rows in which this field had close to
> a thousand characters in it. I changed all the "text" fields to
> "varchar", making the one big one a varchar(2048). That seems to have
> fixed the problem.
>
> By the way, I did try searching for "#Deleted", but the search engine
> tries to be helpful and also find instances of "delete", "deleting",
> etc. There were just too many hits to be able to weed through them
> all and find the relevant ones.
>
> Thanks for your help.
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2005-12-02 11:24 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-01 15:35 Data showing up as #Deleted in Access Chris Moore <chrismoore@surewest.net>
2005-12-01 17:24 ` Campbell, Greg <greg.campbell@us.michelin.com>
2005-12-01 18:40 ` Chris Moore <chrismoore@surewest.net>
2005-12-01 19:59 ` Ludek Finstrle <luf@pzkagis.cz>
2005-12-02 10:19 ` Andrus <eetasoft@online.ee>
2005-12-02 10:35 ` Miguel Juan <mjuan@cibal.es>
2005-12-02 10:38 ` Ludek Finstrle <luf@pzkagis.cz>
2005-12-02 11:24 ` Andrus <eetasoft@online.ee>
2005-12-02 10:02 ` Zlatko Matic <zlatko.matic1@sb.t-com.hr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox