Thread: Problems with Access XP and text primary keys

Problems with Access XP and text primary keys

From

"Ben Trewern"

Date:

15 January 2005, 05:53:05

I've been using Postgres 8.0rc5 with the pgsqlODBC 8.00.00.03 driver and am
having some problems.

The database works correctly for some time but suddenly and for no reason I
can find I get #Deleted in some of the fields. These tables seem to be those
which have text primary keys. I've looked at the psqlodbcXXXX.log and it
seems that I get:

conn=162845400, query='SELECT "mw"."users"."user_name" FROM "mw"."users" '
[ fetched 13 rows ]

conn=162845400, query='SELECT
"user_name","entered_ts","entered_user","last_changed","last_user","full_name","area_id","xmin"
FROM "mw"."users" WHERE "user_name" = 'b' OR "user_name" = 'c' OR
"user_name" = 'm' OR "user_name" = 'b' OR "user_name" = 'p' OR "user_name" =
't' OR "user_name" = 'j' OR "user_name" = 'r' OR "user_name" = 'f' OR
"user_name" = 'j''
[ fetched 0 rows ]

conn=162845400, query='SELECT
"user_name","entered_ts","entered_user","last_changed","last_user","full_name","area_id","xmin"
FROM "mw"."users" WHERE "user_name" = 'p' OR "user_name" = 'p' OR
"user_name" = 'j' OR "user_name" = 'j' OR "user_name" = 'j' OR "user_name" =
'j' OR "user_name" = 'j' OR "user_name" = 'j' OR "user_name" = 'j' OR
"user_name" = 'j''
[ fetched 0 rows ]

instead of when working correctly:

conn=170998744, query='SELECT "mw"."users"."user_name" FROM "mw"."users" '
[ fetched 13 rows ]

conn=170998744, query='SELECT
"user_name","entered_ts","entered_user","last_changed","last_user","full_name","area_id","xmin"
FROM "mw"."users" WHERE "user_name" = 'btrewern' OR "user_name" =
'chappellj' OR "user_name" = 'mcwilliamsf' OR "user_name" = 'ben' OR
"user_name" = 'postgres' OR "user_name" = 'timc' OR "user_name" = 'jasonc'
OR "user_name" = 'robh' OR "user_name" = 'francism' OR "user_name" =
'janeh''
[ fetched 10 rows ]

conn=170998744, query='SELECT
"user_name","entered_ts","entered_user","last_changed","last_user","full_name","area_id","xmin"
FROM "mw"."users" WHERE "user_name" = 'pons_temp' OR "user_name" = 'pauls'
OR "user_name" = 'jeremyw' OR "user_name" = 'jeremyw' OR "user_name" =
'jeremyw' OR "user_name" = 'jeremyw' OR "user_name" = 'jeremyw' OR
"user_name" = 'jeremyw' OR "user_name" = 'jeremyw' OR "user_name" =
'jeremyw''
[ fetched 3 rows ]

The table "mw"."users" is created as:

CREATE TABLE users
(
  user_name short_text_dom PRIMARY KEY,
  entered_ts timestamp DEFAULT ('now'::text)::timestamp(6) with time zone,
  entered_user short_text_dom DEFAULT "current_user"(),
  last_changed timestamp,
  last_user short_text_dom,
  full_name text,
  area_id int4 NOT NULL
);

and

"mw"."short_text_dom" is:

CREATE DOMAIN short_text_dom
  AS varchar(250);

Does anyone have any ideas?

Regards,

Ben Trewern

Re: Problems with Access XP and text primary keys

From

"Ben Trewern"

Date:

21 January 2005, 07:37:38

Is this just me?


