Re: [HELP] MSAccess picks wrong keys

--- 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/