Skip site navigation (1) Skip section navigation (2)

Re: [HELP] MSAccess picks wrong keys

From: Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com>
To: Aaron Spike <sauron(at)mlc-wels(dot)edu>, pgsql-odbc(at)postgresql(dot)org
Subject: Re: [HELP] MSAccess picks wrong keys
Date: 2004-01-26 15:19:07
Message-ID: 20040126151907.47148.qmail@web20813.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-odbc
--- Aaron Spike <sauron(at)mlc-wels(dot)edu> wrote:
> Using MS Access 2003 and pgsql-odbc 7.03.02.00.
> 
> Today I added a column (Course_LongName) to a table
> (Courses), when I
> relinked the table in Access decided that that
> column should be part 
> of
> the composite primary key. The Correct Key is
> "Course_Number",
> "SchoolYear", "SchoolYearSemester". Access Chooses
> "Course_Number",
> "SchoolYearSemester", "Course_LongName" every time.
> I can't get it to 
> let
> go!
> 
> I've deleted the link and relinked/refreshed. I've
> checked and 
> unchecked "Recognise unique indexes" to no avail. Is
> there any way I 
> can
> make this go right?
> 
> How does Access choose keys? Can I trick it?
> 
> Is there a way to force Access to display the key
> choosing interface 
> when
> linking tables from postgresql?
> 
> 
> Aaron Spike
> 
> My Table description:
>                      Table "public.Courses"
>        Column       |              Type             
> | Modifiers
>
--------------------+--------------------------------+-----------
>  Course_Number      | character varying(7)          
> | not null
>  Course_ShortName   | character varying(12)         
> |
>  Course_Credit      | double precision              
> | not null
>  ModifiedBy         | text                          
> |
>  Modified           | timestamp(0) without time zone
> |
>  Course_Description | text                          
> |
>  SchoolYear         | character varying(6)          
> | not null
>  SchoolYearSemester | character varying(1)          
> | not null
>  Course_LongName    | character varying(255)        
> |
> Indexes: Courses_pkey primary key btree
> ("Course_Number", 
> "SchoolYear", "SchoolYearSemester")
> Triggers: courses_update_modified

From the psqlodbc FAQs:

Q: How do I get my application to recognize primary
keys? 
A: SQLPrimaryKeys() is implemented in the driver. The
driver queries the system tables in search of a unique
index named with the using "{table}_pkey". For
Example: 

create table foo (
   id integer primary key,
   name varchar(20)
);
 
So, for you this should "just work".  I don't have an
answer, but maybe someone else can suggest it.

If you don't have logging enabled, you may want to try
that for a while; there may be some clues in there.

BTW, I suggest you reconsider your use of a composite
primary key.  The fact that they are supported doesn't
make them a good idea.

At some time in the past I resolved to put
serial/autoincrement fields as a primary key in every
one of my Access tables (linked or not), and my life
became simpler after that.

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

In response to

Responses

pgsql-odbc by date

Next:From: NellianDate: 2004-01-26 19:06:05
Subject:
Previous:From: Dave PageDate: 2004-01-26 14:47:37
Subject: Re: odbc.sql script

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group