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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Nellian | 2004-01-26 19:06:05 | |
Previous Message | Dave Page | 2004-01-26 14:47:37 | Re: odbc.sql script |