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: (view raw, whole thread or download thread mbox)
Lists: pgsql-odbc
--- Aaron Spike <sauron(at)mlc-wels(dot)edu> wrote:
> Using MS Access 2003 and pgsql-odbc
> 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
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

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!

In response to


pgsql-odbc by date

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

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