Thread: [HELP] MSAccess picks wrong keys
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
--- Aaron Spike <sauron@mlc-wels.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/
> So, for you this should "just work". I don't have an > answer, but maybe someone else can suggest it. Well it didn't just work. Until I dropped and recreated the table and copied the data back in. Now it works fine. Its almost like the driver knew that the index was on the first field and the last two fields. Problems and fixes aside can anyone offer more information on how the pgodbc driver and access decide whether or not to present the "Select Unique Record Identifier" dialogue when linking tables? > If you don't have logging enabled, you may want to try > that for a while; there may be some clues in there. Logging with the odbc driver or on the server? > 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. Not my choice actually, but after working with them for some time I they have grown on me. Aaron Spike
--- Aaron Spike <sauron@mlc-wels.edu> wrote: > > So, for you this should "just work". I don't have > an > > answer, but maybe someone else can suggest it. > > Well it didn't just work. Until I dropped and > recreated the table and > copied the data back in. Now it works fine. Its > almost like the > driver knew that the index was on the first field > and the last two > fields. > > Problems and fixes aside can anyone offer more > information on how the > pgodbc driver and access decide whether or not to > present the "Select > Unique Record Identifier" dialogue when linking > tables? I suspect that is up to the ODBC driver. Anybody feel like hunting through the code for "SQLPrimaryKeys()"? ;-) > > > If you don't have logging enabled, you may want to > try > > that for a while; there may be some clues in > there. > > Logging with the odbc driver or on the server? Driver (MyLog) logging will tell you exactly what the driver is sending to the server, and what it is doing with what it gets back. But that is extremely verbose and hard to follow. But may be indispensable if you are having a problem. > > > 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. > > Not my choice actually, but after working with them > for some time I > they have grown on me. > > Aaron Spike __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/