"Ben Trewern" <bendottrewern@mowlem.com> wrote in message
news:cs8rha$2hv9$1@news.hub.org...
> I've been using Postgres 8.0rc5 with the pgsqlODBC 8.00.00.03 driver and
> am having some problems.
>
> The database works correctly for some time but suddenly and for no reason
> I can find I get #Deleted in some of the fields. These tables seem to be
> those which have text primary keys. I've looked at the psqlodbcXXXX.log
> and it seems that I get:
>
> conn=162845400, query='SELECT "mw"."users"."user_name" FROM "mw"."users" '
> [ fetched 13 rows ]
>
> conn=162845400, query='SELECT
> "user_name","entered_ts","entered_user","last_changed","last_user","full_name","area_id","xmin"
> FROM "mw"."users" WHERE "user_name" = 'b' OR "user_name" = 'c' OR
> "user_name" = 'm' OR "user_name" = 'b' OR "user_name" = 'p' OR "user_name"
> = 't' OR "user_name" = 'j' OR "user_name" = 'r' OR "user_name" = 'f' OR
> "user_name" = 'j''
> [ fetched 0 rows ]
>
> conn=162845400, query='SELECT
> "user_name","entered_ts","entered_user","last_changed","last_user","full_name","area_id","xmin"
> FROM "mw"."users" WHERE "user_name" = 'p' OR "user_name" = 'p' OR
> "user_name" = 'j' OR "user_name" = 'j' OR "user_name" = 'j' OR "user_name"
> = 'j' OR "user_name" = 'j' OR "user_name" = 'j' OR "user_name" = 'j' OR
> "user_name" = 'j''
> [ fetched 0 rows ]
>
> instead of when working correctly:
>
> conn=170998744, query='SELECT "mw"."users"."user_name" FROM "mw"."users" '
> [ fetched 13 rows ]
>
> conn=170998744, query='SELECT
> "user_name","entered_ts","entered_user","last_changed","last_user","full_name","area_id","xmin"
> FROM "mw"."users" WHERE "user_name" = 'btrewern' OR "user_name" =
> 'chappellj' OR "user_name" = 'mcwilliamsf' OR "user_name" = 'ben' OR
> "user_name" = 'postgres' OR "user_name" = 'timc' OR "user_name" = 'jasonc'
> OR "user_name" = 'robh' OR "user_name" = 'francism' OR "user_name" =
> 'janeh''
> [ fetched 10 rows ]
>
> conn=170998744, query='SELECT
> "user_name","entered_ts","entered_user","last_changed","last_user","full_name","area_id","xmin"
> FROM "mw"."users" WHERE "user_name" = 'pons_temp' OR "user_name" = 'pauls'
> OR "user_name" = 'jeremyw' OR "user_name" = 'jeremyw' OR "user_name" =
> 'jeremyw' OR "user_name" = 'jeremyw' OR "user_name" = 'jeremyw' OR
> "user_name" = 'jeremyw' OR "user_name" = 'jeremyw' OR "user_name" =
> 'jeremyw''
> [ fetched 3 rows ]
>
> The table "mw"."users" is created as:
>
> CREATE TABLE users
> (
>  user_name short_text_dom PRIMARY KEY,
>  entered_ts timestamp DEFAULT ('now'::text)::timestamp(6) with time zone,
>  entered_user short_text_dom DEFAULT "current_user"(),
>  last_changed timestamp,
>  last_user short_text_dom,
>  full_name text,
>  area_id int4 NOT NULL
> );
>
> and
>
> "mw"."short_text_dom" is:
>
> CREATE DOMAIN short_text_dom
>  AS varchar(250);
>
> Does anyone have any ideas?
>
> Regards,
>
> Ben Trewern
>

Re: Problems with Access XP and text primary keys

From

laurie.burrow@powerconv.alstom.com

Date:

21 January 2005, 12:23:22

"Ben Trewern" <bendottrewern@mowlem.com> wrote in message

> I've been using Postgres 8.0rc5 with the pgsqlODBC 8.00.00.03 driver and
> am having some problems.
>
> The database works correctly for some time but suddenly and for no reason
> I can find I get #Deleted in some of the fields. These tables seem to be
> those which have text primary keys. I've looked at the psqlodbcXXXX.log
> and it seems that I get:

I believe Access will give this mesage when the primary key of a record has
been  modified. I think the text type is mapped to memo fields which Access
cannot use as a primary key. If  a table does not have a primary key it
will use the fields of the original record as sort of combined key to
identify the record. If any field is changed this will then generate the
message. It may be that if you set the Row Versioning flag in the postgres
ODBC driver this may resolve the problem.

Laurie

:.________________
CONFIDENTIALITY : This  e-mail  and  any attachments are confidential and
may be privileged. If  you are not a named recipient, please notify the
sender immediately and do not disclose the contents to another person, use
it for any purpose or store or copy the information in any medium.

Re: Problems with Access XP and text primary keys

From

"Ben Trewern"

Date:

25 January 2005, 20:35:59

Sorry I don't think I explained the problem clearly.  The primary key is a
varchar(250) which I think I should be able to use.  It is defined as a
domain.

If you look at the log entries from the previous post, while the app works
correctly the WHERE clause looks like:

WHERE "user_name" = 'btrewern' OR "user_name" =  'chappellj' OR "user_name"
= 'mcwilliamsf' OR ....

Then suddenly, and I can't explain why, it becomes:

WHERE "user_name" = 'b' OR "user_name" = 'c' OR  "user_name" = 'm' OR ..

Just the first letter of the primary key is used.  This then gives the
errors described.

Any Ideas?

BTW Ive changed back to using the

Ben.


<laurie.burrow@powerconv.alstom.com> wrote in message
news:OF1BADDF84.F5CD0F57-ON80256F90.0032A34C-80256F90.0033829C@transport.alstom.com...
> "Ben Trewern" <bendottrewern@mowlem.com> wrote in message
>
>> I've been using Postgres 8.0rc5 with the pgsqlODBC 8.00.00.03 driver and
>> am having some problems.
>>
>> The database works correctly for some time but suddenly and for no reason
>> I can find I get #Deleted in some of the fields. These tables seem to be
>> those which have text primary keys. I've looked at the psqlodbcXXXX.log
>> and it seems that I get:
>
> I believe Access will give this mesage when the primary key of a record
> has
> been  modified. I think the text type is mapped to memo fields which
> Access
> cannot use as a primary key. If  a table does not have a primary key it
> will use the fields of the original record as sort of combined key to
> identify the record. If any field is changed this will then generate the
> message. It may be that if you set the Row Versioning flag in the postgres
> ODBC driver this may resolve the problem.
>
> Laurie
>
> :.________________
> CONFIDENTIALITY : This  e-mail  and  any attachments are confidential and
> may be privileged. If  you are not a named recipient, please notify the
> sender immediately and do not disclose the contents to another person, use
> it for any purpose or store or copy the information in any medium.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>