Rick -- I read your post -- was wondering if there is some more formal guidance on WHY MS Access asks for a primary key on ODBC linked tables.
I infer from your post that this is to enable editing through the link.
Anyway, having an issue with a read-only ODBC linked table (to Oracle 10g) that my tech support is insisting is because I picked the wrong primary key when I linked the table. I'm contending that it shouldn't matter, since it's read-only. So far, they're not interested in pursuing the matter further, unless I can "show them" that they're wrong, and thus that the problem isn't really solved.
Here's my description of the "issue" if you're interested in more details.
http://stackoverflow.com/questions/...-table-query-with-flawed-results-but-no-error
thanks for any help!
Rick Brandt wrote:
Re: ODBC linked table - primary key
17-May-07
Alan wrote
Unless it is REALLY old legacy stuff it might still be a database table.
Big-Iron people tend to still use the term "file", but the AS400 has had the
UDB400 database built into its operating system for a really long time now
so most "files" really are database tables. They are just not very often
properly designed database tables and lack of a proper primary key is
common
When you create the link in the Access GUI you should get a prompt that asks
you what columns in the table can be used to build a local index. Doing so
would make the link editable
HOWEVER; if what you say is true and this table really has no combination of
fields that can guarantee uniqueness then you really cannot make it editable
from Access. If you select a combination of fields that are "usually, but
not always" unique, then any edit you make to one record can actually end up
editing multiple records on the AS400. That's a recipe for really messing
things up
Do you actually need to edit the data? What is it that you mean by "make
things work properly"? A PK or unique index is only required if you want to
make edits via the link
--
Rick Brandt, Microsoft Access MV
Email (as appropriate) to..
RBrandt at Hunter dot com
Previous Posts In This Thread:
ODBC linked table - primary key
Hi folks
I'm just wondering if there's a way to add a primary key to an ODBC linked
table? The current linked table as it is does not have a primary key and
there's no data in there that would actually work as a primary key so I'd
like to add one to make things work properly. Is there a way that I can add a
primary key to this linked table but preserve the live link so that my data
is always current
Thanks
Alan
Re: ODBC linked table - primary key
You would have to add the PK to the table on the server and then refresh o
re-create the link
-
Rick Brandt, Microsoft Access MV
Email (as appropriate) to..
RBrandt at Hunter dot com
Thanks for the reply.
Thanks for the reply
Unfortunately the server is an AS400 and the file that comes over is
basically a text file that has the data updated twice a day. I didn't think
there was a way to designate an PK on the AS400 is there?
Ala
:
Re: ODBC linked table - primary key
Alan wrote
Unless it is REALLY old legacy stuff it might still be a database table.
Big-Iron people tend to still use the term "file", but the AS400 has had the
UDB400 database built into its operating system for a really long time now
so most "files" really are database tables. They are just not very often
properly designed database tables and lack of a proper primary key is
common
When you create the link in the Access GUI you should get a prompt that asks
you what columns in the table can be used to build a local index. Doing so
would make the link editable
HOWEVER; if what you say is true and this table really has no combination of
fields that can guarantee uniqueness then you really cannot make it editable
from Access. If you select a combination of fields that are "usually, but
not always" unique, then any edit you make to one record can actually end up
editing multiple records on the AS400. That's a recipe for really messing
things up.
Do you actually need to edit the data? What is it that you mean by "make
things work properly"? A PK or unique index is only required if you want to
make edits via the link.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Hi Rick,What I'm creating is a quote system that allows a salesman to create a
Hi Rick,
What I'm creating is a quote system that allows a salesman to create a quote
with multiple packages in it. I have the individual packages as a table of
their own with a relationship back to the quote they're related to which is
all fine and dandy. Each quote can display multiple packages of differing
configurations.
Everything works the way it should. Within a package, they can pick kits
that are bolted onto a unit and the data for these kits which includes
pricing comes from the AS400 (that is updated daily) so an ODBC link has been
created to this data that is live. Any change that happens on the AS400 side
gets reflected right away on the dB side however where I'm running into
problems is getting that pricing data over. The package table essentially
contains:
pkgID,
quoteID
kit# (as a dropdown box that is linked the ODBC file that shows the kit#)
kitMarkUp
Everything in the package table works as it should and links properly
however to get the pricing data too, I created a query that links the package
table back to the ODBC file through the kit# and brought the pricing field
over from the ODBC table so when the salesman picks a kit#, it should display
the associated price so they can create their quote. Should work right? It
does partially. After this query is created, it displays the proper kits and
their pricing BUT I can't add any more kits to the package and my guess is
that it's due to the fact that there isn't a designated PK on the ODBC table.
As an experiment, instead of creating a linked table, I just imported the
entire table and once in the dB I designated the kit# as the PK, created the
same query as above and as I guessed, everything works exactly as I wanted.
The relationship between the package table and the ODBC table I imported
using the kit# displays the price for whatever kit I pick and I can add more
kits to the package. But now the problem is the ODBC table is no longer live
and I'd have to do an import each time to get the most updated pricing.
I guess I could work with this if I could create a macro that does the
import automatically and over-writes the table. It wouldn't be as clean as
having a live link that does it all automatically though.
Any ideas or am I missing something fundamental in the design?
Alan
:
Re: ODBC linked table - primary key
Alan wrote:
Instead of adding the ODBC link to your query use a Dlookup() or similar to
retrieve the price. I suspect that adding a read-only table to your query
is making the entire query non-editable. Actually, ANY multi-table query is
often not editable, so I avoid trying to do that whenever possible.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
You cannot really add a primary key; however, you can tell Access to use a
You cannot really add a primary key; however, you can tell Access to use a
particular field like if it was the primary key but I don't know if this
will work in your case; see the section Adjusting Dynaset Behavior in the
following reference for an example:
http://msdn2.microsoft.com/en-us/library/bb188204.aspx
And for Views:
http://support.microsoft.com/kb/q209123/
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
To make things even clearer, I noticed that you have said ? there's no data in
To make things even clearer, I noticed that you have said ? there's no data
in there that would actually work as a primary key ? in your OP but I'm not
sure about what you really mean with this; so I gave the previous references
just in case it could help you.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message
Thanks guys for your help.
Thanks guys for your help. I ended up running this AS400 data through an SQL
server and it solved my problems. Just wanted to post it in case anyone else
has a similar problem.
Alan
:
Primary Key for Linked Table
Rick -- I read your post -- was wondering if there is some more formal guidance on WHY MS Access asks for a primary key on ODBC linked tables.
I infer from your post that this is to enable editing through the link.
Anyway, having an issue with a read-only ODBC linked table (to Oracle 10g) that my tech support is insisting is because I picked the wrong primary key when I linked the table. I'm contending that it shouldn't matter, since it's read-only. So far, they're not interested in pursuing the matter further, unless I can "show them" that they're wrong, and thus that the problem isn't really solved.
Here's my description of the "issue" if you're interested in more details.
http://stackoverflow.com/questions/...-table-query-with-flawed-results-but-no-error
thanks for any help!
Primary Key for Linked Table
Rick -- I read your post -- was wondering if there is some more formal guidance on WHY MS Access asks for a primary key on ODBC linked tables.
I infer from your post that this is to enable editing through the link.
Anyway, having an issue with a read-only ODBC linked table (to Oracle 10g) that my tech support is insisting is because I picked the wrong primary key when I linked the table. I'm contending that it shouldn't matter, since it's read-only. So far, they're not interested in pursuing the matter further, unless I can "show them" that they're wrong, and thus that the problem isn't really solved.
Here's my description of the "issue" if you're interested in more details.
http://stackoverflow.com/questions/...-table-query-with-flawed-results-but-no-error
thanks for any help!
Submitted via EggHeadCafe - Software Developer Portal of Choice
Win a Free License of SandRibbon for Silverlight
http://www.eggheadcafe.com/tutorial...ee-license-of-sandribbon-for-silverlight.aspx