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

"Alan" wrote in message
...
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?

You would have to add the PK to the table on the server and then refresh or
re-create the link.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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

Alan

"Rick Brandt" wrote:

"Alan" wrote in message
...
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?

You would have to add the PK to the table on the server and then refresh or
re-create the link.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Alan wrote:
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??

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

"Rick Brandt" wrote:

Alan wrote:
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??

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

Alan wrote:
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?

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


"Alan" wrote in message
...
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

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 ...
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)


"Alan" wrote in message
...
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

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

"Alan" wrote:

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